Leetcode_資料庫刷題_580. 統計各專業學生人數

580題. 統計各專業學生人數

SQL架構：

CREATE TABLE IF NOT EXISTS student (student_id INT,student_name VARCHAR(45), gender VARCHAR(6), dept_id INT)
CREATE TABLE IF NOT EXISTS department (dept_id INT, dept_name VARCHAR(255))
Truncate table student
insert into student (student_id, student_name, gender, dept_id) values ('1', 'Jack', 'M', '1')
insert into student (student_id, student_name, gender, dept_id) values ('2', 'Jane', 'F', '1')
insert into student (student_id, student_name, gender, dept_id) values ('3', 'Mark', 'M', '2')
Truncate table department
insert into department (dept_id, dept_name) values ('1', 'Engineering')
insert into department (dept_id, dept_name) values ('2', 'Science')
insert into department (dept_id, dept_name) values ('3', 'Law')
           

一所大學有 2 個資料表，分别是 student 和 department ，這兩個表儲存着每個專業的學生資料和院系資料。

問題：寫一個查詢語句，查詢 department 表中每個專業的學生人數 （即使沒有學生的專業也需列出）。

将你的查詢結果按照學生人數降序排列。 如果有兩個或兩個以上專業有相同的學生數目，将這些部門按照部門名字的字典序從小到大排列。

student 表格如下：

Column Name	Type
student_id	Integer
student_name	String
gender	Character
dept_id	Integer

其中， student_id 是學生的學号， student_name 是學生的姓名， gender 是學生的性别， dept_id 是學生所屬專業的專業編号。

department 表格如下：

Column Name	Type
dept_id	Integer
dept_name	String

dept_id 是專業編号， dept_name 是專業名字。

這裡是一個示例輸入：

student 表格：

student_id	student_name	gender	dept_id
1	Jack	M	1
2	Jane	F	1
3	Mark	M	2

department 表格：

dept_id	dept_name
1	Engineering
2	Science
3	Law

示例輸出為：

dept_name	student_number
Engineering	2
Science	1
Law

解答：

select dept_name, count(student_id) as student_number from department d
left join student s
on d.dept_id = s.dept_id
group by d.dept_id
order by student_number desc, d.dept_name
或
select dept_name,(CASE WHEN temp.student_name is null THEN 0 ELSE count(*) END) as student_number 
from(
 select d.dept_name,d.dept_id,s.student_name from department d 
 left join student s 
 on d.dept_id = s.dept_id 
) temp
group by temp.dept_id
order by student_number desc,dept_name 
           

方法一：COUNT(expression) 語句，因為如果 expression is null，那麼這條記錄不會被計數,而COUNT(*) 将會記錄

方法二：CASE WHEN THEN 用法說明

1、簡單Case函數

CASE 字段名
            WHEN  條件1  THEN  結果1
            WHEN  條件2  THEN  結果2
    ELSE  結果3 END
舉例：
    CASE sex
         	WHEN '1' THEN '男'
         	WHEN '2' THEN '女'
    ELSE '其他' END
           

2、Case搜尋函數

CASE WHEN 字段名 =值 THEN 結果
         WHEN 字段名 =值 THEN 結果
    ELSE 結果3 END  
舉例：
    CASE WHEN sex = '1' THEN '男'
         WHEN sex = '2' THEN '女'
    ELSE '其他' END
           

關于NULL需要注意：MySQL 使用三值邏輯 —— TRUE, FALSE 和 UNKNOWN。任何與 NULL 值進行的比較都會與第三種值 UNKNOWN 做比較。這個“任何值”包括 NULL 本身！這就是為什麼 MySQL 提供 IS NULL 和 IS NOT NULL 兩種操作來對 NULL 特殊判斷。

來源：力扣（LeetCode）

連結：https://leetcode-cn.com/problems/count-student-number-in-departments