MySQL題集

題一（組合兩個表）

表1: Person

+-------------+---------+
| 列名         | 型別     |
+-------------+---------+
| PersonId    | int     |
| FirstName   | varchar |
| LastName    | varchar |
+-------------+---------+
PersonId 是上表主鍵

> 來源：力扣（LeetCode） 鏈接：https://leetcode-cn.com/problems/combine-two-tables
> 著作權歸領扣網路所有，商業轉載請聯系官方授權，非商業轉載請注明出處，

表2: Address

+-------------+---------+
| 列名         | 型別    |
+-------------+---------+
| AddressId   | int     |
| PersonId    | int     |
| City        | varchar |
| State       | varchar |
+-------------+---------+
AddressId 是上表主鍵

> 來源：力扣（LeetCode） 鏈接：https://leetcode-cn.com/problems/combine-two-tables
> 著作權歸領扣網路所有，商業轉載請聯系官方授權，非商業轉載請注明出處，

撰寫一個 SQL 查詢，滿足條件：無論 person 是否有地址資訊，都需要基于上述兩表提供 person 的以下資訊：

FirstName, LastName, City, State

題解

我沒題解，我菜，

方法：使用 outer join

因為表 Address 中的 personId 是表 Person 的外關鍵字，所以我們可以連接這兩個表來獲取一個人的地址資訊，
考慮到可能不是每個人都有地址資訊，我們應該使用 outer join 而不是默認的 inner join，

select FirstName, LastName, City, State
from Person left join Address
on Person.PersonId = Address.PersonId
;
> 作者：LeetCode
> 鏈接：https://leetcode-cn.com/problems/combine-two-tables/solution/zu-he-liang-ge-biao-by-leetcode/
> 來源：力扣（LeetCode） 著作權歸作者所有，商業轉載請聯系作者獲得授權，非商業轉載請注明出處，

注意：如果沒有某個人的地址資訊，使用 where 子句過濾記錄將失敗，因為它不會顯示姓名資訊，

識訓

題解沒有，要是連識訓都沒有那不是白忙活一場，

多表聯結

涉及到多表查詢，需要用到聯結，

多表的聯結又分為以下幾種型別：

1）左聯結（left join），聯結結果保留左表的全部資料

2）右聯結（right join），聯結結果保留右表的全部資料

3）行內結（inner join），取兩表的公共資料

底下那倆，各位自行實作，反正我是實作了，

題二：第二高的薪水

撰寫一個 SQL 查詢，獲取 Employee 表中第二高的薪水（Salary） ，

+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+

例如上述 Employee 表，SQL查詢應該回傳 200 作為第二高的薪水，如果不存在第二高的薪水，那么查詢應回傳 null，

+---------------------+
| SecondHighestSalary |
+---------------------+
| 200                 |
+---------------------+

> 來源：力扣（LeetCode）
> 鏈接：https://leetcode-cn.com/problems/second-highest-salary
> 著作權歸領扣網路所有，商業轉載請聯系官方授權，非商業轉載請注明出處，

題解

方法：使用 IFNULL 和 LIMIT 子句

解決 “NULL” 問題的另一種方法是使用 “IFNULL” 函式，如下所示，

SELECT
    IFNULL(
      (SELECT DISTINCT Salary
       FROM Employee
       ORDER BY Salary DESC
        LIMIT 1 OFFSET 1),
    NULL) AS SecondHighestSalary

> 作者：LeetCode
> 鏈接：https://leetcode-cn.com/problems/second-highest-salary/solution/di-er-gao-de-xin-shui-by-leetcode/
> 來源：力扣（LeetCode） 著作權歸作者所有，商業轉載請聯系作者獲得授權，非商業轉載請注明出處，

識訓

IFNULL(expression, alt_value)
如果第一個引數的運算式 expression 為 NULL，則回傳第二個引數的備用值(此題中是回傳null)，
expression是table的時候要加括號

distinct：
去重一樣的Salary

limit：限回傳的個數
offset：跳過幾個
limit 1 offset 1:回傳一個結果，跳過一個
例如回傳第三高就是：limit 1 offset 2

---

題三：第N高的薪水

可以說是對上一題的鞏固拓展吧，

