理解题意:最差名次,即该等级中的最后一名对应的名次(将小于等于该等级的人数相加 作为最差名次)
两种解法,两个知识点:case函数以及开窗函数(MySQL8.0 引入),其中case命令为面试笔试中常考知识点。
case函数
# 不用MySQL8.0的开窗函数 -- case when then end SELECT grade,(CASE grade WHEN 'A' THEN (SELECT SUM(number) FROM class_grade WHERE grade <='A') -- 如果等级为A,将小于等于A的人数相加 作为最差名次 WHEN 'B' THEN (SELECT SUM(number) FROM class_grade WHERE grade <='B') -- 等级为B的最差名次,等级的''不能忘,不然会有函数转换,报错 WHEN 'C' THEN (SELECT SUM(number) FROM class_grade WHERE grade <='C') -- 等级为C的最差名次 WHEN 'D' THEN (SELECT SUM(number) FROM class_grade WHERE grade <='D') -- 等级为D的最差名次 ELSE (SELECT SUM(number) FROM class_grade ) -- 等级为E的最差名次 END )AS t_rank FROM class_grade ORDER BY grade; -- 按等级排序
开窗函数
# 使用开窗函数 # 首先使用sum(number) --算名次 # over(order by grade)--按各个等级排序,分别计算sum()最差名次 SELECT grade,sum(number) over(order by grade) AS t_rank -- sum() over 开窗函数求各个等级的最差名次 FROM class_grade ORDER BY grade; -- 按等级排序
1.CASE函数
case函数有两种:简单case、搜索case
简单case
#简单case: CASE <表达式> WHEN <值1> THEN <操作> WHEN <值2> THEN <操作> ... ELSE <操作> END
SELECT grade,(CASE grade WHEN 'A' THEN (SELECT SUM(number) FROM class_grade WHERE grade <='A') -- 如果等级为A,将小于等于A的人数相加 作为最差名次 WHEN 'B' THEN (SELECT SUM(number) FROM class_grade WHERE grade <='B') -- 等级为B的最差名次,等级的''不能忘,不然会有函数转换,报错 WHEN 'C' THEN (SELECT SUM(number) FROM class_grade WHERE grade <='C') -- 等级为C的最差名次 WHEN 'D' THEN (SELECT SUM(number) FROM class_grade WHERE grade <='D') -- 等级为D的最差名次 WHEN 'E' THEN (SELECT SUM(number) FROM class_grade ) -- 等级为E的最差名次,即共有多少人(因题意可知最差为E) END )AS t_rank FROM class_grade ORDER BY grade; -- 按等级排序
搜索case
#搜索case CASE WHEN <条件1> THEN <操作> WHEN <条件2> THEN <操作> ... ELSE <操作> END
SELECT grade,(CASE WHEN grade='A' THEN (SELECT SUM(number) FROM class_grade WHERE grade <='A') -- 如果等级为A,将小于等于A的人数相加 作为最差名次 WHEN grade='B' THEN (SELECT SUM(number) FROM class_grade WHERE grade <='B') -- 等级为B的最差名次,等级的''不能忘,不然会有函数转换,报错 WHEN grade='C' THEN (SELECT SUM(number) FROM class_grade WHERE grade <='C') -- 等级为C的最差名次 WHEN grade='D' THEN (SELECT SUM(number) FROM class_grade WHERE grade <='D') -- 等级为D的最差名次 ELSE (SELECT SUM(number) FROM class_grade ) -- 剩下的为E的 END )AS t_rank FROM class_grade ORDER BY grade; -- 按等级排序
简单总结
1.简单case 只能在表达式中做函数操作,在when后做等值比较,依次匹配,等于则执行then后的操作。
如: case sum(number) when 12 then 1 end
2.搜索case 在when 后面可以执行复杂的函数操作和比较,条件成立则执行then后的操作。
如: case when sum(number)>=12 then 1 end
3.case是依顺序判断的,遇到某一项条件成立则执行对应操作
4.如果都不匹配则执行else 后面的操作,可以没有else
5.每条语句后不用加任何符号,最后必须以 end 结尾
2.开窗函数
函数简介
- MySQL从8.0开始支持开窗函数,这个功能在大多商业数据库中早已支持,也叫分析函数;
- 开窗函数与分组聚合比较像,分组聚合是通过制定字段将数据分成多份,每一份执行聚合函数,每份数据返回一条结果;
- 开窗函数也是通过指定字段将数据分成多份,也就是多个窗口,对每个窗口的每一行执行函数,每个窗口返回等行数的结果;
- 窗口函数分为静态窗口和滑动窗口,静态窗口的大小是固定的,滑动窗口的大小可以根据设置进行变化,在当前窗口下生成子窗口。
语法简介
- 语法:函数名([参数]) over(partition by [分组字段] order by [排序字段] asc/desc rows/range between 起始位置 and 结束位置)
函数解读
函数分为两个部分,函数名称 + over()语句
# 函数名称 + over(...) 开窗函数(字段) over(partition by 分组字段 order by 排序字段 range between 起始位置 and 结束位置)
第一部分是函数名称,开窗函数的数量较少,只有11个窗口函数+聚合函数(所有聚合函数都可以用作开窗函数),根据函数性质,有的要写参数,有的不需要写参数;
按照功能划分,可以把MySQL支持的窗口函数分为如下几类:
- 所有聚合函数:如sum()/avg()/count()/max()/min()/...
- 序号函数:row_number() / rank() / dense_rank()
- 分布函数:percent_rank() / cume_dist()
- 前后函数:lag() / lead()
- 头尾函数:first_val() / last_val()
- 其他函数:nth_value() / nfile()
第二部分是over语句,over()是必须要写的,里面有三个参数,都是非必须参数,根据需求选写:
- 第一个参数是 partition by +分组字段,将数据根据此字段分成多份,如果不加partition by参数,那会把整个数据当做一个窗口。
- 第二个参数是 order by +排序字段,每个窗口的数据要不要进行排序。
- 第三个参数 rows/range between 起始位置 and 结束位置,这个参数仅针对滑动窗口函数有用,是在当前窗口下分出更小的子窗口。其中起始位置和结束位置可写:current row 边界是当前行,unbounded preceding 边界是分区中的第一行,unbounded following 边界是分区中的最后一行,expr preceding 边界是当前行减去expr的值,expr following 边界是当前行加上expr的值。rows是基于行数,range是基于值的大小,到讲解到滑动窗口函数时再详细介绍。
更多操作详情博客:Mysql 开窗函数实战
https://www.cnblogs.com/thxj/p/12727589.html
https://www.cnblogs.com/DataArt/p/9961676.html