理解题意:最差名次,即该等级中的最后一名对应的名次(将小于等于该等级的人数相加 作为最差名次)

两种解法,两个知识点: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()是必须要写的,里面有三个参数,都是非必须参数,根据需求选写:

  1. 第一个参数是 partition by +分组字段,将数据根据此字段分成多份,如果不加partition by参数,那会把整个数据当做一个窗口。
  2. 第二个参数是 order by +排序字段,每个窗口的数据要不要进行排序。
  3. 第三个参数 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