数据表 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]