一、SQLServer
大致分为两种情况:ID连续和ID不连续。
1.ID连续的情况:
select * from A where ID between 31 and 40
2.ID不连续的情况:
(1).两次对表查询,效率较低。
select top 10 * from A where ID not in (select top 30 ID from A)
(2).外层查询没有对表A进行查询,效率提高。
select top 10 * from (select top 40 ID from A order by ID) as a order by a.ID desc
(3).ROW_NUMBER()函数效率更高,SQL2005以上版本可用。
select * from(select *,ROW_NUMBER() over(order by ID)as 'userID' from A) as a where a.userID between 31 and 40
二、Oracle
select * from (select row_num as rid,* from A where rid <=40 ) t1 where t1.rid>=31;(必须两层嵌套)
嵌套原因参考:https://www.cnblogs.com/songhengchao/p/8996255.html
分页查询语句:
1:单表查询
SELECT * FROM (SELECT t.*,ROWNUM r FROM TABLE t WHERE ROWNUM <= pageNumber*pageSize) WHERE r >(pageNumber)*pageSize
2:两张表联查
SELECT * FROM (SELECT ROWNUM RN,XX.* FROM (SELECT 表名.字段名, 表名.字段名, 表名.字段名... FROM TABLE1 t1, TABLE2 t2 WHERE t1.字段=t2.字段) XX WHERE ROWNUM<=pageSize*pageNumber) WHERE RN >(pageNumber-1)*pageSize