多列重叠日期

loading...


1
;with cte as (
 select Domain_Id, Starting_Date, End_Date
 from Que_Date
 union all
 select t.Domain_Id, cte.Starting_Date, t.End_Date
 from cte
 join Que_Date t on cte.Domain_Id = t.Domain_Id and cte.End_Date = t.Starting_Date),
cte2 as (
 select *, rn = row_number() over (partition by Domain_Id, End_Date order by Domain_Id)
 from cte
)
select DISTINCT Domain_Id, Starting_Date, max(End_Date) enddate
from cte2
where rn=1
group by Domain_Id, Starting_Date
order by Domain_Id, Starting_Date;

select * from Que_Date

这是我写的代码,但是我得到了一个额外的行,即第二行是额外的,预期的输出应该只有第1行,第3行和第4行作为输出,所以请帮助我 .

我附上了一个显示输入,例外输出和我得到的输出的图像 .

loading...

2回答

  • 0

    我使用了@stepup提供的相同的表创建脚本,但您也可以通过这种方式获得结果 .

    CREATE TABLE testtbl
    (
    Domain_ID INT,
    Starting_Date DATE,
    End_Date Date
    )
    
    INSERT INTO testtbl
    
    VALUES
    (1, convert(date, '01-01-2011' ,103), convert(date, '05-01-2011',103)  )
    ,(1,  convert(date, '05-01-2011' ,103), convert(date, '07-01-2011',103)  )  
    ,(1,  convert(date, '07-01-2011' ,103), convert(date, '15-01-2011',103)  )
    ,(2, convert(date, '11-05-2011' ,103), convert(date, '12-05-2011',103)   )
    ,(2, convert(date, '13-05-2011' ,103), convert(date, '14-05-2011',103)   )
    

    您可以使用自联接和Firs_value以及组中的最后一个值,以确保您在相同的ID和重叠日期内进行比较 .

    select distinct t.Domain_ID,
    case when lag(t1.starting_date)over (partition by t.Domain_id order by 
    t.starting_date) is not null   
    then first_value(t.Starting_Date) over (partition by t.domain_id order by 
    t.starting_date) 
    else  t.Starting_Date end StartingDate, 
    case when lead(t.domain_id) over (partition by t.domain_id order by t.starting_date)  = 
    t1.Domain_ID then  isnull(last_value(t.End_Date) over (partition by t.domain_id  order by t.end_date rows between unbounded preceding and unbounded following),t.End_Date)   
    else t.End_Date  end    end_date 
    from testtbl t 
    left join testtbl t1 on t.Domain_ID = t1.Domain_ID 
                        and t.End_Date = t1.Starting_Date 
                        and t.Starting_Date < t1.Starting_Date
    

    输出:

    Domain_ID   StartingDate    end_date
    1           2011-01-01  2011-01-15
    2           2011-05-11  2011-05-12
    2           2011-05-13  2011-05-14
    

  • 0

    你的第一个 cte 你有这么多的结果 . 您的第一个 cte 包含域名 . 因此,您无法根据 cte 过滤域名 . 所以你查询有不必要的行 .

    尝试此解决方案 . Cte ConsistentDomains 只有一致的域名 . 因此,基于此cte,我们可以得到不重叠的结果 .

    创建和填充数据:

    CREATE TABLE FooTable
    (
        Domain_ID INT,
        Starting_Date DATE,
        End_Date Date
    )
    
    INSERT INTO dbo.FooTable
    (
        Domain_ID,
        Starting_Date,
        End_Date
    )
    VALUES
    (   1,         -- Domain_ID - int
        CONVERT(datetime,'01-01-2011',103), -- Starting_Date - date
        CONVERT(datetime,'05-01-2011',103)  -- End_Date - date
        )
    , (1, CONVERT(datetime,'05-01-2011',103), CONVERT(datetime,'07-01-2011',103))
    , (1, CONVERT(datetime,'07-01-2011',103), CONVERT(datetime,'15-01-2011',103))
    , (2, CONVERT(datetime,'11-05-2011',103), CONVERT(datetime,'12-05-2011',103))
    , (2, CONVERT(datetime,'13-05-2011',103), CONVERT(datetime,'14-05-2011',103))
    

    查询以查找不重叠的结果:

    DECLARE @startDate varchar(50) = '2011-01-01';
    WITH ConsistentDomains AS
    (
        SELECT      
          f.Domain_ID
        , f.Starting_Date
        , f.End_Date    
        FROM FooTable f
        WHERE f.Starting_Date = @startDate
        UNION ALL
        SELECT 
          s.Domain_ID
        , s.Starting_Date
        , s.End_Date
        FROM FooTable s
        INNER JOIN  ConsistentDomains cd 
          ON s.Domain_ID = cd.Domain_ID
          AND s.Starting_Date = cd.End_Date
    ), ConsistentDomainsRownumber AS 
    (
        SELECT 
          cd.Domain_ID
        , cd.Starting_Date
        , cd.End_Date
        , ROW_NUMBER() OVER (PARTITION BY cd.Domain_ID ORDER BY cd.Starting_Date, 
            cd.End_Date) RN
        FROM ConsistentDomains cd
    )
    SELECT cd.Domain_ID
    , convert(varchar, cd.Starting_Date, 105) Starting_Date
    , convert(varchar, cd.End_Date, 105) End_Date
    FROM ConsistentDomainsRownumber cd WHERE  cd.RN = 1
    UNION ALL
    SELECT        
       ft.Domain_ID
       , convert(varchar, ft.Starting_Date, 105) Starting_Date
       , convert(varchar, ft.End_Date, 105) End_Date
     FROM dbo.FooTable ft WHERE ft.Domain_ID NOT IN (SELECT cd.Domain_ID FROM 
         ConsistentDomainsRownumber cd)
    

    输出:

评论

暂时没有评论!