sql server使用公用表表達(dá)式CTE通過遞歸方式編寫通用函數(shù)自動生成連續(xù)數(shù)字和日期
問題：
在數(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ù)庫服務(wù)器的交互就太頻繁了。如果生成1W個連續(xù)數(shù)字，那就要跟數(shù)據(jù)庫服務(wù)器交互1W次，可怕！如果是有1000個客戶端都需要調(diào)用這個while循環(huán)，那就是1000W次！可怕！
解決方案：
可以使用公用表表達(dá)式CTE通過遞歸方式實現(xiàn)，并編寫為一個通用表值函數(shù)方便調(diào)用，封裝起來簡化使用，返回表格式數(shù)據(jù)。
CTE是在內(nèi)存中準(zhǔn)備好數(shù)據(jù)，而不是每次一條往返服務(wù)器和客戶端一次。如果需要再插入到臨時表的話就是全部數(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_ConcatStringsToTable
go
/*
功能：連續(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))
as
begin
--數(shù)字
if isnumeric(@strBegin) = 1 and isnumeric(@strEnd) = 1
begin
--使用CTE遞歸批量插入數(shù)字?jǐn)?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;
end
go
調(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')
腳本運行結(jié)果：
結(jié)論：
從上面幾個圖可以看到，通過簡單調(diào)用fun_ConcatStringsToTable這個自定義表值函數(shù)，指定起止數(shù)字或日期，就達(dá)到了生成連續(xù)數(shù)字和日期的目的。
擴(kuò)展：
如果想生成連續(xù)月份呢？博主在這里也幫大家寫了一下腳本，如果需要可以在此基礎(chǔ)上再自行做成表值函數(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 YearMonth
from cte_table
option (maxrecursion 0)
總結(jié)
以上所述是小編給大家介紹的sql server使用公用表表達(dá)式CTE通過遞歸方式編寫通用函數(shù)自動生成連續(xù)數(shù)字和日期 ,希望對大家有所幫助，如果大家有任何疑問請給我留言，小編會及時回復(fù)大家的。在此也非常感謝大家對本站網(wǎng)站的支持！如果你覺得本文對你有幫助，歡迎轉(zhuǎn)載，煩請注明出處，謝謝！
版權(quán)聲明：本站文章來源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請保持原文完整并注明來源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非www.sddonglingsh.com所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來，僅供學(xué)習(xí)參考，不代表本站立場，如有內(nèi)容涉嫌侵權(quán)，請聯(lián)系alex-e#qq.com處理。
關(guān)注官方微信