当前位置：首页 -> AI编程基础 -> 数据库编程

Oracle ROWNUM及分页查询的一点总结(二)

2014-11-24 15:56:32 · 作者: · 浏览: 1
标签: Oracle ROWNUM 查询 一点 总结
E ROWNUM <= 50) A
WHERE A.RN >= 41
UNION ALL
SELECT RD FROM
(SELECT ROWNUM RN,ROWID RD, TT.* FROM (SELECT * FROM TT5 ORDER BY OBJECT_NAME)TT WHERE ROWNUM <= 60) A
WHERE A.RN >= 51
UNION ALL
SELECT RD FROM
(SELECT ROWNUM RN,ROWID RD, TT.* FROM (SELECT * FROM TT5 ORDER BY OBJECT_NAME)TT WHERE ROWNUM <= 71) A
WHERE A.RN >= 61
) A GROUP BY RD HAVING COUNT(*)>1;
RD
------------------
AAAPB2AAEAAAFS1AAt
AAAPB2AAEAAAFS1AAu
AAAPB2AAEAAAFS1AAf
AAAPB2AAEAAAFS1AA1
AAAPB2AAEAAAFS1AAh
--下面的方式不会产生重复值，因为数据的取值顺序不会发生变化
SELECT RD FROM
(
SELECT RD FROM
(SELECT ROWNUM RN,ROWID RD, TT.* FROM (SELECT * FROM TT5 )TT WHERE ROWNUM <= 10) A
WHERE A.RN >= 1
UNION ALL
SELECT RD FROM
(SELECT ROWNUM RN,ROWID RD, TT.* FROM (SELECT * FROM TT5 )TT WHERE ROWNUM <= 20) A
WHERE A.RN >= 11
UNION ALL
SELECT RD FROM
(SELECT ROWNUM RN,ROWID RD, TT.* FROM (SELECT * FROM TT5 )TT WHERE ROWNUM <= 30) A
WHERE A.RN >= 21
UNION ALL
SELECT RD FROM
(SELECT ROWNUM RN,ROWID RD, TT.* FROM (SELECT * FROM TT5 )TT WHERE ROWNUM <= 40) A
WHERE A.RN >= 31
UNION ALL
SELECT RD FROM
(SELECT ROWNUM RN,ROWID RD, TT.* FROM (SELECT * FROM TT5 )TT WHERE ROWNUM <= 50) A
WHERE A.RN >= 41
UNION ALL
SELECT RD FROM
(SELECT ROWNUM RN,ROWID RD, TT.* FROM (SELECT * FROM TT5 )TT WHERE ROWNUM <= 60) A
WHERE A.RN >= 51
UNION ALL
SELECT RD FROM
(SELECT ROWNUM RN,ROWID RD, TT.* FROM (SELECT * FROM TT5 )TT WHERE ROWNUM <= 71) A
WHERE A.RN >= 61
) A GROUP BY RD HAVING COUNT(*)>1;