面试时遇到的笔试题：查询31-40条记录

面试中经常会遇到，查询31-40条记录的笔试题目，一般情况下ID是不连续的：

ID连续： 

 select  * from A where ID between 30 and 40;    

ID不连续：

 1. 此种写法效率非常低：

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

 2. 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;   

 3. 海量数据做查询时更高效：

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

 4. sqlserver2012最新写法：

     select * from A order by id offset 30 rows fetch next 10 rows only;

 5. 支持多数据，支持多排序字段，效率高：

　　select top 10 *
  　　from a
　　where id in (select top 10 id
                　　from (select top 40 id from A order by ID desc) as t
               　　order by t.ID)
　　order by t.ID desc;

 6. 效率勉强可以：

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

 7. 此方法不一定适用于所有数据库，自行测试：

    select * from A order by A.ID asc LIMIT 30,10;

 

欢迎继续补充............