技術文章 » MySQL題集

MySQL題集

2020-10-01 08:00:02

文章目錄

題一（組合兩個表）

表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） 著作權歸作者所有。商業轉載請聯絡作者獲得授權，非商業轉載請註明出處。