数据库分页查询

 在我们使用查询语句的时候，经常要返回前几条或者中间某几行数据

mysql中：用limit,LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。初始记录行的偏移量是 0(而不是1)

eg ：mysql> SELECT * FROM table LIMIT 5,10;  // 检索记录行 6-15

如果为了检索从某一个偏移量到记录集的结束所有的记录行，可以指定第二个参数为 -1，如下： mysql> SELECT * FROM table LIMIT 95,-1; // 检索记录行 96-last.

 //如果只给定一个参数，它表示返回最大的记录行数目：
mysql> SELECT * FROM table LIMIT 5;     //检索前 5 个记录行

//换句话说，LIMIT n 等价于 LIMIT 0,n。

postgresql：limit 2 offset 1 相當於mysql的 limit 1,2。//返回行2到3
为了与 PostgreSQL 兼容，MySQL 也支持句法： LIMIT # OFFSET #。

 

ORACLE：返回20到40行

 

SELECT * FROM 
(
SELECT A.*, ROWNUM RN 
FROM (SELECT * FROM TABLE_NAME) A 
WHERE ROWNUM <= 40
)
WHERE RN >= 21

或

SELECT * FROM 
(
SELECT A.*, ROWNUM RN 
FROM (SELECT * FROM TABLE_NAME) A 
)
WHERE RN BETWEEN 21 AND 40
方式一执行效率高

使用分析函数的分页写法:

最好还是利用分析函数
row_number() over ( partition by col1 order by col2 )
比如想取出100-150条记录,按照tname排序
select tname,tabtype from (
  select tname,tabtype,row_number() over ( order by tname ) rn from tab
)
where rn between 100 and 150;

用 Oracle rownum 排序有几个需要注意：
1. 必须先排序，再取 rownum . 这样分页 SQL 至少要用三个 select
2. 如果用ROW_NUMBER() OVER，则可以只用两个 select
3. 数据肯定要排序的。不然，如果数据有 11 页，用户在第 1 页没有找到自己想要的数据，跳到第 8 页也没有找到，那么，他怎么知道，下一步是往前翻页还是往后翻页？
4. 用 ROW_NUMBER() OVER 没有翻到最后一页很慢的说法。建议用这种方法，这是标准 SQL 2003 中的写法，SQL Server 2005/DB2/Oracle 9i 都支持。
5. select count 里面的 where 要与取当前页的 where 写得一样。

SQL Server:
方式一:
select * 
from (select ROW_NUMBER() Over(order by billId) as rowNum,user.*
  from user 
  where convert(varchar(10),cerateDate,21)>='2009-04-01' 
  and convert(varchar(10),cerateDate,21)<='2009-04-30'
 ) table1 
 
where rowNum > 10 and rowNum <= 20 order by cerateDate desc

方式二:
select * 
from (select top 10 * 
  from (select top 100 * 
  from cpcode 
  order by code) a 
  order by code desc) as b  
order by code  

MySQL:
select * from cpcode limit 90,10 

PostGreSQL:

select *
from cp_project order by id
offset 10 limit 10

offset 开始行数,limit 返回记录数