有这样的数据
19 7
20 21
21 1
22 12
23 17
24 19
25 17
26 18
27 19
28 20
29 22
30 36
查19的话会得出
21 1
22 12
23 17
24 19
25 17
26 18
27 19
，
24 19
25 17
26 18
27 19
28 20
29 22
30 36
这样可以实现吗用SQL。
------解决方案--------------------
看不懂，第一列是序号吗？
------解决方案--------------------
- SQL code
declare @test table(col1 int,col2 int)insert into @testselect 19, 7 union allselect 20, 21 union allselect 21, 1 union allselect 22, 12 union allselect 23, 17 union allselect 24, 19 union allselect 25, 17 union allselect 26, 18 union allselect 27, 19 union allselect 28, 20 union allselect 29, 22 union allselect 30, 36declare @id int set @id=19select * from @testwhere col1>=(select top 1 col1 from (select top 3 col1 from @test where col1>[email protected] order by col1) t order by col1 desc)union allselect * from @testwhere col1>=(select top 1 col1 from (select top 6 col1 from @test where col1>[email protected] order by col1) t order by col1 desc)/*col1 col2----------- -----------21 122 1223 1724 1925 1726 1827 1928 2029 2230 3624 1925 1726 1827 1928 2029 2230 36*/
------解决方案--------------------
- SQL code
--> 测试数据：[tb]IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]GO CREATE TABLE [tb]([col1] INT,[col2] INT)INSERT [tb]SELECT 19,7 UNION ALLSELECT 20,21 UNION ALLSELECT 21,1 UNION ALLSELECT 22,12 UNION ALLSELECT 23,17 UNION ALLSELECT 24,19 UNION ALLSELECT 25,17 UNION ALLSELECT 26,18 UNION ALLSELECT 27,19 UNION ALLSELECT 28,20 UNION ALLSELECT 29,22 UNION ALLSELECT 30,36--------------开始查询--------------------------SELECT DISTINCT a.* FROM [tb] AS aJOIN(SELECT * FROM [tb] WHERE [col2]=19) bON abs(a.[col1]-b.[col1])<=3----------------结果----------------------------/* col1 col221 122 1223 1724 1925 1726 1827 1928 2029 2230 36*/
------解决方案--------------------
- SQL code
--应该重新生成一次序号，这个方法才正确，因为实际中是有断号出现的。;WITH t AS(SELECT *,row_id=ROW_NUMBER()OVER(ORDER BY [col1]) FROM [tb])SELECT DISTINCT a.col1,a.col2 FROM t AS aJOIN(SELECT * FROM t WHERE [col2]=19) bON abs(a.[row_id]-b.[row_id])<=3----------------结果----------------------------/* col1 col221 122 1223 1724 1925 1726 1827 1928 2029 2230 36*/