首页 > 代码库 > SQL Server中公用表表达式（CTE）递归的生成帮助数据
SQL Server中公用表表达式（CTE）递归的生成帮助数据
本文出处：http://www.cnblogs.com/wy123/p/5960825.html
我们在做开发的时候，有时候会需要一些帮助数据，必须需要连续的数字，连续间隔的时间点，连续的季度日期等等
常见很多人利用master库的spt_values系统表，这个当然没有问题
比如下面这个（没截完，结果是0-2047）
这样也可以使用，但是感觉不够灵活，一是不是随便一个账号都可以访问master数据库的，而是他这里面也只有这么一个连续的数字了，
想要别的结果集就不太弄了，
类似数据可以用公用表表达式CTE的递归来生成
比如上述的0-2047的结果集
;with GenerateHelpDataas( select 0 as id union all select id+1 from GenerateHelpData where id<2047)select id from GenerateHelpData option (maxrecursion 2047);
可以直接让CTE参数逻辑运算，也可以生成临时表，达到多次重用的目的，这样感觉是不是也很清爽？
1，生成连续数字（当然数字的起始值，间隔值都可以自定义）
--生成连续数字;with GenerateHelpDataas( select 0 as id union all select id+1 from GenerateHelpData where id<2047)select id from GenerateHelpData option (maxrecursion 2047);
2，CTE递归生成连续日期
--生成连续日期;with GenerateHelpDataas( select cast(‘2016-10-01‘ as date) as [Date] union all select DATEADD(D,1,[Date]) from GenerateHelpData where [Date]<‘2017-01-01‘)select [Date] from GenerateHelpData;
3，生成连续间隔的时间点
有时候一些统计需要按照一个小时或者半个小时之类的时间间隔做组合，比如统计某天内没半个小时的小时数据等等
--生成连续间隔的时间点;with GenerateHelpDataas( select 1 as id, cast(‘00:00:00‘ as time(0)) as timeSection union all select id+1 as id, cast(dateadd(mi,30,timeSection) as time(0)) as timeSection from GenerateHelpData where id<49)select * from GenerateHelpData
当然这里就可以非常灵活了，更骚一点的变形
--更骚一点的变形;with GenerateHelpDataas( select 1 as id, cast(‘00:00:00‘ as time(0)) as timeSection union all select id+1 as id, cast(dateadd(mi,30,timeSection) as time(0)) as timeSection from GenerateHelpData where id<49)select A.timeSection as timeSectionFrom,B.timeSection as timeSectionTo,cast(A.timeSection as varchar(10))+‘~‘+cast(B.timeSection as varchar(10)) as timeSectionfrom GenerateHelpData A inner join GenerateHelpData B on A.id= B.id-1
4，生成连续季度的最后一天
DECLARE @begin_date date = ‘2014-12-31‘,@end_date date = ‘2016-12-31‘;with GenerateHelpData as( select CAST( CASE WHEN RIGHT(@begin_date,5)=‘12-30‘ THEN DATEADD(DAY,1,@begin_date) ELSE @begin_date END AS DATE) AS EndingDate UNION ALL SELECT CASE WHEN RIGHT(DATEADD(QQ,1,EndingDate),5)=‘12-30‘ THEN DATEADD(DAY,1,DATEADD(QQ,1,EndingDate)) ELSE DATEADD(QQ,1,EndingDate) END AS EndingDate from GenerateHelpData where EndingDate< @end_date)select * from GenerateHelpData
通过变形可以生成两个日期间隔之间的的数据
DECLARE @begin_date date = ‘2014-12-31‘,@end_date date = ‘2016-12-31‘;with GenerateHelpData as( select 1 as id , CAST( CASE WHEN RIGHT(@begin_date,5)=‘12-30‘ THEN DATEADD(DAY,1,@begin_date) ELSE @begin_date END AS DATE) AS EndingDate UNION ALL SELECT id+1 as id, CASE WHEN RIGHT(DATEADD(QQ,1,EndingDate),5)=‘12-30‘ THEN DATEADD(DAY,1,DATEADD(QQ,1,EndingDate)) ELSE DATEADD(QQ,1,EndingDate) END AS EndingDate from GenerateHelpData where EndingDate< @end_date)select A.EndingDate as DateFrom,B.EndingDate as DateTo,cast(A.EndingDate as varchar(10))+‘~‘+cast(B.EndingDate as varchar(10)) as timeSectionfrom GenerateHelpData A inner join GenerateHelpData B on A.id= B.id-1
需要注意的是，CTE递归的默认次数是100，如果不指定递归次数（option (maxrecursion N);），超出默认最大递归次数之后会报错。
总结：本文演示了几种常用的根据CTE递归生成帮助数据的情况，如果需要帮助数据，可以根据CTE的递归特性做灵活处理。
SQL Server中公用表表达式（CTE）递归的生成帮助数据
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。