sql - 使用分组子句时如何避免重叠日期范围？

Question

我遇到一种情况，我需要找到值变化之间的时间跨度。我尝试了一个简单的 group by 子句，但它消除了重叠的更改。考虑以下示例:

create table #items (
      code varchar(4)
    , class varchar(4)
    , txdate datetime
)

insert into #items (code, class, txdate) values ('A', 'C', '2010-01-01');
insert into #items (code, class, txdate) values ('A', 'C', '2010-01-02');
insert into #items (code, class, txdate) values ('A', 'C', '2010-01-03');
insert into #items (code, class, txdate) values ('A', 'D', '2010-01-04');
insert into #items (code, class, txdate) values ('A', 'D', '2010-01-05');
insert into #items (code, class, txdate) values ('A', 'C', '2010-01-06');
insert into #items (code, class, txdate) values ('A', 'C', '2010-01-07');
insert into #items (code, class, txdate) values ('A', 'D', '2010-01-08');
insert into #items (code, class, txdate) values ('A', 'D', '2010-01-09');

select code
, class
, min(txdate) mindate
, max(txdate) maxdate
from #items
group by code, class

这将返回以下结果(注意重叠的日期范围):

|code|class|mindate   |maxdate   |
----------------------------------
|A   |C    |2010-01-01|2010-01-07|
|A   |D    |2010-01-04|2010-01-09|

我想让查询返回以下内容:

|code|class|mindate   |maxdate   |
----------------------------------
|A   |C    |2010-01-01|2010-01-03|
|A   |D    |2010-01-04|2010-01-05|
|A   |C    |2010-01-06|2010-01-07|
|A   |D    |2010-01-08|2010-01-09|

有什么想法和建议吗？

Answer 1

编辑:正如评论中所指出的，这仍然不太正确。

;with cteNtile as (
    select code, class, txdate, 
           ntile((select count(*) from (select NULL as dummy from #items group by code, class) a)) over(partition by code, class order by txdate) as tilenum
        from #items
)
select code, class, MIN(txdate) as mindate, MAX(txdate) as maxdate
    from cteNtile
    group by code, class, tilenum
    order by mindate, maxdate