1. 分页SQL语句
1.1 定位
select top 10 * from tbl_FlightsDetail where FlightsDetailID>( select max(FlightsDetailID) from ( select top 3000000 FlightsDetailID from tbl_FlightsDetail order by FlightsDetailID ) as t ) order by FlightsDetailID
先查出 top 300000,再聚合取这个集合中最大的Id1,再过滤 id大于id1的集合(上图中使用到索引),再取top 10 条。
1.2 利用Not In
select top 10* from tbl_FlightsDetail where FlightsDetailID not in ( select top 3000000 FlightsDetailID from tbl_FlightsDetail order by FlightsDetailID) order by FlightsDetailID和方法一类似,只是过滤where条件不一样,这里用到的是not in,上图中没有用到索引,耗时8秒
1.3 利用颠颠倒倒top
select top 10* from ( select top 3000010* from tbl_FlightsDetail order by FlightsDetailID) as t order by t.FlightsDetailID desc先取 前面3000010条记录,再倒序,这时再取前面10条即是300001 到300010条记录,没有用到索引,耗时11秒
1.4 ROW_NUMBER()函数
select * from ( select *,ROW_NUMBER() OVER (ORDER BY FlightsDetailID) as rank from tbl_FlightsDetail) as t where t.rank between 3000001 and 3000010Sql 2005版本或以上支持,也没用到索引,耗时2秒,速度还不错。
1.5 利用IN
select top 10 * from tbl_FlightsDetail where FlightsDetailID in( select top 10 FlightsDetailID from( select top 3000010 FlightsDetailID from tbl_FlightsDetail order by FlightsDetailID ) as t order by t.FlightsDetailID desc ) order by FlightsDetailID
2. 问题 (sqlserver @P0 附近有语法错误)
2.1 CreateStatement 查询数据
直接拼接SQL语句查询
2.2 PrepareStatement查询数据
Top后不支持直接加占位符,需将?直接拼接成SQL中的top 10
2.3 MyBatis查询数据
Top后不支持占位符, 把#{var}改成${var}
具体解析见 http://blog.csdn.net/luo4105/article/details/51174257
参照:http://www.cnblogs.com/qqlin/archive/2012/11/01/2745161.html