数据库分页检索

1、Not in --两次Rep_InsuranceStandard表查询，效率慢

select top 10 * from Rep_InsuranceStandard where DisplayOrder not in 
(
select top 30 DisplayOrder from Rep_InsuranceStandard order by DisplayOrder
) order by DisplayOrder

2、第二次获取在第一次基础上，效率提高

select top 10 * from (select top 40 * from Rep_InsuranceStandard order by DisplayOrder) a order by DisplayOrder desc

3、row_number() over  -- sqlserve2005后支持

select * from 
    (select ROW_NUMBER() over (order by displayorder) as rownum, * from Rep_InsuranceStandard )m 
where m.rownum > 30 and m.rownum <=40

4、offset 30 row fetch next 10 rows only  --sqlserve2012后支持，和3效率一样，但是更优雅

select * from Rep_InsuranceStandard order by DisplayOrder offset 30 row fetch next 10 rows only