Create Table A ([from] Int, [to] Int) Insert A Select 100, 200 Union All Select 200, 300 Union All Select 300, 400 Union All Select 500, 550 Union All Select 550, 600 Union All Select 600, 650 Union All Select 650, 700 GO Select A.[from], Min(B.[to]) As [to] From (Select [from] From A T Where Not Exists (Select [to] From A Where [to] = T.[from])) A Inner Join (Select [to] From A T Where Not Exists (Select [from] From A Where [from] = T.[to])) B On A.[from] <= B.[to] Group By A.[from] GO Drop Table A --Resutl /* from to 100 400 500 700 */
Create Table A ([from] Int, [to] Int) Insert A Select 100, 200 Union All Select 200, 300 Union All Select 300, 400 Union All Select 500, 550 Union All Select 550, 600 Union All Select 600, 650 Union All Select 650, 700 GOselect ta.[from],[to]=min(tb.[to]) from A as ta , A as tb where ta.[from] <= tb.[to] and not exists(Select [to] From A Where [to] = Ta.[from]) and Not Exists (Select [from] From A Where [from] = Tb.[to]) group by ta.[from]
([from] Int,
[to] Int)
Insert A Select 100, 200
Union All Select 200, 300
Union All Select 300, 400
Union All Select 500, 550
Union All Select 550, 600
Union All Select 600, 650
Union All Select 650, 700
GO
Select
A.[from],
Min(B.[to]) As [to]
From
(Select [from] From A T Where Not Exists (Select [to] From A Where [to] = T.[from])) A
Inner Join
(Select [to] From A T Where Not Exists (Select [from] From A Where [from] = T.[to])) B
On A.[from] <= B.[to]
Group By A.[from]
GO
Drop Table A
--Resutl
/*
from to
100 400
500 700
*/
([from] Int,
[to] Int)
Insert A Select 100, 200
Union All Select 200, 300
Union All Select 300, 400
Union All Select 500, 550
Union All Select 550, 600
Union All Select 600, 650
Union All Select 650, 700
GOselect ta.[from],[to]=min(tb.[to])
from A as ta , A as tb
where ta.[from] <= tb.[to]
and not exists(Select [to] From A Where [to] = Ta.[from])
and Not Exists (Select [from] From A Where [from] = Tb.[to])
group by ta.[from]
http://community.csdn.net/Expert/topic/5412/5412187.xml?temp=.2829553
http://community.csdn.net/Expert/topic/5424/5424304.xml?temp=.6343042