sql语句如下:SELECT * FROM ( SELECT r.*, ROWNUM RN FROM ( SELECT * FROM DUTSWITCH T order by DID ) r WHERE ROWNUM <= 10 ) WHERE RN > 0 显示出来多了rn这么一列,如何不显示这一列?
SELECT r.* FROM ( SELECT r.*, ROWNUM RN FROM ( SELECT * FROM DUTSWITCH T order by DID ) r WHERE ROWNUM <= 10 ) WHERE RN > 0
不对啊 ORA-00904:"R":invalid identifier
SELECT 一个个字段 FROM ( SELECT r.*, ROW_NUMBER() over(order by DID ) rn) t WHERE t.RN > 0 and t.rn<=10;楼主这个效率比你那个高并且是正确结果。你那个只会取前10条来排序。
楼主:最外层的条件 where rn>0这个条件有些诡异!直接用 SELECT r.* FROM ( SELECT * FROM DUTSWITCH T order by DID ) r WHERE ROWNUM <= 10 这样不知能否得到您要的结果!!
我这是一个求oracle分页的效果。楼上的tx看似达不到我要的效果。
前10行还是前10列。WHERE ROWNUM <= 10这个条件不是你自己加的吗
能不能写个完整的啊?你的那个t是table的意思吗?
里面少了from了。t是排序结果的别名。以下取排序后的第2页 SELECT 一个个字段 FROM ( SELECT r.*, ROW_NUMBER() over(order by DID ) rn FROM DUTSWITCH) t WHERE t.RN > 10 and t.rn <=20;
--每页显示10条数据,查询第20页 SELECT r.xxx,r.xxx,... FROM ( SELECT T.*,row_number()over(order by DID) FROM DUTSWITCH T ) r WHERE rn between 10*(20-1)+1 and 10*20;
--每页显示15条数据,查询第8页 SELECT r.xxx,r.xxx,... FROM ( SELECT T.*,row_number()over(order by DID) FROM DUTSWITCH T ) r WHERE rn between 15*(8-1)+1 and 15*8;
SELECT * FROM ( SELECT r.*, ROW_NUMBER() over(order by DID ) rn FROM DUTSWITCH) t WHERE t.RN > 0 and t.rn <=10;还是编译通不过啊。错误提示:ORA-00904:"R":invalid identifier
SELECT * FROM ( SELECT r.*, ROW_NUMBER() over(order by DID ) rn FROM DUTSWITCH r) t WHERE t.RN > 0 and t.rn <=10;
(
SELECT r.*, ROWNUM RN
FROM ( SELECT * FROM DUTSWITCH T order by DID ) r
WHERE ROWNUM <= 10
)
WHERE RN > 0
ORA-00904:"R":invalid identifier
SELECT r.* FROM ( SELECT * FROM DUTSWITCH T order by DID ) r WHERE ROWNUM <= 10
这样不知能否得到您要的结果!!
SELECT 一个个字段 FROM ( SELECT r.*, ROW_NUMBER() over(order by DID ) rn FROM DUTSWITCH) t WHERE t.RN > 10 and t.rn <=20;
--每页显示10条数据,查询第20页
SELECT r.xxx,r.xxx,...
FROM ( SELECT T.*,row_number()over(order by DID) FROM DUTSWITCH T ) r
WHERE rn between 10*(20-1)+1 and 10*20;
--每页显示15条数据,查询第8页
SELECT r.xxx,r.xxx,...
FROM ( SELECT T.*,row_number()over(order by DID) FROM DUTSWITCH T ) r
WHERE rn between 15*(8-1)+1 and 15*8;
SELECT * FROM ( SELECT r.*, ROW_NUMBER() over(order by DID ) rn FROM DUTSWITCH) t
WHERE t.RN > 0 and t.rn <=10;还是编译通不过啊。错误提示:ORA-00904:"R":invalid identifier
WHERE t.RN > 0 and t.rn <=10;
楼主google下row_number,能得到详细的介绍