撰寫一個 SQL 查詢，獲取 Employee 表中第 n 高的薪水（Salary），

+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+

例如上述 Employee 表，n = 2 時，應回傳第二高的薪水 200，如果不存在第 n 高的薪水，那么查詢應回傳 null，

+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| 200                    |
+------------------------+

來源：力扣（LeetCode） 鏈接：https://leetcode-cn.com/problems/nth-highest-salary
著作權歸領扣網路所有，商業轉載請聯系官方授權，非商業轉載請注明出處，

題解

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
    SET n = N-1;
  RETURN (
      # Write your MySQL query statement below.
      select IFNULL(
        (
            select DISTINCT Salary 
            from Employee order by Salary DESC
            limit 1 offset n
        ),
        NULL
      )
  );
END

識訓

怎么說呢，昨天刷了一道這樣的題，沒記太清楚，今天再做一題，把格式記住了，
leetcode兩題選手 - MySQL類題目（一）

這里不多做贅述，

題四：分數排名

撰寫一個 SQL 查詢來實作分數排名，

如果兩個分數相同，則兩個分數排名（Rank）相同，請注意，平分后的下一個名次應該是下一個連續的整數值，換句話說，名次之間不應該有“間隔”，

+----+-------+
| Id | Score |
+----+-------+
| 1  | 3.50  |
| 2  | 3.65  |
| 3  | 4.00  |
| 4  | 3.85  |
| 5  | 4.00  |
| 6  | 3.65  |
+----+-------+

例如，根據上述給定的 Scores 表，你的查詢應該回傳（按分數從高到低排列）：

+-------+------+
| Score | Rank |
+-------+------+
| 4.00  | 1    |
| 4.00  | 1    |
| 3.85  | 2    |
| 3.65  | 3    |
| 3.65  | 3    |
| 3.50  | 4    |
+-------+------+

重要提示：對于 MySQL 解決方案，如果要轉義用作列名的保留字，可以在關鍵字之前和之后使用撇號，例如 Rank

來源：力扣（LeetCode） 鏈接：https://leetcode-cn.com/problems/rank-scores
著作權歸領扣網路所有，商業轉載請聯系官方授權，非商業轉載請注明出處，

題解

select score, 
       dense_rank() over(order by Score desc) as Ranking
from Scores;

我就想說這不是為難我這個新手嗎？這是要保留兩位數？這也沒說啊，，，

解題思路

排名是資料庫中的一個經典題目，實際上又根據排名的具體細節可分為3種場景：

連續排名，例如薪水3000、2000、2000、1000排名結果為1-2-3-4，體現同薪不同名，排名類似于編號
同薪同名但總排名不連續，例如同樣的薪水分布，排名結果為1-2-2-4
同薪同名且總排名連續，同樣的薪水排名結果為1-2-2-3
不同的應用場景可能需要不同的排名結果，也意味著不同的查詢策略，

值得一提的是：在Oracle等資料庫中有視窗函式，可非常容易實作這些需求，而MySQL直到8.0版本也引入相關函式，

row_number(): 同薪不同名，相當于行號，例如3000、2000、2000、1000排名后為1、2、3、4
rank(): 同薪同名，有跳級，例如3000、2000、2000、1000排名后為1、2、2、4
dense_rank(): 同薪同名，無跳級，例如3000、2000、2000、1000排名后為1、2、2、3
ntile(): 分桶排名，即首先按桶的個數分出第一二三桶，然后各桶內從1排名，實際不是很常用

顯然，本題是要用第三個函式，
另外這三個函式必須要要與其搭檔over()配套使用，over()中的引數常見的有兩個，分別是

partition by，按某欄位切分
order by，與常規order by用法一致，也區分ASC(默認)和DESC，因為排名總得有個依據

識訓

涉及到排名的問題，都可以使用視窗函式來解決，記住rank, dense_rank, row_number排名的區別，
MySQL視窗函式

---

題五：連續出現的數字

撰寫一個 SQL 查詢，查找所有至少連續出現三次的數字，

+----+-----+
| Id | Num |
+----+-----+
| 1  |  1  |
| 2  |  1  |
| 3  |  1  |
| 4  |  2  |
| 5  |  1  |
| 6  |  2  |
| 7  |  2  |
+----+-----+

