日期:2014-05-17 浏览次数:20755 次
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 ,
&