我有一个表,结果如下
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的三条记)
要求高效
解决方案 »
- Oracle 11G 没有提供网页上用SQL语言操作数据库的界面了吗?
- oreacl数据库连接问题
- oracle 10g数据库迁移表空间无法读取问题
- 在过程中怎么调用 系统命令 如:SQLLDR
- 请推荐几本oracle的书籍
- 如何将数值型字段显示为文本
- Oracle数据库出错,大家来帮忙看看,指导一下怎么解决呀~~救命哟!
- 关于游标的问题,请解答
- call与execute调用存储过程(在sqlplus中)有什么区别
- 高分相赠!!!!!!!!!!!如何全文检索BLOB字段(不是CLOB),以及如何在SQLPLUS上把结果显示出来!
- 数据库设计问题
- 求个SQL语句或效率稍高一点的其它解决方案,我脑子转不过弯(MySQL))
(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