如何在 SQL 服务器中确定连续的日期记录
本文关键字:连续 日期 记录 SQL 服务器 | 更新日期: 2023-09-27 18:36:14
我有一个这样的数据结构:
ID Status Date
--- ------ ------------
1 I 2013-10-01
1 A 2013-10-02
1 A 2013-10-03
1 I 2013-10-04
1 I 2013-10-05
1 I 2013-10-06
1 A 2013-10-07
1 I 2013-10-08
我想确定有多少个状态I
是连续的。在本例中为 3 (2013-10-04、05 和 06)。
像这样:
Status ID Total_consecutive
------- --- ------------------
A 1 2
I 1 3
提前谢谢。
我相信
这会给你正确的结果:
declare @t table
(
ID INT,
[Status] CHAR(1),
[Date] DATE
);
insert @t (ID, [Status], [Date])
values (1, 'I', '2013-10-01'),
(1, 'A', '2013-10-02'),
(1, 'A', '2013-10-03'),
(1, 'I', '2013-10-04'),
(1, 'I', '2013-10-05'),
(1, 'I', '2013-10-05'),
(1, 'I', '2013-10-05'),
(1, 'I', '2013-10-06'),
(1, 'A', '2013-10-10')
;with a as
(
select ID, [Status], [Date],
row_number() over (partition by id order by [Status], [Date])-
row_number() over (partition by id order by [Date], [Status]) rn1
from @t
)
select ID, [Status], min([Date]) Start, max([Date]) [End],
count(*) [Total_consecutive]
from a
group by id, [Status], rn1
having count(*) > 1
结果:
ID Status Start End Total_consecutive
1 A 2013-10-02 2013-10-03 2
1 I 2013-10-04 2013-10-06 5