MySQL实战三 窗口函数实现及查询案例
MySQL实战三 窗口函数实现及查询案例
公众号guangcity
发布于 2019-09-20 17:30:19
发布于 2019-09-20 17:30:19
MySQL学习仓库Up-Up-MySQL，这是一个学习MySQL从入门实战到理论完善，再到精通的一个仓库，后面会把MySQL的学习资料上传上去！欢迎大家star与fork起来！
仓库地址：
https://github.com/Light-City/Up-Up-MySQL
也可以点击阅读原文！
今天上手第三弹，窗口函数实现及查询案例。
窗口分析函数在做数据分析时十分常用，但是mysql5.7却不支持。
下面一起来实现一下：
1.生成组内连续但不唯一的数字。类似DENSE_RANK()函数。
按各科成绩进行排序，并显示排名， Score 重复时合并名次空缺
set @cid=0;
set @rank=0;
set @score=0;
select @rank:=IF(@cid=sc.CId,IF(@score=sc.score,@rank,@rank+1),1) '排名',@cid:=sc.CId '课程编号' ,sc.SId,@score:=sc.score score from SC sc order by cid,score desc;
+--------+--------------+-----+-------+
| 排名 | 课程编号 | SId | score |
+--------+--------------+-----+-------+
| 1 | 01 | 01 | 80.0 |
| 1 | 01 | 03 | 80.0 |
| 2 | 01 | 05 | 76.0 |
| 3 | 01 | 02 | 70.0 |
| 4 | 01 | 04 | 50.0 |
| 5 | 01 | 06 | 31.0 |
| 1 | 02 | 01 | 90.0 |
| 2 | 02 | 07 | 89.0 |
| 3 | 02 | 05 | 87.0 |
| 4 | 02 | 03 | 80.0 |
| 5 | 02 | 02 | 60.0 |
| 6 | 02 | 04 | 30.0 |
| 1 | 03 | 01 | 99.0 |
| 2 | 03 | 07 | 98.0 |
| 3 | 03 | 03 | 80.0 |
| 3 | 03 | 02 | 80.0 |
| 4 | 03 | 06 | 34.0 |
| 5 | 03 | 04 | 20.0 |
+--------+--------------+-----+-------+
18 rows in set (0.00 sec)
查询学生的总成绩，并进行排名，总分重复时不保留名次空缺
set @total=0;
select @total:=IF(@total=s.total,@total,@total+1) '排名',s.SId,s.total from (select sc.SId,sum(sc.score) total from SC sc group by sc.SId) as s order by total desc;
+--------+-----+-------+
| 排名 | SId | total |
+--------+-----+-------+
| 1 | 01 | 269.0 |
| 2 | 03 | 240.0 |
| 3 | 02 | 210.0 |
| 4 | 07 | 187.0 |
| 5 | 05 | 163.0 |
| 6 | 04 | 100.0 |
| 7 | 06 | 65.0 |
+--------+-----+-------+
7 rows in set (0.00 sec)
2.生成组内连续且唯一的数字。类似ROW_NUMBER()函数
首先初始化变量
set @cid=0;
set @rank=0;
使用IF
select @rank:=IF(@cid=sc.CId,@rank+1,1) '排名',@cid:=sc.CId '课程编号' ,sc.SId,sc.score from SC sc order by cid,sc.score desc;
使用case when end
select @rank:=(case @cid when sc.CId THEN @rank+1 else 1 end) '排名',@cid:=sc.CId '课程编号' ,sc.SId,sc.score from SC sc order by cid,sc.score desc;
查询结果：
+--------+--------------+-----+-------+
| 排名 | 课程编号 | SId | score |
+--------+--------------+-----+-------+
| 1 | 01 | 01 | 80.0 |
| 2 | 01 | 03 | 80.0 |
| 3 | 01 | 05 | 76.0 |
| 4 | 01 | 02 | 70.0 |
| 5 | 01 | 04 | 50.0 |
| 6 | 01 | 06 | 31.0 |
| 1 | 02 | 01 | 90.0 |
| 2 | 02 | 07 | 89.0 |
| 3 | 02 | 05 | 87.0 |
| 4 | 02 | 03 | 80.0 |
| 5 | 02 | 02 | 60.0 |
| 6 | 02 | 04 | 30.0 |
| 1 | 03 | 01 | 99.0 |
| 2 | 03 | 07 | 98.0 |
| 3 | 03 | 03 | 80.0 |
| 4 | 03 | 02 | 80.0 |
| 5 | 03 | 06 | 34.0 |
| 6 | 03 | 04 | 20.0 |
+--------+--------------+-----+-------+
18 rows in set (0.00 sec)
注意：select中排名必须在课程编号前面，否则结果不正确！
3.生成组内既不连续也不唯一的数字。类似RANK()函数。
按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺
select @enum:=IF(@cid=sc.CId,@enum+1,1) enum,@rank:=IF(@cid=sc.CId,IF(@score=sc.score,@rank,@enum),1) '排名',@cid:=sc.CId '课程编号' ,sc.SId,@score:=sc.score score from SC sc order by cid,score desc;
查询结果：
mysql> select @enum:=IF(@cid=sc.CId,@enum+1,1) enum,@rank:=IF(@cid=sc.CId,IF(@score=sc.score,@rank,@enum),1) '排名',@cid:=sc.CId '课程编号' ,sc.SId,@score:=sc.score score from SC sc order by cid,score desc;
+------+--------+--------------+-----+-------+
| enum | 排名 | 课程编号 | SId | score |
+------+--------+--------------+-----+-------+
| 1 | 1 | 01 | 01 | 80.0 |
| 2 | 1 | 01 | 03 | 80.0 |
| 3 | 3 | 01 | 05 | 76.0 |
| 4 | 4 | 01 | 02 | 70.0 |
| 5 | 5 | 01 | 04 | 50.0 |
| 6 | 6 | 01 | 06 | 31.0 |
| 1 | 1 | 02 | 01 | 90.0 |
| 2 | 2 | 02 | 07 | 89.0 |
| 3 | 3 | 02 | 05 | 87.0 |
| 4 | 4 | 02 | 03 | 80.0 |
| 5 | 5 | 02 | 02 | 60.0 |
| 6 | 6 | 02 | 04 | 30.0 |
| 1 | 1 | 03 | 01 | 99.0 |
| 2 | 2 | 03 | 07 | 98.0 |
| 3 | 3 | 03 | 03 | 80.0 |
| 4 | 3 | 03 | 02 | 80.0 |
| 5 | 5 | 03 | 06 | 34.0 |
| 6 | 6 | 03 | 04 | 20.0 |
+------+--------+--------------+-----+-------+
18 rows in set (0.00 sec)
使用自连接：
mysql> select sc1.CId,sc1.SId,sc1.score,count(sc2.score)+1 rank from SC sc1 left join SC sc2 on sc1.CId=sc2.CId and sc1.score<sc2.score group by sc1.CId,sc1.SId,sc1.score order by sc1.CId,rank asc;
+-----+-----+-------+------+
| CId | SId | score | rank |
+-----+-----+-------+------+
| 01 | 01 | 80.0 | 1 |
| 01 | 03 | 80.0 | 1 |
| 01 | 05 | 76.0 | 3 |
| 01 | 02 | 70.0 | 4 |
| 01 | 04 | 50.0 | 5 |
| 01 | 06 | 31.0 | 6 |
| 02 | 01 | 90.0 | 1 |
| 02 | 07 | 89.0 | 2 |
| 02 | 05 | 87.0 | 3 |
| 02 | 03 | 80.0 | 4 |
| 02 | 02 | 60.0 | 5 |
| 02 | 04 | 30.0 | 6 |
| 03 | 01 | 99.0 | 1 |
| 03 | 07 | 98.0 | 2 |
| 03 | 02 | 80.0 | 3 |
| 03 | 03 | 80.0 | 3 |
| 03 | 06 | 34.0 | 5 |
| 03 | 04 | 20.0 | 6 |
+-----+-----+-------+------+
18 rows in set (0.00 sec)
不按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺
select sc1.CId,sc1.SId,sc1.score,(select count(1) from SC sc2 where sc2.score>sc1.score)+1 as rank from SC sc1 order by rank;
+-----+-----+-------+------+
| CId | SId | score | rank |
+-----+-----+-------+------+
| 03 | 01 | 99.0 | 1 |
| 03 | 07 | 98.0 | 2 |
| 02 | 01 | 90.0 | 3 |
| 02 | 07 | 89.0 | 4 |
| 02 | 05 | 87.0 | 5 |
| 01 | 01 | 80.0 | 6 |
| 02 | 03 | 80.0 | 6 |
| 03 | 03 | 80.0 | 6 |
| 03 | 02 | 80.0 | 6 |
| 01 | 03 | 80.0 | 6 |
| 01 | 05 | 76.0 | 11 |
| 01 | 02 | 70.0 | 12 |
| 02 | 02 | 60.0 | 13 |
| 01 | 04 | 50.0 | 14 |
| 03 | 06 | 34.0 | 15 |
| 01 | 06 | 31.0 | 16 |
| 02 | 04 | 30.0 | 17 |
| 03 | 04 | 20.0 | 18 |
+-----+-----+-------+------+
18 rows in set (0.00 sec)
本文参与 腾讯云自媒体分享计划，分享自微信公众号。
原始发表：2019-06-24，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
目录
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server （TencentDB for SQL Server）是业界最常用的商用数据库之一，对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权，可持续为用户提供最新的功能，避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。