22,209
社区成员
发帖
与我相关
我的任务
分享
create table tb (no int,part varchar(10),date datetime)
insert into tb values(1,'AA', '2011-03-15')
insert into tb values(2 ,'AA', '2011-03-16')
insert into tb values(3, 'bb', '2011-03-15')
insert into tb values(4 ,'bb' ,'2011-03-17')
insert into tb values(5, 'bb' ,'2011-03-18')
insert into tb values(6, 'AA' ,'2011-03-17')
select * from
(select *,COUNT(*) over (partition by part,date_c) as 最大连续天数 from
(select *,date-ROW_NUMBER() over (partition by part order by date) date_c
from tb) a) b
where 最大连续天数=3
no part date date_c 最大连续天数
----------- ---------- ----------------------- ----------------------- -----------
1 AA 2011-03-15 00:00:00.000 2011-03-14 00:00:00.000 3
2 AA 2011-03-16 00:00:00.000 2011-03-14 00:00:00.000 3
6 AA 2011-03-17 00:00:00.000 2011-03-14 00:00:00.000 3
(3 行受影响)
select * from
(select *,COUNT(*) over (partition by part,date_c) as 最大连续天数 from
(select *,date-ROW_NUMBER() over (partition by part order by date) date_c
from tb) a) b
where 最大连续天数=2
no part date date_c 最大连续天数
----------- ---------- ----------------------- ----------------------- -----------
4 bb 2011-03-17 00:00:00.000 2011-03-15 00:00:00.000 2
5 bb 2011-03-18 00:00:00.000 2011-03-15 00:00:00.000 2
(2 行受影响)
select * from tb a where exists (select 1 from tb b where b.part=a.part and b.date=DATEADD(DAY,1,a.date))
and exists(select 1 from tb b where b.part=a.part and b.date=DATEADD(DAY,2,a.date))
and exists(select 1 from tb b where b.part=a.part and b.date=DATEADD(DAY,3,a.date)