我有一个表,结果如下
labelID pointIDlabelID为主键,其表内容可能为
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的三条记)
要求高效
labelID pointIDlabelID为主键,其表内容可能为
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的三条记)
要求高效
(select labelID,pointID,abs(pointID-1022) cha from table_test order by cha)
where rownum<=6 order by labelID
也是pointID 不是number,to_number()
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
;
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
;
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);
实测结果:
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