请教一条SQL语句(如何从表A得到表B)？
表A: from to
100 200
200 300
300 400
500 550
550 600
600 650
650 700
表B: 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
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
/*
fromto
100400
500700
*/
[解决办法]
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 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]
