高级查询-查列值的前三和后3的值

高级查询-查列值的前三和后三的值
有这样的数据
197
2021
211
2212
2317
2419
2517
2618
2719
2820
2922
30 36
查19的话会得出
211
2212
2317
2419
2517
2618
2719
，

2419
2517
2618
2719
2820
2922
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>=@id 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>=@id 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*/