LeetCode mysql 刷題一：計算特殊獎金 | 買下所有產品的客戶

實際開發中 sql 的高階用法並不常用，特別是在做資料庫遷移時，高階用法簡直是噩夢

只滿足於簡單的查詢，然後用程式碼實現相關邏輯，又感覺自己的 sql 能力太弱

透過 leetcode 中資料庫相關的練習題，刷下題目，增加下自己的 sql 能力

leetcode 只提供了 MySQL 和 Oracle 兩種資料庫，我是用 MySQL 刷題的

下面兩條題目：

第一題[簡單]，主要考察 MySQL 的基本用法：比如正則使用，條件判斷，如果判斷偶數
第二題[中等]，主要考察 MySQL 的高階用法：比如將一張表的統計結果去和另一張表的資料做匹配

題目一

題目連結：計算特殊獎金

編寫解決方案，計算每個僱員的獎金。如果一個僱員的 id 是奇數並且他的名字不是以 'M' 開頭，那麼他的獎金是他工資的 100% ，否則獎金為 0 。

返回的結果按照 employee_id 排序。

Create table If Not Exists Employees (employee_id int, name varchar(30), salary int);
Truncate table Employees;
insert into Employees (employee_id, name, salary) values ('2', 'Meir', '3000');
insert into Employees (employee_id, name, salary) values ('3', 'Michael', '3800');
insert into Employees (employee_id, name, salary) values ('7', 'Addilyn', '7400');
insert into Employees (employee_id, name, salary) values ('8', 'Juan', '6100');
insert into Employees (employee_id, name, salary) values ('9', 'Kannon', '7700');

輸入：
Employees 表:
+-------------+---------+--------+
| employee_id | name    | salary |
+-------------+---------+--------+
| 2           | Meir    | 3000   |
| 3           | Michael | 3800   |
| 7           | Addilyn | 7400   |
| 8           | Juan    | 6100   |
| 9           | Kannon  | 7700   |
+-------------+---------+--------+
輸出：
+-------------+-------+
| employee_id | bonus |
+-------------+-------+
| 2           | 0     |
| 3           | 0     |
| 7           | 7400  |
| 8           | 0     |
| 9           | 7700  |
+-------------+-------+
解釋：
因為僱員id是偶數，所以僱員id 是2和8的兩個僱員得到的獎金是0。
僱員id為3的因為他的名字以'M'開頭，所以，獎金是0。
其他的僱員得到了百分之百的獎金。

解析

本題考察了三個知識點：

判斷 name 中首字母是 M 的方法有 LIKE 和 REGEXP 兩種：
- 使用 LIKE，用左匹配：M%
- 使用 REGEXP，正則匹配有很多種，正則的寫法有很多種，就不一一列舉了
  - ^M：以 M 開頭
  - ^M.*：以 M 開頭，後面跟任意字元
  - ^[^M]：以非 M 開頭
- 還有一個跟 REGEXP 類似的 RLIKE，也是匹配正則
匹配偶數方法有很多，可以看這題 620. 有趣的電影，用了 6 種方法判斷奇數
條件判斷如果滿足輸出 bonus 否者輸出 0，有兩種方法：
- IF：IF (condition, true, false)
- CASE：CASE WHEN condition THEN true ELSE false END

掌握了上面的方法，你就可以寫出 24 種 SQL 語句了，下面是其中一種

SELECT
    employee_id,
    IF (employee_id % 2 != 0 AND name NOT LIKE 'M%', salary, 0) bonus
FROM
    Employees
ORDER BY employee_id;

題目二

題目連結：買下所有產品的客戶

編寫解決方案，報告 Customer 表中購買了 Product 表中所有產品的客戶的 id。

返回結果表無順序要求。

返回結果格式如下所示。

Create table If Not Exists Customer (customer_id int, product_key int);
Create table Product (product_key int);
Truncate table Customer;
insert into Customer (customer_id, product_key) values ('1', '5');
insert into Customer (customer_id, product_key) values ('2', '6');
insert into Customer (customer_id, product_key) values ('3', '5');
insert into Customer (customer_id, product_key) values ('3', '6');
insert into Customer (customer_id, product_key) values ('1', '6');
Truncate table Product;
insert into Product (product_key) values ('5');
insert into Product (product_key) values ('6');

Customer 表：
+-------------+-------------+
| customer_id | product_key |
+-------------+-------------+
| 1           | 5           |
| 2           | 6           |
| 3           | 5           |
| 3           | 6           |
| 1           | 6           |
+-------------+-------------+
該表可能包含重複的行。
customer_id 不為 NULL。
product_key 是 Product 表的外來鍵(reference 列)。

Product 表：
+-------------+
| product_key |
+-------------+
| 5           |
| 6           |
+-------------+
輸出：
+-------------+
| customer_id |
+-------------+
| 1           |
| 3           |
+-------------+
product_key 是這張表的主鍵（具有唯一值的列）。

解釋：
購買了所有產品（5 和 6）的客戶的 id 是 1 和 3 。

本題考察了 2 個知識點：

如何查詢出一個使用者購買了多少件商品
如何關聯兩張表中的資料

解析

方法一

全部有多少件商品：
- SELECT COUNT(*) FROM product;
每個使用者購買了哪些商品：
- SELECT customer_id, group_concat(product_key) AS product_key FROM Customer GROUP BY customer_id;
分組之後透過 having 過濾掉購買商品數量不等於全部商品數量的使用者：
- 第一個 count 需要使用 distinct 去重，可能會出現同一個使用者買了多件商品
- 第二個 count 不需要去重，因為商品不會重複
  COUNT(DISTINCT product_key) = (SELECT COUNT(*) FROM Product);

SELECT
  customer_id
FROM
  Customer
GROUP BY
  customer_id
HAVING
  count( DISTINCT product_key ) = ( SELECT count(*) FROM Product );

方法二

先透過 group by coustomer_id 分組
分組之後，可以使用 group_concat 函式將商品編號拼接成字串
- group_concat( DISTINCT product_key ORDER BY product_key )：將 Customer 表中每個使用者購買的商品編號拼接成字串
- SELECT group_concat( product_key ORDER BY product_key ) FROM Product：將 Product 表中的商品編號拼接成字串
然後比較兩個字串是否相等

SELECT
  customer_id
FROM
  Customer
GROUP BY
  customer_id
HAVING
  group_concat( DISTINCT product_key ORDER BY product_key ) = ( SELECT group_concat( product_key ORDER BY product_key ) FROM Product )

LeetCode mysql 刷題一：計算特殊獎金 | 買下所有產品的客戶

題目一

解析

題目二

解析

方法一

方法二

更多題目

相關文章