leetcode 高频 SQL 50 题记录

1. 寻找今年具有正收入的客户

题目描述：

表：`Customers`

+--------------+------+
| Column Name  | Type |
+--------------+------+
| customer_id  | int  |
| year         | int  |
| revenue      | int  |
+--------------+------+
(customer_id, year) 是这个表的主键。
这个表包含客户 ID 和不同年份的客户收入。
注意，这个收入可能是负数。

写一个 SQL 查询来查询 2021 年具有 正收入 的客户。

可以按 任意顺序 返回结果表。

查询结果格式如下例。

Customers
+-------------+------+---------+
| customer_id | year | revenue |
+-------------+------+---------+
| 1           | 2018 | 50      |
| 1           | 2021 | 30      |
| 1           | 2020 | 70      |
| 2           | 2021 | -50     |
| 3           | 2018 | 10      |
| 3           | 2016 | 50      |
| 4           | 2021 | 20      |
+-------------+------+---------+

Result table:
+-------------+
| customer_id |
+-------------+
| 1           |
| 4           |
+-------------+
客户 1 在 2021 年的收入等于 30 。
客户 2 在 2021 年的收入等于 -50 。
客户 3 在 2021 年没有收入。
客户 4 在 2021 年的收入等于 20 。
因此，只有客户 1 和 4 在 2021 年有正收入。

解答：

因为题中声明(customer_id, year) 是这个表的主键，所以不会有重复值，直接 `AND` 条件查询即可。

SELECT customer_id 
FROM Customers
WHERE year = 2021 and revenue>0;

2. 从不订购的客户

题目描述：

`Customers` 表：

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| name        | varchar |
+-------------+---------+
在 SQL 中，id 是该表的主键。
该表的每一行都表示客户的 ID 和名称。

`Orders` 表：

+-------------+------+
| Column Name | Type |
+-------------+------+
| id          | int  |
| customerId  | int  |
+-------------+------+
在 SQL 中，id 是该表的主键。
customerId 是 Customers 表中 ID 的外键( Pandas 中的连接键)。
该表的每一行都表示订单的 ID 和订购该订单的客户的 ID。

找出所有从不点任何东西的顾客。

以 任意顺序 返回结果表。

结果格式如下所示。

示例 1：

输入：
Customers table:
+----+-------+
| id | name  |
+----+-------+
| 1  | Joe   |
| 2  | Henry |
| 3  | Sam   |
| 4  | Max   |
+----+-------+
Orders table:
+----+------------+
| id | customerId |
+----+------------+
| 1  | 3          |
| 2  | 1          |
+----+------------+
输出：
+-----------+
| Customers |
+-----------+
| Henry     |
| Max       |
+-----------+

解答：

1. `NOT IN`: 使用嵌套查询的方法，首先查询出订购的客户的 id，然后客户 id 不在这个表中的即为没有订购的客户。

SELECT name AS 'Customers'
FROM Customers
WHERE id NOT IN 
(SELECT CustomerId from Orders);

2. `LEFT JOIN`：客户表与订单表左连接后查询订单 id 为 NULL 的即为没有订购的客户。

SELECT name AS 'Customers'
FROM Customers
LEFT JOIN Orders 
ON Customers.Id = Orders.CustomerId
WHERE Orders.CustomerId IS NULL;

3. 计算特殊奖金

题目描述：

表: `Employees`

+-------------+---------+
| 列名        | 类型     |
+-------------+---------+
| employee_id | int     |
| name        | varchar |
| salary      | int     |
+-------------+---------+
employee_id 是这个表的主键(具有唯一值的列)。
此表的每一行给出了雇员id ，名字和薪水。

编写解决方案，计算每个雇员的奖金。如果一个雇员的 id 是 奇数 并且他的名字不是以 'M' 开头，那么他的奖金是他工资的 100% ，否则奖金为 0 。

返回的结果按照 employee_id 排序。

返回结果格式如下面的例子所示。

示例 1:

输入：
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。
其他的雇员得到了百分之百的奖金。

解答：

1. `IF`：如果id%2=1，则拿到全部薪水，否则奖金为0。此外，要根据id升序排序。

SELECT 
    employee_id,
    IF(employee_id % 2 = 1 AND name NOT REGEXP '^M', salary, 0) AS bonus 
FROM 
    employees 
ORDER BY 
    employee_id;

注意事项：

1. IF 函数来实现条件判断。IF 函数接受三个参数：一个条件表达式、一个为真时的返回值和一个为假时的返回值。根据条件表达式的计算结果，IF 函数会返回相应的值。

SELECT IF(condition, value_if_true, value_if_false) AS result;

2. 姓名不以 `M` 开头的几种写法：

name NOT LIKE 'M%';

LEFT(name,1) != 'M';

name NOT REGEXP '^M';

同样，不以 `M` 结尾对应的三种写法：

-- 使用 `LIKE` 进行模糊匹配：
name NOT LIKE '%M';

-- 使用 `RIGHT` 函数获取字符串右侧指定长度的字符进行比较：
RIGHT(name, 1) != 'M';

-- 使用 `NOT REGEXP` 搭配正则表达式 `M$` 进行匹配：
name NOT REGEXP 'M$';

3. `ORDER BY` 默认是升序( ASC)排列，指定降序可用(DESC)