例如，給定上面的 Logs 表， 1 是唯一連續出現至少三次的數字，

+-----------------+
| ConsecutiveNums |
+-----------------+
| 1               |
+-----------------+

來源：力扣（LeetCode）
鏈接：https://leetcode-cn.com/problems/consecutive-numbers
著作權歸領扣網路所有，商業轉載請聯系官方授權，非商業轉載請注明出處，

題解

這題一看我就沒有題解了，，，

SELECT DISTINCT
    l1.Num AS ConsecutiveNums
FROM
    Logs l1,
    Logs l2,
    Logs l3
WHERE
    l1.Id = l2.Id - 1
    AND l2.Id = l3.Id - 1
    AND l1.Num = l2.Num
    AND l2.Num = l3.Num
;

題六：超過經理收入的員工

Employee 表包含所有員工，他們的經理也屬于員工，每個員工都有一個 Id，此外還有一列對應員工的經理的 Id，

+----+-------+--------+-----------+
| Id | Name  | Salary | ManagerId |
+----+-------+--------+-----------+
| 1  | Joe   | 70000  | 3         |
| 2  | Henry | 80000  | 4         |
| 3  | Sam   | 60000  | NULL      |
| 4  | Max   | 90000  | NULL      |
+----+-------+--------+-----------+

給定 Employee 表，撰寫一個 SQL 查詢，該查詢可以獲取收入超過他們經理的員工的姓名，在上面的表格中，Joe 是唯一一個收入超過他的經理的員工，

+----------+
| Employee |
+----------+
| Joe      |
+----------+

來源：力扣（LeetCode）
鏈接：https://leetcode-cn.com/problems/employees-earning-more-than-their-managers
著作權歸領扣網路所有，商業轉載請聯系官方授權，非商業轉載請注明出處，

題解

這題就友好多了，這題我會寫，

就分兩張表，然后比對嘛，不過格式上還有那么點生疏，

select a.Name AS 'Employee'
FROM
    Employee AS a,
    Employee AS b
WHERE 
    a.ManagerId = b.Id
        and a.Salary>b.Salary
;

識訓

聽說這個叫自連接：

運用到自連接
1、先把這張表看成員工表 e
2、再把這張表看成管理者表 m
3、用e表的ID去連接m表的ID，關聯兩張表
4、設定where條件即可

select e.Name as Employee
from Employee e 
join Employee m on e.managerid=m.id
where e.salary>m.salary;

> 作者：xiao-bai-bai-o
> 鏈接：https://leetcode-cn.com/problems/employees-earning-more-than-their-managers/solution/zi-lian-jie-by-xiao-bai-bai-o/
> 來源：力扣（LeetCode） 著作權歸作者所有，商業轉載請聯系作者獲得授權，非商業轉載請注明出處，

題七：從不訂購的客戶

某網站包含兩個表，Customers 表和 Orders 表，撰寫一個 SQL 查詢，找出所有從不訂購任何東西的客戶，

Customers 表：

+----+-------+
| Id | Name  |
+----+-------+
| 1  | Joe   |
| 2  | Henry |
| 3  | Sam   |
| 4  | Max   |
+----+-------+

Orders 表：

+----+------------+
| Id | CustomerId |
+----+------------+
| 1  | 3          |
| 2  | 1          |
+----+------------+

例如給定上述表格，你的查詢應回傳：

+-----------+
| Customers |
+-----------+
| Henry     |
| Max       |
+-----------+

來源：力扣（LeetCode）
鏈接：https://leetcode-cn.com/problems/customers-who-never-order
著作權歸領扣網路所有，商業轉載請聯系官方授權，非商業轉載請注明出處，

題解

要不怎么說簡單題做的有感覺呢，這兩題我還是有思路的，

方法：使用子查詢和 NOT IN 子句

如果我們有一份曾經訂購過的客戶名單，就很容易知道誰從未訂購過，

我們可以使用下面的代碼來獲得這樣的串列，

select customerid from orders;

然后，我們可以使用 NOT IN 查詢不在此串列中的客戶，

select Name as 'Customers'
from Customers
where Customers.Id not in(
    select CustomerId from orders
)
;

識訓

