代码为网上找的,我只是负责收集整理一下。
oracle:
SELECT * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME) A
WHERE ROWNUM <= 40
)
WHERE RN >= 21
其中最内层的查询SELECT * FROM TABLE_NAME表示不进行翻页的原始查询语句。ROWNUM <= 40和RN >= 21控制分页查询的每页的范围
第二种:
SELECT * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME) A
)
WHERE RN BETWEEN 21 AND 40
对比这两种写法,绝大多数的情况下,第一个查询的效率比第二个高得多。
sql server:
第一种方案、最简单、普通的方法:
查询出来你不需要的,然后使用not in
SELECT TOP 30 * FROM ARTICLE WHERE ID NOT IN(SELECT TOP 45000 ID FROM ARTICLE ORDER BY YEAR DESC, ID DESC) ORDER BY YEAR DESC,ID DESC
第二种方案:
查询查来你不需要的+当前的页码数 然后倒叙排序 在使用top方法 页码数
SELECT * FROM (SELECT TOP 30 * FROM (SELECT TOP 45030 * FROM ARTICLE ORDER BY YEAR DESC, ID DESC) f ORDER BY f.YEAR ASC, f.ID DESC) s ORDER BY s.YEAR DESC,s.ID DESC
第三种方案:
表示没看懂,有看懂了的可以给我讲解一下email:lvjianyu2007@163.com谢谢
SELECT * FROM ARTICLE w1,
(
SELECT TOP 30 ID FROM
( SELECT TOP 50030 ID, YEAR FROM ARTICLE ORDER BY YEAR DESC, ID DESC
) w ORDER BY w.YEAR ASC, w.ID ASC
) w2 WHERE w1.ID = w2.ID ORDER BY w1.YEAR DESC, w1.ID DESC
第四种方案:
同方案2 只不过又加了外层封装
SELECT * FROM ARTICLE w1
WHERE ID in
(
SELECT top 30 ID FROM
(
SELECT top 45030 ID, YEAR FROM ARTICLE ORDER BY YEAR DESC, ID DESC
) w ORDER BY w.YEAR ASC, w.ID ASC
)
ORDER BY w1.YEAR DESC, w1.ID DESC
第五种方案:
先查询出来前50030条数据 然后再根据外面的限制 >50000 结果就出来50030-50000条数据,
实现了一个和top指令差不多的查询。
SELECT w2.n, w1.* FROM ARTICLE w1, (
SELECT TOP 50030 row_number() OVER (ORDER BY YEAR DESC, ID DESC) n, ID FROM ARTICLE ) w2
WHERE w1.ID = w2.ID AND w2.n > 50000 ORDER BY w2.n ASC
mysql:
正向查找法分页:
SELECT * FROM `abc` WHERE `BatchID` = 123 LIMIT 839960, 40 ;
反向查找sql:
SELECT * FROM `abc` WHERE `BatchID` = 123 ORDER BY InputDate DESC LIMIT 788775, 40;
hibernate:
criteria.setFirstResult(3);
criteria.setMaxResults(2);