sql server使用公用表表達式CTE通過遞歸方式如何編寫通用函數自動生成連續數字和日期

141次閱讀

共計 2916 個字符，預計需要花費 8 分鐘才能閱讀完成。

行業資訊
數據庫
sql server 使用公用表表達式 CTE 通過遞歸方式如何編寫通用函數自動生成連續數字和日期

sql server 使用公用表表達式 CTE 通過遞歸方式如何編寫通用函數自動生成連續數字和日期，相信很多沒有經驗的人對此束手無策，為此本文總結了問題出現的原因和解決方法，通過這篇文章希望你能解決這個問題。

問題：

在數據庫腳本開發中，有時需要生成一堆連續數字或者日期，例如 yearly report 就需要連續數字做年份，例如 daily report 就需要生成一定時間范圍內的每一天日期。

而自帶的系統表 master..spt_values 存在一定的局限性，只是從 0 到 2047（驗證腳本：select * from master..spt_values b where b.type = P），也不能直接生成連續日期。

可能大部分人會想到一個笨辦法，通過 while 循環去逐條插入數據到臨時表，每次數字加 1 或者日期加 1 天，但這樣和數據庫服務器的交互就太頻繁了。如果生成 1W 個連續數字，那就要跟數據庫服務器交互 1W 次，可怕！如果是有 1000 個客戶端都需要調用這個 while 循環，那就是 1000W 次！可怕！

解決方案：

可以使用公用表表達式 CTE 通過遞歸方式實現，并編寫為一個通用表值函數方便調用，封裝起來簡化使用，返回表格式數據。

CTE 是在內存中準備好數據，而不是每次一條往返服務器和客戶端一次。如果需要再插入到臨時表的話就是全部數據一次性插入。

如果傳入參數為數字，則生成連續數字；如果傳入參數為日期，則生成連續日期。是不是覺得很方便呢？

函數腳本：

if object_id(dbo.fun_ConcatStringsToTable) is not null drop function dbo.fun_ConcatStringsToTablego/*  功能：連續字符串（數字或日期）以 table 形式返回   作者：zhang502219048 2018-12-10  腳本來源：https://www.cnblogs.com/zhang502219048/p/11108991.html --  示例 1（數字）： select * from dbo.fun_ConcatStringsToTable(1, 10000)--  示例 2（數字文本）： select * from dbo.fun_ConcatStringsToTable(1 ,  10000)--  示例 3（日期）： declare @dateBegin datetime =  2009-1-1 , @dateEnd datetime =  2018-12-31  select * from dbo.fun_ConcatStringsToTable(@dateBegin, @dateEnd)--  示例 4（日期文本）： select * from dbo.fun_ConcatStringsToTable(2009-1-1 ,  2018-12-31)**/create function [dbo].[fun_ConcatStringsToTable]( @strBegin as nvarchar(100), @strEnd as nvarchar(100))returns @tempResult table (vid nvarchar(100))asbegin -- 數字  if isnumeric(@strBegin) = 1 and isnumeric(@strEnd) = 1 begin -- 使用 CTE 遞歸批量插入數字數據  ;with cte_table(id) as ( select cast(@strBegin as int) union all select id + 1 from cte_table where id   @strEnd ) insert into @tempResult select cast(id as nvarchar(100)) from cte_table option (maxrecursion 0) end -- 日期  else if isdate(@strBegin) = 1 and isdate(@strEnd) = 1 begin -- 使用 CTE 遞歸批量插入日期數據  ;with cte_table(CreatedDate) as ( select cast(@strBegin as datetime) union all select dateadd(day, 1, CreatedDate) from cte_table where CreatedDate   @strEnd ) insert into @tempResult select convert(varchar(10), CreatedDate, 120) from cte_table option (maxrecursion 0) end return;endgo

調用函數示例：

--  示例 1（數字）： select * from dbo.fun_ConcatStringsToTable(1, 10000)--  示例 2（數字文本）： select * from dbo.fun_ConcatStringsToTable(1 ,  10000)--  示例 3（日期）： declare @dateBegin datetime =  2009-1-1 , @dateEnd datetime =  2018-12-31  select * from dbo.fun_ConcatStringsToTable(@dateBegin, @dateEnd)--  示例 4（日期文本）： select * from dbo.fun_ConcatStringsToTable(2009-1-1 ,  2018-12-31)

腳本運行結果：

結論：

從上面幾個圖可以看到，通過簡單調用 fun_ConcatStringsToTable 這個自定義表值函數，指定起止數字或日期，就達到了生成連續數字和日期的目的。

擴展：

如果想生成連續月份呢？博主在這里也幫大家寫了一下腳本，如果需要可以在此基礎上再自行做成表值函數：

with cte_table(CreatedDate) as( select cast( 2017-12-1  as datetime) union all select dateadd(month, 1, CreatedDate) from cte_table where CreatedDate    2018-04-01 )select convert(varchar(7), CreatedDate, 120) as YearMonthfrom cte_tableoption (maxrecursion 0)

看完上述內容，你們掌握 sql server 使用公用表表達式 CTE 通過遞歸方式如何編寫通用函數自動生成連續數字和日期的方法了嗎？如果還想學到更多技能或想了解更多相關內容，歡迎關注丸趣 TV 行業資訊頻道，感謝各位的閱讀！

正文完