CREATE
TABLE
#TMP(
ID INT ,
S NVARCHAR ( 100 ),
E NVARCHAR ( 100 ))
INSERT INTO #TMP(ID,S,E)
SELECT 1 , 1 , 2000 UNION ALL
SELECT 1 , 2001 , 4000 UNION ALL
SELECT 1 , 5000 , 6000 UNION ALL
SELECT 2 , 20001 , 30000 UNION ALL
SELECT 2 , 40001 , 50000 UNION ALL
SELECT 2 , 30001 , 40000
SELECT max (a.id) id,a.s , min ( case when (b.e IS NOT NULL ) THEN b.e ELSE a.e end ) FROM #tmp a
left JOIN #tmp b ON a.id = b.id AND a.e + 1 = b.s
WHERE NOT EXISTS ( SELECT 1 FROM #tmp WHERE e + 1 = a.s AND id = a.id)
AND NOT EXISTS ( SELECT 1 FROM #tmp WHERE s + 1 = b.e AND id = a.id)
GROUP BY a.id,a.s ORDER BY id
ID INT ,
S NVARCHAR ( 100 ),
E NVARCHAR ( 100 ))
INSERT INTO #TMP(ID,S,E)
SELECT 1 , 1 , 2000 UNION ALL
SELECT 1 , 2001 , 4000 UNION ALL
SELECT 1 , 5000 , 6000 UNION ALL
SELECT 2 , 20001 , 30000 UNION ALL
SELECT 2 , 40001 , 50000 UNION ALL
SELECT 2 , 30001 , 40000
SELECT max (a.id) id,a.s , min ( case when (b.e IS NOT NULL ) THEN b.e ELSE a.e end ) FROM #tmp a
left JOIN #tmp b ON a.id = b.id AND a.e + 1 = b.s
WHERE NOT EXISTS ( SELECT 1 FROM #tmp WHERE e + 1 = a.s AND id = a.id)
AND NOT EXISTS ( SELECT 1 FROM #tmp WHERE s + 1 = b.e AND id = a.id)
GROUP BY a.id,a.s ORDER BY id
<
p
>
id s e
1 1 4000
1 5000 6000
2 20001 40000 </ p >< p > ( 3 行受影响)
</ p >
1 1 4000
1 5000 6000
2 20001 40000 </ p >< p > ( 3 行受影响)
</ p >