问一个SQL的查询

[favorit]

表单test中有ID和Port两个字段，想查找这样的记录：后一条记录的ID和Port均比前一条记录少1。其中ID是记录的序号。

[skyboy0720]

ID是序号，增长的？！那如果是，怎么可能有你说的记录！
如果理解错误，还是把数据贴出来，比较直观些！

[yesyesyes]

看样子要用游标

[favorit]

假设一条记录是这样的
ID SRC DST Port
1 12 21 36
想查询这样的两条记录
4 73 36 11
5 21 54 12

[pignet]

declare @Table table( [ID] int primary key, SRC int, DST int, Port int )

insert into @Table
select 1 , 12 , 21 , 36
union select 4 , 73 , 36 , 11
union select 5 , 21 , 54 , 12
union select 6 , 21 , 54 , 13
union select 7 , 21 , 54 , 14


select distinct C.* from @Table C
inner join ( select A.[ID] as ID1, B.[ID] as ID2 from @Table A inner join @Table B on A.[ID] +1 = B.[ID] and A.Port + 1 = B.Port ) as D
on C.[ID] = D.ID1 or C.[ID] = D.ID2

[zheninchangjiang]

少1,越看越不明白

[favorit]

up一下