下面给出Oracle/Sqlserver/MySQL三种数据库先出第m至第n条记录的方法：
获取按照F1字段升序排序的，从m至n行记录
注意，F1一定要做索引或主键，最好是主键
SqlServer
select t2.* from (Select top n-m t1.* from (Select top n * from Tablename as t order by t.F1) as t1 order by t1.F1 desc) as t2 order by t2.F1
select t2.* from
(
select top 20 t1.* from
(
select top 500020 * from dbgen as t order by t.F1
)
as t1 order by t1.F1 desc
)
as t2 order by t2.F1
Oracle
select * from (select rownum r, t.* from Tablename t where r m and t1.r <= m
select * from (select rownum r, t.* from DBTEST t where rownum<300020) ss where ss.r > 300000 and ss.r <= 300020
MySQL
最简单
select * from Tablename t order by t.F1 limit n,m-n+1
select * from DBtest t order by t.F1 limit 300000,20
性能对比：
三者差不多少！
上面实例中的语句在50万条记录的表中选取第50万左右的记录20条，均不超过1秒。
其它数据库我相信也能找到差不多的方法。
获取按照F1字段升序排序的，从m至n行记录
注意，F1一定要做索引或主键，最好是主键
SqlServer
select t2.* from (Select top n-m t1.* from (Select top n * from Tablename as t order by t.F1) as t1 order by t1.F1 desc) as t2 order by t2.F1
select t2.* from
(
select top 20 t1.* from
(
select top 500020 * from dbgen as t order by t.F1
)
as t1 order by t1.F1 desc
)
as t2 order by t2.F1
Oracle
select * from (select rownum r, t.* from Tablename t where r
select * from (select rownum r, t.* from DBTEST t where rownum<300020) ss where ss.r > 300000 and ss.r <= 300020
MySQL
最简单
select * from Tablename t order by t.F1 limit n,m-n+1
select * from DBtest t order by t.F1 limit 300000,20
性能对比：
三者差不多少！
上面实例中的语句在50万条记录的表中选取第50万左右的记录20条，均不超过1秒。
其它数据库我相信也能找到差不多的方法。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/12122734/viewspace-343275/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
ar常用tables
下一篇：
转：新会计准则会计科目表
请登录后发表评论
登录
全部评论
