一、MYSQL之 Limit
SELECT * FROM `e-commerce`.computer c where c.price=15 Having c.id in (1,2) order by c.id desc limit 0,1 ;
二、oracle之rownum
方法1:
SELECT * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME) A
WHERE ROWNUM <= 40
)
WHERE RN >= 21
方法2:
SELECT * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME) A
)
WHERE RN BETWEEN 21 AND 40
对比这两种写法,绝大多数的情况下,第一个查询的效率比第二个高得多。
三、Sqlserver 之ROW_NUMBER()
方法1:
适用于 SQL Server 2000/2005
SELECT
TOP
页大小
*
FROM table1
WHERE id NOT IN
(
SELECT TOP (页大小 * (页数 - 1 )) id FROM table1 ORDER BY id
)
ORDER BY id
FROM table1
WHERE id NOT IN
(
SELECT TOP (页大小 * (页数 - 1 )) id FROM table1 ORDER BY id
)
ORDER BY id
方法2:
适用于 SQL Server 2000/2005
SELECT
TOP
页大小
*
FROM table1
WHERE id >
(
SELECT ISNULL ( MAX (id), 0 )
FROM
(
SELECT TOP 页大小 * (页数 - 1 ) id FROM table1 ORDER BY id
) A
)
ORDER BY id
FROM table1
WHERE id >
(
SELECT ISNULL ( MAX (id), 0 )
FROM
(
SELECT TOP 页大小 * (页数 - 1 ) id FROM table1 ORDER BY id
) A
)
ORDER BY id
方法3.适用于 SQL Server 2005
SELECT
TOP
页大小
*
FROM
(
SELECT ROW_NUMBER() OVER ( ORDER BY id) AS RowNumber, * FROM table1
) A
WHERE RowNumber > 页大小 * (页数 - 1 )
FROM
(
SELECT ROW_NUMBER() OVER ( ORDER BY id) AS RowNumber, * FROM table1
) A
WHERE RowNumber > 页大小 * (页数 - 1 )
分页方案二:(利用ID大于多少和SELECT TOP分页)效率最高,需要拼接SQL语句
分页方案一:(利用Not In和SELECT TOP分页) 效率次之,需要拼接SQL语句
分页方案三:(利用SQL的游标存储过程分页) 效率最差,但是最为通用