连续的季度日期等等澳门太陽城集团登录网址

补给园友的一个实际上选取-20141119

 

 

  一时候有个别总括需求遵照三个钟头只怕一时辰之类的光阴距离做结合,例如总结某天内没半个钟头的钟点数据等等

固有数据的指南,很经常

 

 

实际效果:

  4,生成三回九转季度的末段一天

--生成连续日期
;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;

 

澳门太阳集团城网址, 

 

急需专注的是,CTE递归的暗中认可次数是100,假诺不点名递归次数(option
(maxrecursion N);),高出暗中认可最大递归次数之后会报错。

澳门太陽城集团登录网址, 

澳门太陽城集团登录网址 1

能够向来让CTE参数逻辑运算,也能够转移不时表,到达多次收音和录音的目标,那样认为是还是不是也很舒心?

 

 

 

连续的季度日期等等澳门太陽城集团登录网址。 

澳门太陽城集团登录网址 2

澳门太陽城集团登录网址 3

 

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

澳门太陽城集团登录网址 4

正文出处:

诸如此类也足以应用,不过感到相当不够利索,一是还是不是无论三个账号都可以访问master数据库的,而是他那中间也唯有这么一个接连的数字了,
想要其余结果集就不太弄了,
仿佛数据足以用公用表表明式CTE的递归来变化
举个例子说上述的0-2047的结果集

譬喻下边这几个(没截完,结果是0-2047)

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')

连续的季度日期等等澳门太陽城集团登录网址。连续的季度日期等等澳门太陽城集团登录网址。3,生成连续间隔的日子点

连续的季度日期等等澳门太陽城集团登录网址。我们在做开荒的时候,不经常候会必要有个别帮助数据,必须须求连接的数字,接二连三间隔的时间点,连续的季度日期等等
大范围很五人采纳master库的spt_values系统表,那一个当然没极度

澳门太陽城集团登录网址 5

 ——————————————递归原本很轻便选拔,本文原来是说用递归生成帮忙数据的,有意中人问到递归本人的利用,那就再补充二个DEMO吧———————————————————

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

连续的季度日期等等澳门太陽城集团登录网址。 

 

 

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

澳门太陽城集团登录网址 7

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

1,生成三番五次数字(当然数字的开首值,间隔值都能够自定义)

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

--生成连续数字
;with GenerateHelpData
as
(
    select 0 as id
    union all
    select id+1 from GenerateHelpData where id<2047
)
select id from GenerateHelpData option (maxrecursion 2047);

测量试验数据:

 

 

  

--更骚一点的变形
;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

  澳门太陽城集团登录网址 8

创办三个函数,获取当前节点的父节点信息

 

 

澳门太陽城集团登录网址 9

 

 

澳门太陽城集团登录网址 10

2,CTE递归生成再三再四日期

  总括:本文演示了三种常用的基于CTE递归生成协助数据的情事,假若须求扶助数据,能够遵照CTE的递归性子做灵活管理。

 

You may also like...

发表评论

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

网站地图xml地图