在我们使用查询语句的时候,经常要返回前几条或者中间某几行数据
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 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 返回记录数