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

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

問題：

在數(shù)據(jù)庫腳本開發(fā)中，有時需要生成一堆連續(xù)數(shù)字或者日期，例如yearly report就需要連續(xù)數(shù)字做年份，例如daily report就需要生成一定時間范圍內(nèi)的每一天日期。

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

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

解決方案：

可以使用公用表表達式CTE通過遞歸方式實現(xiàn)，并編寫為一個通用表值函數(shù)方便調(diào)用，封裝起來簡化使用，返回表格式數(shù)據(jù)。

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

如果傳入?yún)?shù)為數(shù)字，則生成連續(xù)數(shù)字；如果傳入?yún)?shù)為日期，則生成連續(xù)日期。是不是覺得很方便呢？

函數(shù)腳本：

if object_id('dbo.fun_ConcatStringsToTable') is not null drop function dbo.fun_ConcatStringsToTablego/*  功能：連續(xù)字符串（數(shù)字或日期）以table形式返回  作者：zhang502219048 2018-12-10  腳本來源：https://www.cnblogs.com/zhang502219048/p/11108991.html -- 示例1（數(shù)字）：  select * from dbo.fun_ConcatStringsToTable(1, 10000)-- 示例2（數(shù)字文本）：  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  --數(shù)字  if isnumeric(@strBegin) = 1 and isnumeric(@strEnd) = 1  begin    --使用CTE遞歸批量插入數(shù)字數(shù)據(jù)    ;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遞歸批量插入日期數(shù)據(jù)    ;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

調(diào)用函數(shù)示例：

-- 示例1（數(shù)字）：  select * from dbo.fun_ConcatStringsToTable(1, 10000)-- 示例2（數(shù)字文本）：  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')

腳本運行結果：

結論：

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

擴展：

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

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)

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