還有的題解是使用左連接的：也挺好
將兩張表join一下
找到join后顧客沒有購物的

select A.Name as Customers
from Customers A left join Orders B
on A.Id = B.CustomerId
where B.Id is null

題八：查找重復的電子郵箱

撰寫一個 SQL 查詢，查找 Person 表中所有重復的電子郵箱，

示例：

+----+---------+
| Id | Email   |
+----+---------+
| 1  | a@b.com |
| 2  | c@d.com |
| 3  | a@b.com |
+----+---------+

根據以上輸入，你的查詢應回傳以下結果：

+---------+
| Email   |
+---------+
| a@b.com |
+---------+

說明：所有電子郵箱都是小寫字母，

來源：力扣（LeetCode） 鏈接：https://leetcode-cn.com/problems/duplicate-emails
著作權歸領扣網路所有，商業轉載請聯系官方授權，非商業轉載請注明出處，

題解：

select Email 
from Person
group by Email 
having count(Email)>1
;

識訓

方法：使用 GROUP BY 和 HAVING 條件

向 GROUP BY 添加條件的一種更常用的方法是使用 HAVING 子句，該子句更為簡單高效，

---

題九：各部門工資最高的員工

Employee 表包含所有員工資訊，每個員工有其對應的 Id, salary 和 department Id，

+----+-------+--------+--------------+
| Id | Name  | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 70000  | 1            |
| 2  | Henry | 80000  | 2            |
| 3  | Sam   | 60000  | 2            |
| 4  | Max   | 90000  | 1            |
+----+-------+--------+--------------+

Department 表包含公司所有部門的資訊，

+----+----------+
| Id | Name     |
+----+----------+
| 1  | IT       |
| 2  | Sales    |
+----+----------+

撰寫一個 SQL 查詢，找出每個部門工資最高的員工，例如，根據上述給定的表格，Max 在 IT 部門有最高工資，Henry 在 Sales 部門有最高工資，

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| Sales      | Henry    | 80000  |
+------------+----------+--------+

來源：力扣（LeetCode）
鏈接：https://leetcode-cn.com/problems/department-highest-salary
著作權歸領扣網路所有，商業轉載請聯系官方授權，非商業轉載請注明出處，

題解

思路是相同的，奈何寫出來一直編譯不過，，，
真讓人無話可說，，，

SELECT
    Department.name AS 'Department',
    Employee.name AS 'Employee',
    Salary
FROM
    Employee
        JOIN
    Department ON Employee.DepartmentId = Department.Id
WHERE
    (Employee.DepartmentId , Salary) IN
    (   SELECT
            DepartmentId, MAX(Salary)
        FROM
            Employee
        GROUP BY DepartmentId
	)
;

識訓

解題方法

方法：使用 JOIN 和 IN 陳述句
因為 Employee 表包含 Salary 和 DepartmentId 欄位，我們可以以此在部門內查詢最高工資，

SELECT
    DepartmentId, MAX(Salary)
FROM
    Employee
GROUP BY DepartmentId;

注意：有可能有多個員工同時擁有最高工資，所以最好在這個查詢中不包含雇員名字的資訊，

| DepartmentId | MAX(Salary) |
|--------------|-------------|
| 1            | 90000       |
| 2            | 80000       |

然后，我們可以把表 Employee 和 Department 連接，再在這張臨時表里用 IN 陳述句查詢部門名字和工資的關系，

作者：LeetCode
鏈接：https://leetcode-cn.com/problems/department-highest-salary/solution/bu-men-gong-zi-zui-gao-de-yuan-gong-by-leetcode/
來源：力扣（LeetCode） 著作權歸作者所有，商業轉載請聯系作者獲得授權，非商業轉載請注明出處，

題十：各部門工資前三高的員工

Employee 表包含所有員工資訊，每個員工有其對應的工號 Id，姓名 Name，工資 Salary 和部門編號 DepartmentId ，

+----+-------+--------+--------------+
| Id | Name  | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 85000  | 1            |
| 2  | Henry | 80000  | 2            |
| 3  | Sam   | 60000  | 2            |
| 4  | Max   | 90000  | 1            |
| 5  | Janet | 69000  | 1            |
| 6  | Randy | 85000  | 1            |
| 7  | Will  | 70000  | 1            |
+----+-------+--------+--------------+

