問題:
在數據庫腳本開發(fā)中,有時需要生成一堆連續(xù)數字或者日期,例如yearly report就需要連續(xù)數字做年份,例如daily report就需要生成一定時間范圍內的每一天日期。
而自帶的系統(tǒng)表master..spt_values存在一定的局限性,只是從0到2047(驗證腳本:select * from master..spt_values b where b.type = 'P'
),也不能直接生成連續(xù)日期。
可能大部分人會想到一個笨辦法,通過while循環(huán)去逐條插入數據到臨時表,每次數字加1或者日期加1天,但這樣和數據庫服務器的交互就太頻繁了。如果生成1W個連續(xù)數字,那就要跟數據庫服務器交互1W次,可怕!如果是有1000個客戶端都需要調用這個while循環(huán),那就是1000W次!可怕!
解決方案:
可以使用公用表表達式CTE通過遞歸方式實現(xiàn),并編寫為一個通用表值函數方便調用,封裝起來簡化使用,返回表格式數據。
CTE是在內存中準備好數據,而不是每次一條往返服務器和客戶端一次。如果需要再插入到臨時表的話就是全部數據一次性插入。
如果傳入參數為數字,則生成連續(xù)數字;如果傳入參數為日期,則生成連續(xù)日期。
是不是覺得很方便呢?
函數腳本:
if object_id('dbo.fun_ConcatStringsToTable') is not null drop function dbo.fun_ConcatStringsToTable
go
/*
功能:連續(xù)字符串(數字或日期)以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))
as
begin
--數字
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;
end
go
調用函數示例:
-- 示例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
這個自定義表值函數,指定起止數字或日期,就達到了生成連續(xù)數字和日期的目的。
擴展:
如果想生成連續(xù)月份呢?博主在這里也幫大家寫了一下腳本,如果需要可以在此基礎上再自行做成表值函數:
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)
總結
以上所述是小編給大家介紹的sql server使用公用表表達式CTE通過遞歸方式編寫通用函數自動生成連續(xù)數字和日期 ,希望對大家有所幫助,如果大家有任何疑問請給我留言,小編會及時回復大家的。在此也非常感謝大家對腳本之家網站的支持!如果你覺得本文對你有幫助,歡迎轉載,煩請注明出處,謝謝!
您可能感興趣的文章:- sqlserver實現(xiàn)樹形結構遞歸查詢(無限極分類)的方法
- 使用SqlServer CTE遞歸查詢處理樹、圖和層次結構
- sqlserver中存儲過程的遞歸調用示例
- 使用SQLSERVER 2005/2008 遞歸CTE查詢樹型結構的方法
- SQLSERVER2005 中樹形數據的遞歸查詢
- sqlserver另類非遞歸的無限級分類(存儲過程版)
- SQLserver2008使用表達式遞歸查詢
- sqlserver數據庫主鍵的生成方式小結(sqlserver,mysql)
- sqlserver 快速生成漢字的首拼字母的函數(經典)