请教一Sql

[chinazhousheng]

我有一个表，结果如下
labelID pointID

labelID为主键，其表内容可能为
1 1003
2 1001
3 1008
4 1010
5 1021
6 1023
7 1031
8 1018
9 1013
10 1025

现在要求pointID 1022前后3条记录（即大于1022的三条记录和小于1022的三条记）
要求高效

[qinglang1987]

一个sql么办法搞定啊，写个函数或者存储过程吧

[chinazhousheng]

望给思路

[yanyuan1024]

一条SQL 有难度啊，用rownum?

[xpingping]

select labelID，pointID from 
(select labelID，pointID，abs(pointID-1022) cha from table_test order by cha)
where rownum<=6 order by labelID

也是pointID 不是number,to_number()

[princes1]

SELECT *
FROM (SELECT A.*, ROW_NUMBER() OVER(ORDER BY pointID) RN FROM test_o A),
(SELECT RN + 3 AS HI ,RN-3 LOW
FROM (SELECT A.*, ROW_NUMBER() OVER(ORDER BY pointID) RN FROM test_o A)
WHERE pointID = 1005) B
WHERE RN <=HI AND RN>=LOW
;

[princes1]

如果pointID有重复的话 需要优化

[princes1]

SELECT *
FROM (SELECT A.*, ROW_NUMBER() OVER(ORDER BY pointID) RN FROM test_o A),
(SELECT MAX(RN + 3) AS HI ,min(RN)-3 LOW
FROM (SELECT A.*, ROW_NUMBER() OVER(ORDER BY pointID) RN FROM test_o A)
WHERE pointID = 1005) B
WHERE RN <=HI AND RN>=LOW
;

[我心飞翔]

实测数据：

-- 查询PointID大于1022的前3条记录和小于1022的三条记录
CREATE TABLE T65 
(
    LabelID NUMBER(4),
    PointID NUMBER(4)
);

INSERT INTO T65 VALUES(1, 1033);
INSERT INTO T65 VALUES(2, 1001);
INSERT INTO T65 VALUES(3, 1008);
INSERT INTO T65 VALUES(4, 1010);
INSERT INTO T65 VALUES(5, 1021);
INSERT INTO T65 VALUES(6, 1023);
INSERT INTO T65 VALUES(7, 1031);
INSERT INTO T65 VALUES(8, 1018);
INSERT INTO T65 VALUES(9, 1013);
INSERT INTO T65 VALUES(10, 1025);

实测结果：

[xpingping]

select labelID，pointID from (select labelID，pointID，abs(pointID-1022) cha 
from table_test where pointID <1022 order by cha)where rownum<=3
union all
select labelID，pointID from (select labelID，pointID，abs(pointID-1022) cha 
from table_test where pointID >=1022 order by cha)where rownum<=3

前面给的错了，sorry