开发者社区> thinkyoung> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

oracle,mysql,SqlServer三种数据库的分页查询

简介: MySql: MySQL数据库实现分页比较简单,提供了 LIMIT函数。一般只需要直接写到sql语句后面就行了。LIMIT子 句可以用来限制由SELECT语句返回过来的数据数量,它有一个或两个参数,如果给出两个参数, 第一个参数指定返回的第一行在所有数据中的位置,从0开始(注意不是1),第二个参数指定最多返回行数。
+关注继续查看

MySql:

MySQL数据库实现分页比较简单,提供了 LIMIT函数。一般只需要直接写到sql语句后面就行了。
LIMIT子 句可以用来限制由SELECT语句返回过来的数据数量,它有一个或两个参数,如果给出两个参数, 第一个参数指定返回的第一行在所有数据中的位置,从0开始(注意不是1),第二个参数指定最多返回行数。例如:
select * from table WHERE … LIMIT 10; #返回前10行
select * from table WHERE … LIMIT 0,10; #返回前10行
select * from table WHERE … LIMIT 10,20; #返回第10-20行数据

 

Oracle:

考虑mySql中的实现分页,select * from 表名  limit 开始记录数,显示多少条;就可以实现我们的分页效果。

但是在oracle中没有limit关键字,但是有 rownum字段

rownum是一个伪列,是oracle系统自动为查询返回结果的每行分配的编号,第一行为1,第二行为2,以此类推。。。。

第一种:

 

复制代码 代码如下:

SELECT * FROM
(
                   SELECT A.*, ROWNUM RN
                   FROM (SELECT * FROM TABLE_NAME) A
                   WHERE ROWNUM <= 40
)
WHERE RN >= 21

 

 

 

其中最内层的查询SELECT * FROM TABLE_NAME表示不进行翻页的原始查询语句。ROWNUM <= 40和RN >= 21控制分页查询的每页的范围。

上面给出的这个分页查询语句,在大多数情况拥有较高的效率。分页的目的就是控制输出结果集大小,将结果尽快的返回。在上面的分页查询语句中,这种考虑主要体现在WHERE ROWNUM <= 40这句上。

选择第21到40条记录存在两种方法,一种是上面例子中展示的在查询的第二层通过ROWNUM <= 40来控制最大值,在查询的最外层控制最小值。而另一种方式是去掉查询第二层的WHERE ROWNUM <= 40语句,在查询的最外层控制分页的最小值和最大值。

第二种:

 

复制代码 代码如下:

select * from (select e.*,rownum  r from  (select * from emp order by sal desc) e ) e1 where e1.r>21 and e1.r<=40;

 

 

 

红色部分:按照工资降序排序并查询所有的信息。

棕色部分:得到红色部门查询的值,并查询出系统的rownum并指定上别名。这一句就比较关键,起了一个过渡的作用,首先要算出rownum来对红色部分指定上序号,也可以为蓝色外面部分用到这个变量。指定上查询的开始记录数和结束记录的条件。

蓝色部分:指定记录从第几条开始到第几条结束,取出棕色部门的值来作为查询条件的变量

总结:绝大多数的情况下,第一个查询的效率比第二个高得多。

SqlServer:

分页方案一:(利用Not In和SELECT TOP分页)

语句形式:

 

复制代码 代码如下:

SELECT TOP 10 *

 

FROM TestTable

WHERE (ID NOT IN

(SELECT TOP 20 id

FROM TestTable

ORDER BY id))

ORDER BY ID

 

SELECT TOP 页大小 *

FROM TestTable

WHERE (ID NOT IN

(SELECT TOP 页大小*页数 id

FROM 表

ORDER BY id))

ORDER BY ID

 

 

分页方案二:(利用ID大于多少和SELECT TOP分页)

语句形式:

 

复制代码 代码如下:

SELECT TOP 10 *

 

FROM TestTable

WHERE (ID >

(SELECT MAX(id)

FROM (SELECT TOP 20 id

FROM TestTable

ORDER BY id) AS T))

ORDER BY ID

 

SELECT TOP 页大小 *

FROM TestTable

WHERE (ID >

(SELECT MAX(id)

FROM (SELECT TOP 页大小*页数 id

FROM 表

ORDER BY id) AS T))

ORDER BY ID

 

 

分页方案三:(利用SQL的游标存储过程分页)

 

复制代码 代码如下:

create procedure XiaoZhengGe

 

@sqlstr nvarchar(4000), --查询字符串

@currentpage int, --第N页

@pagesize int --每页行数

as

set nocount on

declare @P1 int, --P1是游标的id

@rowcount int

exec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1,@rowcount=@rowcount output

select ceiling(1.0*@rowcount/@pagesize) as 总页数--,@rowcount as 总行数,@currentpage as 当前页

set @currentpage=(@currentpage-1)*@pagesize+1

exec sp_cursorfetch @P1,16,@currentpage,@pagesize

exec sp_cursorclose @P1

set nocount off

 

 

其它的方案:如果没有主键,可以用临时表,也可以用方案三做,但是效率会低。

建议优化的时候,加上主键和索引,查询效率会提高。

通过SQL 查询分析器,显示比较:结论是:

分页方案二:(利用ID大于多少和SELECT TOP分页)效率最高,需要拼接SQL语句

分页方案一:(利用Not In和SELECT TOP分页) 效率次之,需要拼接SQL语句

分页方案三:(利用SQL的游标存储过程分页) 效率最差,但是最为通用

在实际情况中,要具体分析。 

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
全方位解读服务网格(Service Mesh)的背景和概念
为了解决微服务框架的侵入性问题,我们引入服务网格。
1248 0
PolarDB 并行查询的前世今生
本文会深入介绍PolarDB MySQL在并行查询这一企业级查询加速特性上做的技术探索、形态演进和相关组件的实现原理,所涉及功能随PolarDB MySQL 8.0.2版本上线。
515 0
企业内部应用接入钉钉获取部门及人员信息
企业内部应用接入钉钉,同时通过API获取当前企业下部门及人员信息
1194 0
MySQL模糊查询再也用不着 like+% 了!
我们都知道 InnoDB 在模糊查询数据时使用 "%xx" 会导致索引失效,但有时需求就是如此,类似这样的需求还有很多,例如,搜索引擎需要根基用户数据的关键字进行全文查找,电子商务网站需要根据用户的查询条件,在可能需要在商品的详细介绍中进行查找,这些都不是B+树索引能很好完成的工作。 通过数值比较,范围过滤等就可以完成绝大多数我们需要的查询了。但是,如果希望通过关键字的匹配来进行查询过滤,那么就需要基于相似度的查询,而不是原来的精确数值比较,全文索引就是为这种场景设计的。
25673 0
阿里云云原生一体化数仓正式发布  助力企业数据驱动业务创新
云原生一体化数仓是集阿里云大数据产品MaxCompute、DataWorks、Hologres三种产品能力于一体的一站式大数据处理平台。核心是3个一体化和全链路数据治理能力,包括离线实时一体、湖仓一体、分析服务一体、全链路数据治理。
1617 0
全面公测|Grafana服务:一张图表胜过千行指标&日志
Grafana 帮助运维人员轻松处理各类运维过程中遇到的各类数据可视化与分析难题。目前阿里云 Grafana 服务全面免费公测,帮助企业轻松构建运维数据可视化平台,轻松实现数据驱动运维!
1164 0
一起学Golang系列(五)初次接触Go语言可能遇到的各种坑!
前面介绍了Go语言的基础语法,所谓磨刀不误砍柴工,希望大家还是能熟悉掌握这些基础知识,这样后面真正学起Go来才会得心应手。 作为初学者。Go语言的语法有些和java类似,但也有很多不一样的地方。刚开始都会遇到各种各样的坑。下面就来总结下学习go语言的过程中,遇到的各种坑。
1037 0
设备接入--海康摄像头SDK
springboot-对接海康摄像头,兼容window和Linux环境
1353 0
Flink SQL 在快手的扩展和实践
快手实时计算团队技术专家张静、张芒在 FFA 2021 的分享
1158 0
无影云桌面,企业与个人的应用神器
阿里云无影云桌面( Elastic Desktop Service)的原产品名为弹性云桌面,融合了无影产品技术后更名升级。它可以为您提供易用、安全、高效的云上桌面服务,帮助您快速构建、高效管理桌面办公环境,提供安全、灵活的办公体系。
276302 0
+关注
722
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载