澳门新葡亰亚洲在线sql server使用公用表表达式CTE通过递归方式编写通用函数自动生成连续数字和日期

澳门新葡亰亚洲在线 10

问题:

 

在数据库脚本开发中,有时需要生成一堆连续数字或者日期,例如yearly
report就需要连续数字做年份,例如daily
report就需要生成一定时间范围内的每一天日期。

澳门新葡亰亚洲在线,本文出处:

而自带的系统表master..spt_values存在一定的局限性,只是从0到2047,也不能直接生成连续日期。

 

可能大部分人会想到一个笨办法,通过while循环去逐条插入数据到临时表,每次数字加1或者日期加1天,但这样和数据库服务器的交互就太频繁了。如果生成1W个连续数字,那就要跟数据库服务器交互1W次,可怕!如果是有1000个客户端都需要调用这个while循环,那就是1000W次!可怕!

我们在做开发的时候,有时候会需要一些帮助数据,必须需要连续的数字,连续间隔的时间点,连续的季度日期等等
常见很多人利用master库的spt_values系统表,这个当然没有问题

解决方案:

比如下面这个(没截完,结果是0-2047)

可以使用公用表表达式CTE通过递归方式实现,并编写为一个通用表值函数方便调用,封装起来简化使用,返回表格式数据。

澳门新葡亰亚洲在线 1

CTE是在内存中准备好数据,而不是每次一条往返服务器和客户端一次。如果需要再插入到临时表的话就是全部数据一次性插入。

这样也可以使用,但是感觉不够灵活,一是不是随便一个账号都可以访问master数据库的,而是他这里面也只有这么一个连续的数字了,
想要别的结果集就不太弄了,
类似数据可以用公用表表达式CTE的递归来生成
比如上述的0-2047的结果集

如果传入参数为数字,则生成连续数字;如果传入参数为日期,则生成连续日期。是不是觉得很方便呢?

;with GenerateHelpData
as
(
    select 0 as id
    union all
    select id+1 from GenerateHelpData where id<2047
)
select id from GenerateHelpData option (maxrecursion 2047);

函数脚本:

可以直接让CTE参数逻辑运算,也可以生成临时表,达到多次重用的目的,这样感觉是不是也很清爽?

if object_id('dbo.fun_ConcatStringsToTable') is not null drop function dbo.fun_ConcatStringsToTablego/* 功能:连续字符串以table形式返回 作者:zhang502219048 2018-12-10 脚本来源: -- 示例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,生成连续数字(当然数字的起始值,间隔值都可以自定义)

-- 示例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')
--生成连续数字
;with GenerateHelpData
as
(
    select 0 as id
    union all
    select id+1 from GenerateHelpData where id<2047
)
select id from GenerateHelpData option (maxrecursion 2047);

脚本运行结果:

澳门新葡亰亚洲在线 2

结论:

 

从上面几个图可以看到,通过简单调用fun_ConcatStringsToTable这个自定义表值函数,指定起止数字或日期,就达到了生成连续数字和日期的目的。

2,CTE递归生成连续日期

扩展:

--生成连续日期
;with GenerateHelpData
as
(
    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 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)

 

总结

3,生成连续间隔的时间点

以上所述是小编给大家介绍的sql
server使用公用表表达式CTE通过递归方式编写通用函数自动生成连续数字和日期
,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对脚本之家网站的支持!如果你觉得本文对你有帮助,欢迎转载,烦请注明出处,谢谢!

  有时候一些统计需要按照一个小时或者半个小时之类的时间间隔做组合,比如统计某天内没半个小时的小时数据等等

--生成连续间隔的时间点
;with GenerateHelpData
as
(
    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

澳门新葡亰亚洲在线 4

 

当然这里就可以非常灵活了,更骚一点的变形

--更骚一点的变形
;with GenerateHelpData
as
(
    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 timeSection
from GenerateHelpData  A inner join GenerateHelpData B on A.id= B.id-1

澳门新葡亰亚洲在线 5

 

  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

澳门新葡亰亚洲在线 6

 

通过变形可以生成两个日期间隔之间的的数据

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 timeSection
from GenerateHelpData  A inner join GenerateHelpData B on A.id= B.id-1

澳门新葡亰亚洲在线 7

 

需要注意的是,CTE递归的默认次数是100,如果不指定递归次数(option
(maxrecursion N);),超出默认最大递归次数之后会报错。

澳门新葡亰亚洲在线 8

 

 

 

 

 

 

 

 ——————————————递归原本很容易使用,本文原本是说用递归生成帮助数据的,有朋友问到递归本身的使用,那就再补充一个DEMO吧———————————————————

 

补充园友的一个实际应用-20161119

测试数据:

 

create table ProuctInfo
(
    Id INT,
    ParentId INT,
    ProuctName VARCHAR(50)
)

INSERT INTO ProuctInfo VALUES (1,0,'镜片')
INSERT INTO ProuctInfo VALUES (2,0,'镜架')
INSERT INTO ProuctInfo VALUES (101,1,'高级镜片')
INSERT INTO ProuctInfo VALUES (102,1,'普通镜片')
INSERT INTO ProuctInfo VALUES (201,2,'高级镜架')
INSERT INTO ProuctInfo VALUES (202,2,'普通镜架')

INSERT INTO ProuctInfo VALUES (1001,101,'高级镜片1')
INSERT INTO ProuctInfo VALUES (1002,102,'普通镜片2')
INSERT INTO ProuctInfo VALUES (2001,201,'高级镜架1')
INSERT INTO ProuctInfo VALUES (2002,202,'普通镜架2')

原始数据的样子,很普通

澳门新葡亰亚洲在线 9

 

创建一个函数,获取当前节点的父节点信息

CREATE FUNCTION dbo.FnGetParentInfo(@id int)
returns varchar(max)
as
begin

declare @name varchar(max)

--查询某一个节点的所有父节点
;with SubTab
as
(
    select [ID],[ParentID], cast(ProuctName as varchar(200)) as ProuctName
    from ProuctInfo WHERE Id = @id
    union all
    select a.[ID],a.[ParentID],cast(a.ProuctName+'--->'+b.ProuctName as varchar(200)) as ProuctName
    from ProuctInfo a,SubTab b
    where a.[ID]=b.[ParentID] 
)
select @name = ProuctName from SubTab  where ParentId = 0 

return @name
end

 

实际效果:

  澳门新葡亰亚洲在线 10

 

 

 

  

  总结:本文演示了几种常用的根据CTE递归生成帮助数据的情况,如果需要帮助数据,可以根据CTE的递归特性做灵活处理。

 

发表评论

电子邮件地址不会被公开。 必填项已用*标注