MySQL实战三 窗口函数实现及查询案例
- 2019 年 10 月 6 日
- 笔记
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)
