日期:2014-05-17 浏览次数:20442 次
--> 测试数据:[TB]
if object_id('[TB]') is not null drop table [TB]
GO
create table [TB]([ca] varchar(1),[cb] varchar(1),[cc] datetime,[cd] datetime)
insert [TB]
select 'A','B','2012-1-1','2012-1-5' union all
select 'A','B','2012-1-6','2012-1-9' union all
select 'A','B','2012-1-10','2012-1-12' union all
select 'A','C','2012-2-1','2012-2-8' union all
select 'A','D','2012-3-1','2012-3-5' union all
select 'A','D','2012-3-7','2012-3-9'
WITH temp AS(
SELECT * FROM TB t
CROSS APPLY
(SELECT flag=DATEADD(dd,number,t.cc)
FROM [master].dbo.spt_values sv WHERE sv.[type]='p' AND DATEADD(dd,number,t.cc)<=cd)g
)
SELECT ca,cb,MIN(cc)AS cc,MAX(cd) AS cd FROM (
SELECT *,number=ROW_NUMBER() OVER (PARTITION BY cb ORDER BY cc) FROM temp t)g
GROUP BY ca,cb,number-DATEPART(dd,flag)
ORDER BY cb
/*
ca cb cc cd
---- ---- ----------------------- -----------------------
A B 2012-01-01 00:00:00.000 2012-01-12 00:00:00.000
A C 2012-02-01 00:00:00.000 2012-02-08 00:00:00.000
A D 2012-03-01 00:00:00.000 2012-03-05 00:00:00.000
A D 2012-03-07 00:00:00.000 2012-03-09 00:00:00.000
(4 行受影响)
*/
drop table [TB]