[SQL] LeetCode题解 #596 超过5名学生的课

对SQL语句不熟悉欢迎查看我整理的笔记：[SQL] MySQL基础 + python交互

转载请注明：陈熹 chenx6542@foxmail.com （简书：半为花间酒）
若公众号内转载请联系公众号：早起Python

题目：

简单题 #596

SQL架构

Create table If Not Exists courses (student varchar(255), class varchar(255));
Truncate table courses;
insert into courses (student, class) values ('A', 'Math');
insert into courses (student, class) values ('B', 'English');
insert into courses (student, class) values ('C', 'Math');
insert into courses (student, class) values ('D', 'Biology');
insert into courses (student, class) values ('E', 'Math');
insert into courses (student, class) values ('F', 'Computer');
insert into courses (student, class) values ('G', 'Math');
insert into courses (student, class) values ('H', 'Math');
insert into courses (student, class) values ('I', 'Math');
insert into courses (student, class) values ('A', 'Math');
insert into courses (student, class) values ('C', 'Math');

题解

第一种解法

—— 基于分组聚合函数

本题简单的思路就是利用class进行分组，计数每个组中的学生个数，超过5个即为符合题意。但需要避开的陷阱是可能有重修或者其他的情况导致同一个学生又修了一次同样的课程，因此本思路的重点是去重的处理

首先写出框架

SELECT class
FROM courses
GROUP BY class
HAVING COUNT(1)>=5;

去重可以利用DISTINCT，放的位置可有以下两种

SELECT class
FROM courses
GROUP BY class
HAVING COUNT(DISTINCT student)>=5;

# 第二种方法在FROM中建立子查询
SELECT class 
FROM (
    SELECT DISTINCT student, class 
    FROM courses
) temp 
GROUP BY temp.class 
HAVING COUNT(1) >= 5;

第二种解法

—— 基于窗口函数DENSE_RANK()

DENSE_RANK()的重要特点是遇到相同记录时排名相同，且下一条记录会接着排名，不会跳过

首先看现在的表内容：

SELECT class,DENSE_RANK() OVER (PARTITION BY class ORDER BY student) rk
FROM courses;

可以看到，这条SQL语句完成了各组内排名，且重复的记录排名相同
因此按照题目，只要组内出现了排名5，就说明这个组至少有5个学生选，但仍需要注意陷阱，注意去重

SELECT DISTINCT class 
FROM (
    SELECT class,DENSE_RANK() OVER (PARTITION BY class ORDER BY student) rk
    FROM courses) temp
WHERE rk = 2;