您的位置:威尼斯官方网站 > 威尼斯官方网站登录 > 威尼斯官方网站必得须求连接的数字

威尼斯官方网站必得须求连接的数字

发布时间:2020-01-04 07:04编辑:威尼斯官方网站登录浏览(107)

     

    本文出处:

     

    咱俩在做开荒的时候,一时候会须要部分协助数据,必需要求接二连三的数字,三番五次间隔的时间点,三回九转的季度日期等等
    周围很几人利用master库的spt_values系统表,这几个当然没非凡

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

    威尼斯官方网站 1

    如此这般也能够使用,不过感到相当不够灵活,一是还是不是随意一个账号都足以访谈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参数逻辑运算,也得以转移一时表,达到数十次录取的指标,这样感到是还是不是也很直率?

     

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

    威尼斯官方网站 2

     

    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

     

    3,生成一连间距的小时点

      临时候有个别计算必要依照多少个钟头或然半时辰之类的光阴间隔做结合,比方计算某天内没半个钟头的小时数据等等

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

     

    补充园友的一个实际上选用-二零一五1119

    测量检验数据:

     

    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的递归性格做灵活管理。

     

    本文由威尼斯官方网站发布于威尼斯官方网站登录,转载请注明出处:威尼斯官方网站必得须求连接的数字

    关键词: