mysql rownumber over_MySQL 8.0窗口函数--row_number over..应用

最新推荐文章于 2023-06-01 14:48:36 发布
最新推荐文章于 2023-06-01 14:48:36 发布
阅读量400 收藏 1
点赞数 1
文章标签： mysql rownumber over
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_32297123/article/details/113229364
版权

EG：取出没门课程的第一名：

CREATE TABLE window_test

(id int,

name text,

subject text,

score numeric

);

INSERT INTO window_test VALUES (1,'小黄','数学',99.5), (2,'小黄','语文',89.5),(3,'小黄','英语',79.5), (4,'小黄','物理',99.5), (5,'小黄','化学',98.5), (6,'小红','数学',89.5), (7,'小红','语文',99.5), (8,'小红','英语',79.5), (9,'小红','物理',89.5), (10,'小红','化学',69.5),(11,'小绿','数学',89.5), (12,'小绿','语文',91.5), (13,'小绿','英语',92.5),(14,'小绿','物理',93.5), (15,'小绿','化学',94.5);

root@my3308.sock-8.0.11>[employees]>select * from window_test;

+------+--------+---------+-------+

| id | name | subject | score |

+------+--------+---------+-------+

| 1 | 小黄 | 数学 | 100 |

| 2 | 小黄 | 语文 | 90 |

| 3 | 小黄 | 英语 | 80 |

| 4 | 小黄 | 物理 | 100 |

| 5 | 小黄 | 化学 | 99 |

| 6 | 小红 | 数学 | 90 |

| 7 | 小红 | 语文 | 100 |

| 8 | 小红 | 英语 | 80 |

| 9 | 小红 | 物理 | 90 |

| 10 | 小红 | 化学 | 70 |

| 11 | 小绿 | 数学 | 90 |

| 12 | 小绿 | 语文 | 92 |

| 13 | 小绿 | 英语 | 93 |

| 14 | 小绿 | 物理 | 94 |

| 15 | 小绿 | 化学 | 95 |

+------+--------+---------+-------+

15 rows in set (0.00 sec)

直接写成如下形式：发现结果并不对。如何解决呢？

root@my3308.sock-8.0.11>[employees]>select id,name,subject,max(score) from window_test group by subject;

+------+--------+---------+------------+

| id | name | subject | max(score) |

+------+--------+---------+------------+

| 1 | 小黄 | 数学 | 100 |

| 2 | 小黄 | 语文 | 100 |

| 3 | 小黄 | 英语 | 93 |

| 4 | 小黄 | 物理 | 100 |

| 5 | 小黄 | 化学 | 99 |

+------+--------+---------+------------+

5 rows in set (0.00 sec)

1.当没有窗口函数之前我们使用哪几种方法：

方法一：使用jion 形式

a.按照subject 分组，取出score 的最大值

root@my3308.sock-8.0.11>[employees]>select subject,max(score) from window_test group by subject;

+---------+------------+

| subject | max(score) |

+---------+------------+

| 数学 | 100 |

| 语文 | 100 |

| 英语 | 93 |

| 物理 | 100 |

| 化学 | 99 |

+---------+------------+

5 rows in set (0.00 sec)

b.根据subject,score 的值再去关键Window_test表取出其他几列的值

root@my3308.sock-8.0.11>[employees]>select b.* from

(select subject,max(score) as score from window_test group by subject

) a

join window_test b on a.score = b.score and a.subject = b.subject;

+------+--------+---------+-------+

| id | name | subject | score |

+------+--------+---------+-------+

| 1 | 小黄 | 数学 | 100 |

| 4 | 小黄 | 物理 | 100 |

| 5 | 小黄 | 化学 | 99 |

| 7 | 小红 | 语文 | 100 |

| 13 | 小绿 | 英语 | 93 |

+------+--------+---------+-------+

5 rows in set (0.00 sec)

此时的结果就是证券的了。

方法二：例如group by 特性来写

a.window_test 按照subject asc,score desc 来排序