Department 表包含公司所有部門的資訊，

+----+----------+
| Id | Name     |
+----+----------+
| 1  | IT       |
| 2  | Sales    |
+----+----------+

撰寫一個 SQL 查詢，找出每個部門獲得前三高工資的所有員工，例如，根據上述給定的表，查詢結果應回傳：

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| IT         | Randy    | 85000  |
| IT         | Joe      | 85000  |
| IT         | Will     | 70000  |
| Sales      | Henry    | 80000  |
| Sales      | Sam      | 60000  |
+------------+----------+--------+

解釋：

IT 部門中，Max 獲得了最高的工資，Randy 和 Joe 都拿到了第二高的工資，Will 的工資排第三，銷售部門（Sales）只有兩名員工，Henry 的工資最高，Sam 的工資排第二，

來源：力扣（LeetCode）
鏈接：https://leetcode-cn.com/problems/department-top-three-salaries
著作權歸領扣網路所有，商業轉載請聯系官方授權，非商業轉載請注明出處，

題解

解題方法：
使用 JOIN 和子查詢
公司里前 3 高的薪水意味著有不超過 3 個工資比這些值大，

select e1.Name as 'Employee', e1.Salary
from Employee e1
where 3 >
(
    select count(distinct e2.Salary)
    from Employee e2
    where e2.Salary > e1.Salary
)
;

在這個代碼里，我們統計了有多少人的工資比 e1.Salary 高，所以樣例的輸出應該如下所示，

| Employee | Salary |
|----------|--------|
| Henry    | 80000  |
| Max      | 90000  |
| Randy    | 85000  |

然后，我們需要把表 Employee 和表 Department 連接來獲得部門資訊，

SELECT
    d.Name AS 'Department', e1.Name AS 'Employee', e1.Salary
FROM
    Employee e1
        JOIN
    Department d ON e1.DepartmentId = d.Id
WHERE
    3 > (SELECT
            COUNT(DISTINCT e2.Salary)
        FROM
            Employee e2
        WHERE
            e2.Salary > e1.Salary
                AND e1.DepartmentId = e2.DepartmentId
        )
;

識訓

我覺得我得去系統的學一下SQL陳述句了，，，
其實我的想法是這樣的：dense_rank()
奈何我寫出來的一直編譯不過啊，我也很苦惱啊，，，

---

題十一：洗掉重復的電子郵箱

撰寫一個 SQL 查詢，來洗掉 Person 表中所有重復的電子郵箱，重復的郵箱里只保留 Id 最小 的那個，

+----+------------------+
| Id | Email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
| 3  | john@example.com |
+----+------------------+

Id 是這個表的主鍵，
例如，在運行你的查詢陳述句之后，上面的 Person 表應回傳以下幾行:

+----+------------------+
| Id | Email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
+----+------------------+
 

提示：

執行 SQL 之后，輸出是整個 Person 表，
使用 delete 陳述句，

來源：力扣（LeetCode）
鏈接：https://leetcode-cn.com/problems/delete-duplicate-emails
著作權歸領扣網路所有，商業轉載請聯系官方授權，非商業轉載請注明出處，

題解

delete p1 from
Person p1,Person p2
where(
    p1.Email = p2.Email 
    and
    p1.Id>p2.Id
);

終于有能自己寫的題目了，

識訓

使用 DELETE 和 WHERE 子句

我們可以使用以下代碼，將此表與它自身在電子郵箱列中連接起來，
然后我們需要找到其他記錄中具有相同電子郵件地址的更大 ID，所以我們可以像這樣給 WHERE 子句添加一個新的條件，
因為我們已經得到了要洗掉的記錄，所以我們最終可以將該陳述句更改為 DELETE，

其實重要的是，我學會了這種寫法：

delete p1 from
Person p1,Person p2

在實際生產中，面對千萬上億級別的資料，連接的效率往往最高，因為用到索引的概率較高，

題十二：上升的溫度

給定一個 Weather 表，撰寫一個 SQL 查詢，來查找與之前（昨天的）日期相比溫度更高的所有日期的 Id，

