首页 > 代码库 > SQL Server中公用表表达式（CTE）递归的生成帮助数据

SQL Server中公用表表达式（CTE）递归的生成帮助数据

2024-08-16 15:24:11   220人阅读

 

本文出处：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）递归的生成帮助数据


联系
我们