日期:2014-05-17  浏览次数:20546 次

高级查询-查列值的前三和后三的值
有这样的数据
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 @test
select 19, 7 union all
select 20, 21 union all
select 21, 1 union all
select 22, 12 union all
select 23, 17 union all
select 24, 19 union all
select 25, 17 union all
select 26, 18 union all
select 27, 19 union all
select 28, 20 union all
select 29, 22 union all
select 30, 36

declare @id int 
set @id=19

select * from @test
where col1>=(select top 1 col1 from (select top 3 col1 from @test where col1>=@id order by col1) t order by col1 desc)
union all
select * from @test
where 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          1
22          12
23          17
24          19
25          17
26          18
27          19
28          20
29          22
30          36
24          19
25          17
26          18
27          19
28          20
29          22
30          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 ALL
SELECT 20,21 UNION ALL
SELECT 21,1 UNION ALL
SELECT 22,12 UNION ALL
SELECT 23,17 UNION ALL
SELECT 24,19 UNION ALL
SELECT 25,17 UNION ALL
SELECT 26,18 UNION ALL
SELECT 27,19 UNION ALL
SELECT 28,20 UNION ALL
SELECT 29,22 UNION ALL
SELECT 30,36
--------------开始查询--------------------------

SELECT  DISTINCT a.* FROM  [tb]  AS a
JOIN(
SELECT  * FROM  [tb] WHERE [col2]=19
) b
ON abs(a.[col1]-b.[col1])<=3

----------------结果----------------------------
/* 
col1    col2
21    1
22    12
23    17
24    19
25    17
26    18
27    19
28    20
29    22
30    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 a
JOIN(
SELECT  * FROM  t WHERE [col2]=19
) b
ON abs(a.[row_id]-b.[row_id])<=3
----------------结果----------------------------
/* 
col1    col2
21    1
22    12
23    17
24    19
25    17
26    18
27    19
28    20
29    22
30    36
*/