A 2014/7/27
A 2014/7/28
A 2015/6/26
A 2015/6/27
B 2016/5/2
B 2016/5/3
B 2016/5/7
B 2016/5/8
B 2016/5/9
B 2016/6/18
B 2016/6/19
B 2016/6/20
B 2016/6/21
C 2015/11/30
C 2015/12/2
C 2015/12/11
C 2015/12/12
以上是数据表，要求查询出日期中有三个或三个以上连续天数的ID和连续天数中最早的日期
按照上面数据表数据，查询出来的结果应该是B，2016-05-07
求大神指导
1 个解决方案
#1
create table 数据表
(ID varchar(10),日期 varchar(20))
insert into 数据表
select 'A','2014/7/27' union all
select 'A','2014/7/28' union all
select 'A','2015/6/26' union all
select 'A','2015/6/27' union all
select 'B','2016/5/2' union all
select 'B','2016/5/3' union all
select 'B','2016/5/7' union all
select 'B','2016/5/8' union all
select 'B','2016/5/9' union all
select 'B','2016/6/18' union all
select 'B','2016/6/19' union all
select 'B','2016/6/20' union all
select 'B','2016/6/21' union all
select 'C','2015/11/30' union all
select 'C','2015/12/2' union all
select 'C','2015/12/11' union all
select 'C','2015/12/12'
with t as(
select ID,日期,rn=datediff(d,'19700101',日期)-row_number() over(partition by ID order by 日期)
from 数据表)
select ID,日期=min(日期)
from (select ID,日期=min(日期)
from t
group by ID,rn
having count(1)>=3) u
group by ID
/*
ID 日期
---------- --------------------
B 2016/5/7
(1 row(s) affected)
*/
#1
create table 数据表
(ID varchar(10),日期 varchar(20))
insert into 数据表
select 'A','2014/7/27' union all
select 'A','2014/7/28' union all
select 'A','2015/6/26' union all
select 'A','2015/6/27' union all
select 'B','2016/5/2' union all
select 'B','2016/5/3' union all
select 'B','2016/5/7' union all
select 'B','2016/5/8' union all
select 'B','2016/5/9' union all
select 'B','2016/6/18' union all
select 'B','2016/6/19' union all
select 'B','2016/6/20' union all
select 'B','2016/6/21' union all
select 'C','2015/11/30' union all
select 'C','2015/12/2' union all
select 'C','2015/12/11' union all
select 'C','2015/12/12'
with t as(
select ID,日期,rn=datediff(d,'19700101',日期)-row_number() over(partition by ID order by 日期)
from 数据表)
select ID,日期=min(日期)
from (select ID,日期=min(日期)
from t
group by ID,rn
having count(1)>=3) u
group by ID
/*
ID 日期
---------- --------------------
B 2016/5/7
(1 row(s) affected)
*/