TSQL 求一个最长连续天数的问题。
数据表 A
DT OYEAR
2002-1-17 2002
2002-1-18 2002
2002-12-25 2002
2002-12-27 2002
2003-1-4 2003
2003-1-5 2003
2003-1-6 2003
2003-1-8 2003
需要查询出结果
OYEAR DAYS
2002 2
2003 3
1.根据表A查询出，每年最长的持续天数，不能中断。
2.如果不用游标，是否也可以实现？
[解决办法]
间断和孤岛问题 自己改改
[解决办法]
create table tb(DT datetime,OYEAR int)
insert into tb values('2002-1-17' , 2002)
insert into tb values('2002-1-18' , 2002)
insert into tb values('2002-12-25' , 2002)
insert into tb values('2002-12-27' , 2002)
insert into tb values('2003-1-4' , 2003)
insert into tb values('2003-1-5' , 2003)
insert into tb values('2003-1-6' , 2003)
insert into tb values('2003-1-8' , 2003)
go
select OYEAR , datediff(dd,start_DT,end_dt) + 1 days from
(
SELECT t2.OYEAR,
MIN(t2.DT) start_DT,
MAX(t2.DT) end_DT
FROM (
SELECT tb.OYEAR,
tb.DT,
tb.DT - ROW_NUMBER() OVER(PARTITION BY tb.OYEAR ORDER BY tb.DT) y1
FROM tb
) t2
GROUP BY t2.OYEAR,t2.y1 HAVING COUNT(*) > 1
) m
drop table tb
/*
OYEAR days
----------- -----------
2002 2
2003 3
(2 行受影响)
*/
if object_id('[TB]') is not null drop table [TB]
go
create table [TB] (DT datetime,OYEAR int)
insert into [TB]
select '2002-1-17',2002 union all
select '2002-1-18',2002 union all
select '2002-12-25',2002 union all
select '2002-12-27',2002 union all
select '2003-1-4',2003 union all
select '2003-1-5',2003 union all
select '2003-1-6',2003 union all
select '2003-1-8',2003
select * from [TB]
SELECT oyear ,
MAX(num) AS [days]
FROM ( SELECT oyear ,
ROW_NUMBER() OVER ( PARTITION BY grp ORDER BY dt ) AS num
FROM ( SELECT dt ,
oyear ,
( SELECT MIN(dt)
FROM tb AS b
WHERE b.dt >= a.dt
AND NOT EXISTS ( SELECT
1
FROM
TB AS C
WHERE
b.dt = DATEADD(dd,
-1, c.dt) )
) AS grp
FROM TB AS A
) T
) N
GROUP BY oyear
/*
oyeardays
20022
20033*/