root@my3308.sock-8.0.11>[employees]>select * from window_test order by subject, score desc;

+------+--------+---------+-------+

| id | name | subject | score |

+------+--------+---------+-------+

| 5 | 小黄 | 化学 | 99 |

| 15 | 小绿 | 化学 | 95 |

| 10 | 小红 | 化学 | 70 |

| 1 | 小黄 | 数学 | 100 |

| 6 | 小红 | 数学 | 90 |

| 11 | 小绿 | 数学 | 90 |

| 4 | 小黄 | 物理 | 100 |

| 14 | 小绿 | 物理 | 94 |

| 9 | 小红 | 物理 | 90 |

| 13 | 小绿 | 英语 | 93 |

| 3 | 小黄 | 英语 | 80 |

| 8 | 小红 | 英语 | 80 |

| 7 | 小红 | 语文 | 100 |

| 12 | 小绿 | 语文 | 92 |

| 2 | 小黄 | 语文 | 90 |

+------+--------+---------+-------+

15 rows in set (0.00 sec)

b.进行对此结果进行group by

root@my3308.sock-8.0.11>[employees]>select * from (select * from window_test order by subject, score desc) a group by a.subject;

+------+--------+---------+-------+

| id | name | subject | score |

+------+--------+---------+-------+

| 1 | 小黄 | 数学 | 100 |

| 2 | 小黄 | 语文 | 90 |

| 3 | 小黄 | 英语 | 80 |

| 4 | 小黄 | 物理 | 100 |

| 5 | 小黄 | 化学 | 99 |

+------+--------+---------+-------+

5 rows in set (0.00 sec)

有没有注意到此时的结果是不正确的，那么我们来看下具体的执行计划。哪里出错了呢？

root@my3308.sock-8.0.11>[employees]>explain select * from (select * from window_test order by subject, score desc) a group by a.subject;

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: window_test

partitions: NULL

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 15

filtered: 100.00

Extra: Using temporary

1 row in set, 1 warning (0.00 sec)

从执行计划中我们得到了什么？有没有发现，视图进行了合并了。explain select_type = simple 。那么我们其实可以通过show warnings\G来查看具体的mysql 改写的语句

root@my3308.sock-8.0.11>[employees]>show warnings\G

*************************** 1. row ***************************

Level: Note

Code: 1003

Message: /* select#1 */

select `employees`.`window_test`.`id` AS `id`,

`employees`.`window_test`.`name` AS `name`,

`employees`.`window_test`.`subject` AS `subject`,

`employees`.`window_test`.`score` AS `score`

from `employees`.`window_test` group by `employees`.`window_test`.`subject`

1 row in set (0.00 sec)

可以直接从show warnings很直观的看到mysql 优化器给改写的SQL。

而在5.6上没有进行合并，5.7 8.0上进行了视图合并，我们应该如何避免呢？

1.5.7优化器新添加了什么功能造成视图合并？

2.如进行避免视图合并有哪几种方法：

方法一：

root@my3308.sock-8.0.11>[employees]>set optimizer_switch ="derived_merge=off";

Query OK, 0 rows affected (0.00 sec)

直接将derived_merge 进行关闭了。这种方法太暴力了。。。。非常不推荐，那么我们应该使用什么方法来解决此问题呢？大家自己思考下

root@my3308.sock-8.0.11>[employees]>set optimizer_switch ="derived_merge=off";

Query OK, 0 rows affected (0.00 sec)

root@my3308.sock-8.0.11>[employees]>select * from (select * from window_test order by subject, score desc) a group by a.subject;

+------+--------+---------+-------+

| id | name | subject | score |

+------+--------+---------+-------+

| 5 | 小黄 | 化学 | 99 |

| 1 | 小黄 | 数学 | 100 |

| 4 | 小黄 | 物理 | 100 |

| 13 | 小绿 | 英语 | 93 |

| 7 | 小红 | 语文 | 100 |

+------+--------+---------+-------+

5 rows in set (0.00 sec)

root@my3308.sock-8.0.11>[employees]>explain select * from (select * from window_test order by subject, score desc) a group by a.subject;

+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-----------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-----------------+

| 1 | PRIMARY | | NULL | ALL | NULL | NULL | NULL | NULL | 15 | 100.00 | Using temporary |

| 2 | DERIVED | window_test | NULL | ALL | NULL | NULL | NULL | NULL | 15 | 100.00 | Using filesort |

+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-----------------+

2 rows in set, 1 warning (0.00 sec)

此时结果就对了

方法三：使用if(@_subject=t.subject ,@rn:=@rn+1,@rn:=1) 形式来模拟

a.使用if(@_subject=t.subject ,@rn:=@rn+1,@rn:=1) 来进行subject 分组，以及显示各个row_num

root@my3308.sock-8.0.11>[employees]>select t.*,if(@_subject=t.subject ,@rn:=@rn+1,@rn:=1) as rn ,@_subject:=t.subject as _subject

-> from(select t.* from window_test t order by subject,score desc ) t , (select @rn:=0 rn ,@_subject:='') b;

+------+--------+---------+-------+------+----------+

| id | name | subject | score | rn | _subject |

+------+--------+---------+-------+------+----------+

| 5 | 小黄 | 化学 | 99 | 1 | 化学 |

| 15 | 小绿 | 化学 | 95 | 2 | 化学 |

| 10 | 小红 | 化学 | 70 | 3 | 化学 |

| 1 | 小黄 | 数学 | 100 | 1 | 数学 |

| 6 | 小红 | 数学 | 90 | 2 | 数学 |

| 11 | 小绿 | 数学 | 90 | 3 | 数学 |

| 4 | 小黄 | 物理 | 100 | 1 | 物理 |

| 14 | 小绿 | 物理 | 94 | 2 | 物理 |

| 9 | 小红 | 物理 | 90 | 3 | 物理 |

| 13 | 小绿 | 英语 | 93 | 1 | 英语 |

| 3 | 小黄 | 英语 | 80 | 2 | 英语 |

| 8 | 小红 | 英语 | 80 | 3 | 英语 |

| 7 | 小红 | 语文 | 100 | 1 | 语文 |

| 12 | 小绿 | 语文 | 92 | 2 | 语文 |

| 2 | 小黄 | 语文 | 90 | 3 | 语文 |

+------+--------+---------+-------+------+----------+

15 rows in set (0.00 sec)

b.进行取出rn = 1 的结果

root@my3308.sock-8.0.11>[employees]>select x.id,x.name,x.subject,x.score

-> from (

-> select t.*,if(@_subject=t.subject ,@rn:=@rn+1,@rn:=1) as rn ,@_subject:=t.subject as _subject

-> from(select t.* from window_test t order by subject,score desc ) t , (select @rn:=0 rn ,@_subject:='') b

-> )x WHERE rn=1 ORDER BY SUBJECT;

+------+--------+---------+-------+

| id | name | subject | score |

+------+--------+---------+-------+

| 5 | 小黄 | 化学 | 99 |

| 1 | 小黄 | 数学 | 100 |

| 4 | 小黄 | 物理 | 100 |

| 13 | 小绿 | 英语 | 93 |

| 7 | 小红 | 语文 | 100 |

+------+--------+---------+-------+

5 rows in set (0.00 sec)

方法四：使用8.0引入的窗口函数row_number over..

root@my3308.sock-8.0.11>[employees]>select id,name,subject,score from (

-> select row_number()over(partition by subject order by score desc) as rn,

-> id,name,subject,score

-> from window_test )t

-> where rn=1;

+------+--------+---------+-------+

| id | name | subject | score |

+------+--------+---------+-------+

| 5 | 小黄 | 化学 | 99 |

| 1 | 小黄 | 数学 | 100 |

| 4 | 小黄 | 物理 | 100 |

| 13 | 小绿 | 英语 | 93 |

