問題：
在數據庫腳本開發中，有時需要生成一堆連續數字或者日期，例如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是在內存中準備好數據，而不是每次一條往返服務器和客戶端一次。如果需要再插入到臨時表的話就是全部數據一次性插入。
如果傳入參數為數字，則生成連續數字；如果傳入參數為日期，則生成連續日期。
是不是覺得很方便呢？
函數腳本：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
|
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
2
3
4
5
6
7
8
9
|
-- 示例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這個自定義表值函數，指定起止數字或日期，就達到了生成連續數字和日期的目的。
擴展：
如果想生成連續月份呢？博主在這里也幫大家寫了一下腳本，如果需要可以在此基礎上再自行做成表值函數：
|
1
2
3
4
5
6
7
8
9
10
11
|
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通過遞歸方式編寫通用函數自動生成連續數字和日期 ,希望對大家有所幫助，如果大家有任何疑問請給我留言，小編會及時回復大家的。在此也非常感謝大家對服務器之家網站的支持！如果你覺得本文對你有幫助，歡迎轉載，煩請注明出處，謝謝！
原文鏈接：https://www.cnblogs.com/zhang502219048/archive/2019/06/30/11108991.html
