SQL实战题解_CASE_开窗函数

最差是第几名(一)

http://www.nowcoder.com/questionTerminal/ae5e8273e73b4413823b676081bd355c

理解题意：最差名次，即该等级中的最后一名对应的名次（将小于等于该等级的人数相加作为最差名次）

两种解法，两个知识点：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