select t1.id as ID1, t2.id as ID2, abs(t1.val-t2.val) as DeltaVal
from A t1, A t2
where t1.ID<t2.ID and abs(t1.val-t2.val)<5
from A t1, A t2
where t1.ID<t2.ID and abs(t1.val-t2.val)<5
调试欢乐多
from A t1, A t2
where t1.ID<t2.ID and abs(t1.val-t2.val)<5
ID int,
Val int
)
insert @a select
1, 100
union all select
2 , 101
union all select
3 , 200
union all select
4 , 198
union all select
5 , 201
select t1.id as ID1, t2.id as ID2, abs(t1.val-t2.val) as DeltaVal
from @A t1, @A t2
where t1.ID<t2.ID and abs(t1.val-t2.val)<5--结果
ID1 ID2 DeltaVal
----------- ----------- -----------
1 2 1
3 4 2
3 5 1
4 5 3(所影响的行数为 4 行)