查询31到40条记录的各种方法

平常工作，尤其是面试中经常遇到这样一个问题，查询表A中31到40条的记录，ID可能是不连续的。

如果ID连续

select * from A where ID between 31 and 40

如果ID不连续，提供三种写法

www.2cto.com

--两次对表A查询效率较低

select top 10 * from A where ID not in (select top 30 ID from A)

--外层查询没有对表A查询，效率大有提高

select top 10 * from (select top 40 * from A) as t order by t.ID desc

--ROW_NUMBER()函数效率更高，sqlserver2005以及以上版本中才可以使用

select * from (select ROW_NUMBER() over(order by ID) as 'sequence',A.* from A ) as t where t.sequence between 31 and 40