[Leetcode][SQL] 596. Classes More Than 5 Students

題目

Classes More Than 5 Students?

leetcode.com

數據:

+---------+------------+| student | class |+---------+------------+| A | Math || B | English || C | MatH || D | Biology || E | Math || F | Computer || G | Math || H | Math || I | Math |+---------+------------+

通過跑什麼指令能都達到一下結果?

+---------+| class |+---------+| Math |+---------+

思路

簡單理解

Classes More Than 5 Students?

leetcode.com

簡單理解

SELECT classFROM ( SELECT class, student, 1 AS number_Of_student FROM courses) AS temp1GROUP BY classHAVING SUM(number_Of_student) >= 5

但是這個看起來太多了. 不過沒關係, 有了思路之後呢, 我們就可以再優化.

精簡版本如下:

SELECT classFROM coursesGROUP BY classHAVING COUNT(DISTINCT student) >= 5;

學習高級語法 HAVING COUNT()

參考

• SQLite HAVING Clause with Practical Examples
• Classes More Than 5 Students

推薦閱讀：