唯一字段列表

debugcn 发表于 Dev

丹尼尔·达尔

我有一个包含以下数据的表

Modifieddate, Frequency  

2015-01-15   Ad-hoc  
2015-01-19   Weekly  
2015-01-20   Weekly  
2016-09-23   Ad-hoc  
2016-09-25   Ad-hoc  
2017-09-20   Monthly

我想对这张表进行分组，以显示该表何时从 Ad-Hoc 变为 Weekly 以及从 Weekly 再次变为 Ad-hoc。我不想知道桌子什么时候从每周变为每周。茶几应该是这样的。

Startdate, Enddate, Frequency 

2015-01-15 2015-01-19 Ad-hoc  
2015-01-19 2016-09-23 Weekly  
2016-09-23 2017-09-20 Ad-hoc  
2017-09-20 getdate()  Monthly

你会怎么做？

Damien_The_Unbeliever

一个双重GROUP BY操作可以找到这个数据：

declare @t table (ModifiedDate date,Frequency varchar(19))
insert into @t(ModifiedDate, Frequency) values
('20150115','Ad-hoc'),
('20150119','Weekly'),
('20150120','Weekly'),
('20160923','Ad-hoc'),
('20160925','Ad-hoc'),
('20170220','Monthly')

;With Ends as (
    select t1.ModifiedDate as StartAt,MIN(t2.ModifiedDate) as EndAt,t1.Frequency
    from
        @t t1
            inner join
        (select * from @t union all select GETDATE(),'dfdhfhdsfjfh') t2
            on
                t1.Frequency != t2.Frequency and
                t1.ModifiedDate < t2.ModifiedDate
    group by
        t1.ModifiedDate,t1.Frequency
)
select MIN(StartAt) as StartAt,EndAt,Frequency from Ends
group by EndAt,Frequency
order by StartAt

结果：

StartAt    EndAt                   Frequency
---------- ----------------------- -------------------
2015-01-15 2015-01-19 00:00:00.000 Ad-hoc
2015-01-19 2016-09-23 00:00:00.000 Weekly
2016-09-23 2017-02-20 00:00:00.000 Ad-hoc
2017-02-20 2017-03-13 11:54:39.277 Monthly

（注意我已经根据评论调整了最后一行）。

这是通过为每一行查找具有不同Frequency值的最早行来实现的。这意味着，比如说，一组Ad-hoc相邻的行将使用值定位相同的后续行Weekly。这就是 CTE/firstGROUP BY正在做的事情。