| 7 | 小红 | 语文 | 100 |

+------+--------+---------+-------+

5 rows in set (0.00 sec)

是不是很方便。

root@my3308.sock-8.0.11>[employees]>with cte as (

-> select row_number()over(partition by subject order by score desc) as rn,

-> id,name,subject,score

-> from window_test

-> )select id,name,subject,score from cte where rn = 1;

+------+--------+---------+-------+

| id | name | subject | score |

+------+--------+---------+-------+

| 5 | 小黄 | 化学 | 99 |

| 1 | 小黄 | 数学 | 100 |

| 4 | 小黄 | 物理 | 100 |

| 13 | 小绿 | 英语 | 93 |

| 7 | 小红 | 语文 | 100 |

+------+--------+---------+-------+

5 rows in set (0.00 sec)

侍酒师小田
关注
  • 1
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
mysql-connector-java-8.0.25-jar
01-20
该资源为mysql-connector-java-8.0.25-jar 欢迎下载使用哦！
mysql 类似rownum的实现 5.6版本可用 8.0版本自带
qq_40311925的博客
11-17 847
mysql 类似rownum
sql函数--04---ROW_NUMBER() OVER()函数用法详解
时光里的博客
10-26 8078
ROW_NUMBER() OVER()函数 使用范围: 这个函数可以Oracle中使用 MySQL用不了 语法格式： row_number() over(partition by 分组列 order by 排序列 desc) 注意: 在使用 row_number() over()函数时候，over()里头的分组以及排序的执行晚于 where 、group by、 order by 的执行。 案例 1 表数据： create table TEST_ROW_NUMBER_OVER(
MySQL8 ROW_NUMBER（）和OVER
daisy_sura的博客
04-18 282
MySQL8.0以后的功能，可以很方便的将结果按小分组进行排序，
mysql8之前如何实现row_number() over(partition by xxx order by xxx asc/desc)
qq_41018861的博客
05-19 2132
学学其他大佬我们也来总结总结公式要想实现row_number() over(partition by 分组字段 order by 排序字段 desc)set @分组字段 = null;set @顺序字段 = 0;
mysql实现rownum，选择行号
m0_67394002的博客
08-23 540
在oracle中，可使用rownum处理很多与行号相关的问题，但是mysql中没有类似的函数。转至https://blog.csdn.net/muzizhuben/article/details/49449853。用@rowno这自定义变量显示行号。
mysql-connector-java-8.0.31-jar包
01-13
该资源为mysql-connector-java-8.0.31-jar包，欢迎下载哦！
mysql-connector-java-8.0.25.jar
最新发布
12-18
该资源为mysql-connector-java-8.0.25.jar，欢迎下载使用哦！
mysql-connector-java-8.0.34
12-13
mysql-connector-java-8.0.34
mysql-server_8.0.25-1debian10.zip
07-09
MySQL 是世界上最流行的开源数据库。无论您是快速发展的网络资产、技术 ISV 还是大型企业，MySQL 都可以经济高效地帮助您交付高性能、可扩展的数据库应用程序。...mysql-server_8.0.25-1debian10_amd64.deb-bundle.tar
MySQL 实现row_number() over(partition by ) 分组排序功能
u014786530的博客
10-13 1万+
MySQL 实现row_number() over(partition by ) 分组排序功能
MySQL 8.0窗口函数
aecuhty88306453的博客
11-15 1195
团队介绍 网易乐得DBA组，负责网易乐得电商、网易邮箱、网易技术部数据库日常运维，负责数据库私有云平台的开发和维护，负责数据库及数据库中间件Cetus的开发和测试等等。 一、窗口函数的使用场景 作为IT人士，日常工作中经常会遇到类似这样的需求： 医院看病，怎样知道上次就医距现在的时间？环比如何计算？怎么样得到各部门工资排名前N名员工列表?查找各部门每人工资...
Mysql的Rownum使用示例
热门推荐
a80C51的博客
05-31 6万+
1，显示当前查询结果的行号SELECT @rownum := @rownum +1 AS rownum, e.* FROM (SELECT @rownum := 0) r, employee e 显示结果如下：2，按部门分组并按年龄降序排序并显示排名SELECT IF(@dept = e.deptNo,@rank := @rank +1,@rank := 1) A...
Mysql8.0+中的rank()、row_num()、dense_rank()等窗口函数
阿斯达克
04-02 2211
在低于Mysql8.0之前的版本中，要对序列进行排序的话需要用到自定义参数@，但是8.0+版本中加入了oracle中的窗口函数，rank()、row_num()、dense_rank()等函数。 下面简单举几个例子介绍下这几个函数的应用。测试用的表主要有两个，一个是学生信息表student，二是学生分数表sc，具体的测试数据的mysql插入语句在另一篇博客：Mysql经典练习题与知识点总结中，大家...
MySQL5.7 实现类似 MySQL8.0 中 row_number() over(partition by ... order by ...) 函数的分组排序编号效果
Hello World
02-25 4309
按照公司和部门分组、创建时间排序，并对每一组内的行进行编号。使用用户变量分别表示当前行的排序编号、前一个分组的字段值。在select子句中使用IF函数来判断当前行是否与前一行属于同一分组，如果是将当前行的排序编号加 1，否则将排序编号重置为 1。order by子句中包含分组和排序字段。使用用户变量来实现类似 ROW_NUMBER() OVER(PARTITION BY ... ORDER BY ...) 函数的分组排序编号效果，可能会影响查询的性能和稳定性，应该谨慎使用。如果不是迫不得已没办法，我会用它？
【业务功能篇10】Mysql 排名函数：row_number() over（partition by)的用法说明
studyday1的博客
06-01 6219
比如，业务有个产品编码表，其中有父编码列，子编码列，一个父编码会对应多个子编码，比如父编码为 a1, 组成的子编码有多个为 suba1,suba2.. 对应多条数据，而且这里也会存在不同的父编码，其包含相同的子编码，也就是说不同的产品用了相同的子模块，那么我们想看看每个子模块，分别都被多少个父编码使用，并且按相应顺序显示，这么就可以利用排名函数，对子模块进行分区，这样就可以得到，如果一个子编码有对应多个父编码的记录，那么就是会有相应的展示其记录并且有编号按序展示。下面举个例子，比如有学生，课程 ， 分数。
mysql中实现rownum，对结果进行排序
ratel的博客
05-28 5307
其中的一个问题就是但是用rownum函数的时候发现Mysql里面没有，所以只能用曲线救过的方式如下： SELECT @rownum := @rownum + 1 AS rownum, USER.* FROM USER, ( SELECT @rownum := 0 ) r; 由于是通过曲线救过方式实现，所以肯定没有Oracle自身实现的效率高；希望在以后的更新中，Mysql可以自带该函数...
mysql实现row_number() over功能
健康平安的活着的专栏
10-04 2万+
想要获取每组的前n个记录，由于mysql中没有row_number() over 函数，所以自己需要通过使用mysql的语法同样实现该功能。 #建表语句 SET FOREIGN_KEY_CHECKS=0; -- ---------------------------- -- Table structure for score -- ---------------------------- D...
Mysql实现ROW_NUMBER() OVER()
Chao_Qing的专栏
03-30 3万+
** Mysql实现ROW_NUMBER() OVER() ** #1.将要进行分组的列进行order by(这个menu排序必须要这样做) select userid,menu,score from test_domain.wxc order by menu asc ,score desc ; #2.先判断pdept变量是否等于menu，如果相等rank变量相等，否则rank变量为1(所以在这...
mysql中的row_number() over函数用法
05-19
如果MySQL版本高于8.0，那么可以使用窗口函数来实现ROW_NUMBER()的功能： ``` SELECT ROW_NUMBER() OVER (ORDER BY column1) AS row_number, column1, column2, ... FROM table_name; ``` 其中`ROW_NUMBER() OVER`...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值