;WITH CTE AS(
SELECT NEWID()ID,startdate,enddate FROM TB
)
,start AS(
SELECT startdate,ROW_NUMBER()OVER(ORDER BY startdate)RN
FROM CTE T1
WHERE NOT EXISTS(
SELECT 1 FROM CTE T2 WHERE T1.ID<>T2.ID
AND T1.startdate>T2.startdate AND T1.startdate<=DATEADD(DAY,1,T2.enddate)
)
GROUP BY startdate
)
,stop AS(
SELECT enddate,ROW_NUMBER()OVER(ORDER BY enddate)RN
FROM CTE T1
WHERE NOT EXISTS(
SELECT 1 FROM CTE T2 WHERE T1.ID<>T2.ID
AND T1.enddate>=DATEADD(DAY,-1,T2.startdate)AND T1.enddate<T2.enddate
)
GROUP BY enddate
)
SELECT startdate,enddate
FROM start JOIN stop ON start.RN=stop.RN如果你每行都有一个唯一的属性,则可以再简化一下
SELECT NEWID()ID,startdate,enddate FROM TB
)
,start AS(
SELECT startdate,ROW_NUMBER()OVER(ORDER BY startdate)RN
FROM CTE T1
WHERE NOT EXISTS(
SELECT 1 FROM CTE T2 WHERE T1.ID<>T2.ID
AND T1.startdate>T2.startdate AND T1.startdate<=DATEADD(DAY,1,T2.enddate)
)
GROUP BY startdate
)
,stop AS(
SELECT enddate,ROW_NUMBER()OVER(ORDER BY enddate)RN
FROM CTE T1
WHERE NOT EXISTS(
SELECT 1 FROM CTE T2 WHERE T1.ID<>T2.ID
AND T1.enddate>=DATEADD(DAY,-1,T2.startdate)AND T1.enddate<T2.enddate
)
GROUP BY enddate
)
SELECT startdate,enddate
FROM start JOIN stop ON start.RN=stop.RN如果你每行都有一个唯一的属性,则可以再简化一下
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货