+---------+------------------+------------------+
| Id(INT) | RecordDate(DATE) | Temperature(INT) |
+---------+------------------+------------------+
|       1 |       2015-01-01 |               10 |
|       2 |       2015-01-02 |               25 |
|       3 |       2015-01-03 |               20 |
|       4 |       2015-01-04 |               30 |
+---------+------------------+------------------+

例如，根據上述給定的 Weather 表格，回傳如下 Id:

+----+
| Id |
+----+
|  2 |
|  4 |
+----+

來源：力扣（LeetCode） 鏈接：https://leetcode-cn.com/problems/rising-temperature
著作權歸領扣網路所有，商業轉載請聯系官方授權，非商業轉載請注明出處，

題解

SELECT w2.Id
FROM Weather w1, Weather w2
WHERE DATEDIFF(w2.RecordDate, w1.RecordDate) = 1
AND w1.Temperature < w2.Temperature

識訓

其實思路很明確，就是輸在了那個日期判斷的函式上，我的函式是這樣的：

-- select W1.Id from
-- Weather W1,Weather W2
-- where(
--     W1.RecordDate = W2.RecordDate+1
--     and
--     W1.Temperature > W2.Temperature
-- );

---

題十三：最大的國家

這里有張 World 表

+-----------------+------------+------------+--------------+---------------+
| name            | continent  | area       | population   | gdp           |
+-----------------+------------+------------+--------------+---------------+
| Afghanistan     | Asia       | 652230     | 25500100     | 20343000      |
| Albania         | Europe     | 28748      | 2831741      | 12960000      |
| Algeria         | Africa     | 2381741    | 37100000     | 188681000     |
| Andorra         | Europe     | 468        | 78115        | 3712000       |
| Angola          | Africa     | 1246700    | 20609294     | 100990000     |
+-----------------+------------+------------+--------------+---------------+

如果一個國家的面積超過300萬平方公里，或者人口超過2500萬，那么這個國家就是大國家，

撰寫一個SQL查詢，輸出表中所有大國家的名稱、人口和面積，

例如，根據上表，我們應該輸出:

+--------------+-------------+--------------+
| name         | population  | area         |
+--------------+-------------+--------------+
| Afghanistan  | 25500100    | 652230       |
| Algeria      | 37100000    | 2381741      |
+--------------+-------------+--------------+

來源：力扣（LeetCode） 鏈接：https://leetcode-cn.com/problems/big-countries
著作權歸領扣網路所有，商業轉載請聯系官方授權，非商業轉載請注明出處，

題解

這題其實很簡單的，

select name,population,area
from World
where population>25000000 or area>3000000;

題十四：超過五名學生的課

有一個courses 表 ，有: student (學生) 和 class (課程)，

請列出所有超過或等于5名學生的課，

例如,表:

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

應該輸出:

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

Note:
學生在每個課中不應被重復計算，

來源：力扣（LeetCode）
鏈接：https://leetcode-cn.com/problems/classes-more-than-5-students
著作權歸領扣網路所有，商業轉載請聯系官方授權，非商業轉載請注明出處，

題解

本來也是個簡單題，但是吧，遇到了有的學生一個課選幾次的情況，

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

識訓

方法：使用 GROUP BY 子句和子查詢
先統計每門課程的學生數量，再從中選擇超過 5 名學生的課程，
使用 GROUP BY 和 COUNT 獲得每門課程的學生數量，

SELECT
    class, COUNT(DISTINCT student)
FROM
    courses
GROUP BY class
;

注：使用 DISTINCT 防止在同一門課中學生被重復計算，

| class    | COUNT(student) |
|----------|----------------|
| Biology  | 1              |
| Computer | 1              |
| English  | 1              |
| Math     | 6              |

使用上面查詢結果的臨時表進行子查詢，篩選學生數量超過 5 的課程，

SELECT
    class
FROM
    (SELECT
        class, COUNT(DISTINCT student) AS num
    FROM
        courses
    GROUP BY class) AS temp_table
WHERE
    num >= 5
;

> 作者：LeetCode
> 鏈接：https://leetcode-cn.com/problems/classes-more-than-5-students/solution/chao-guo-5ming-xue-sheng-de-ke-by-leetcode/
> 來源：力扣（LeetCode） 著作權歸作者所有，商業轉載請聯系作者獲得授權，非商業轉載請注明出處，

---

題十五：有趣的電影

某城市開了一家新的電影院，吸引了很多人過來看電影，該電影院特別注意用戶體驗，專門有個 LED顯示板做電影推薦，上面公布著影評和相關電影描述，

作為該電影院的資訊部主管，您需要撰寫一個 SQL查詢，找出所有影片描述為非 boring (不無聊) 的并且 id 為奇數 的影片，結果請按等級 rating 排列，

例如，下表 cinema:

+---------+-----------+--------------+-----------+
|   id    | movie     |  description |  rating   |
+---------+-----------+--------------+-----------+
|   1     | War       |   great 3D   |   8.9     |
|   2     | Science   |   fiction    |   8.5     |
|   3     | irish     |   boring     |   6.2     |
|   4     | Ice song  |   Fantacy    |   8.6     |
|   5     | House card|   Interesting|   9.1     |
+---------+-----------+--------------+-----------+

對于上面的例子，則正確的輸出是為：

+---------+-----------+--------------+-----------+
|   id    | movie     |  description |  rating   |
+---------+-----------+--------------+-----------+
|   5     | House card|   Interesting|   9.1     |
|   1     | War       |   great 3D   |   8.9     |
+---------+-----------+--------------+-----------+

來源：力扣（LeetCode） 鏈接：https://leetcode-cn.com/problems/not-boring-movies
著作權歸領扣網路所有，商業轉載請聯系官方授權，非商業轉載請注明出處，

題解

簡單題

select * from cinema
where id%2=1 and description!='boring'
order by rating desc;

題二：換座位

小美是一所中學的資訊科技老師，她有一張 seat 座位表，平時用來儲存學生名字和與他們相對應的座位 id，

其中縱列的 id 是連續遞增的，小美想改變相鄰倆學生的座位，

你能不能幫她寫一個 SQL query 來輸出小美想要的結果呢？

示例：

+---------+---------+
|    id   | student |
+---------+---------+
|    1    | Abbot   |
|    2    | Doris   |
|    3    | Emerson |
|    4    | Green   |
|    5    | Jeames  |
+---------+---------+

假如資料輸入的是上表，則輸出結果如下：

+---------+---------+
|    id   | student |
+---------+---------+
|    1    | Doris   |
|    2    | Abbot   |
|    3    | Green   |
|    4    | Emerson |
|    5    | Jeames  |
+---------+---------+

注意：

如果學生人數是奇數，則不需要改變最后一個同學的座位，

來源：力扣（LeetCode）
鏈接：https://leetcode-cn.com/problems/exchange-seats
著作權歸領扣網路所有，商業轉載請聯系官方授權，非商業轉載請注明出處，

題解

SELECT
    (CASE
        WHEN MOD(id, 2) != 0 AND counts != id THEN id + 1
        WHEN MOD(id, 2) != 0 AND counts = id THEN id
        ELSE id - 1
    END) AS id,
    student
FROM
    seat,
    (SELECT
        COUNT(*) AS counts
    FROM
        seat) AS seat_counts
ORDER BY id ASC;

識訓

原來可以用分支陳述句

方法：使用 CASE

對于所有座位 id 是奇數的學生，修改其 id 為 id+1，如果最后一個座位 id 也是奇數，則最后一個座位 id 不修改，對于所有座位 id 是偶數的學生，修改其 id 為 id-1，

首先查詢座位的數量，

SELECT
    COUNT(*) AS counts
FROM
    seat

然后使用 CASE 條件和 MOD 函式修改每個學生的座位 id，

SELECT
    (CASE
        WHEN MOD(id, 2) != 0 AND counts != id THEN id + 1
        WHEN MOD(id, 2) != 0 AND counts = id THEN id
        ELSE id - 1
    END) AS id,
    student
FROM
    seat,
    (SELECT
        COUNT(*) AS counts
    FROM
        seat) AS seat_counts
ORDER BY id ASC;

作者：LeetCode
鏈接：https://leetcode-cn.com/problems/exchange-seats/solution/huan-zuo-wei-by-leetcode/
來源：力扣（LeetCode） 著作權歸作者所有，商業轉載請聯系作者獲得授權，非商業轉載請注明出處，