【刷题日记】SQL 写题技巧积累

最近狂刷 SQL 题单，小小总结了一些题目的思路和技巧。

本文题目难度标识：🟩简单，🟨中等，🟥困难。

单纯的 SELECT 语句使用

SQL 中单纯使用 SELECT 语句（不使用 FROM）语句可以完成一些函数测试，或构建简单表。

-- *************** 函数测试
select NOW()
-- | NOW()               |
-- | ------------------- |
-- | 2024-08-26 14:47:13 |

-- *************** 创建一个简单的行
select "Average Salary",0
-- | Average Salary | 0 |
-- | -------------- | - |
-- | Average Salary | 0 |


-- *************** 表的合并结果
select "High Salary",0
union
select "Low Salary",0
-- | High Salary | 0 |  
-- | ----------- | - |  
-- | High Salary | 0 |  
-- | Low Salary  | 0 |

标量子查询将空表处理为 NULL

相关题目：🟨 176. 第二高的薪水 - 力扣（LeetCode）

select 加上标量子查询的技巧：

select "some result" as col_name;

-- 输出：
-- | col_name    |
-- | ----------- |
-- | some result |

select NULL as col_name; -- 其中NULL中可以替代为一个空的子查询

-- 输出：
-- | col_name    |
-- | ----------- |
-- | null        |

"some result" 可以代替为一个标量子查询，该子查询返回 1 行 1 列结果或者 1 列空表。

• 当子查询返回一个结果时，select <子查询> as col_name; 返回列名更改后的子查询结果
• 当子查询为空表时，select <子查询> as col_name; 返回一个 NULL 值

下面的语句和上面的标量子查询的技巧是等价的：

SELECT IFNULL(
	(
		-- 1 行 1 列结果或者 1 列空表
	),
	NULL
) AS col_name

展示分组前三数据 WHERE

相关题目：🟥 185. 部门工资前三高的所有员工 - 力扣（LeetCode）

分组（GROUP BY）中的比率计算

题目：🟩 1211. 查询结果的质量和占比 - 力扣（LeetCode）

Column Name	Type
query_name	varchar
result	varchar
position	int
rating	int

此表可能有重复的行。此表包含了一些从数据库中收集的查询信息：

• “位置”（position）列的值为 1 到 500 。
• “评分”（rating）列的值为 1 到 5 。评分小于 3 的查询被定义为质量很差的查询。

将查询结果的质量 quality 定义为：各查询结果的评分与其位置之间比率的平均值。
将劣质查询百分比 poor_query_percentage 为：评分小于 3 的查询结果占全部查询结果的百分比。

要点：

• 可以利用 IF 方便计算组内一些记录的占比

SELECT 
    query_name, 
    ROUND(AVG(rating/position), 2) quality,
    ROUND(SUM(IF(rating < 3, 1, 0)) * 100 / COUNT(*), 2) poor_query_percentage
FROM Queries
Where query_name IS NOT NULL
GROUP BY query_name

小练习 1：尝试改变上述的 SQL 语句，使其不包含 SUM 和 COUNT 语句。（提示：使用 AVG）

答案：将第 4 行改为
ROUND(AVG(IF(rating < 3, 1, 0)) * 100 , 2) poor_query_percentage

小练习 2：尝试改变小练习 1 中的 SQL 语句，使其不包含 IF 语句。

答案：将其改为
ROUND(AVG(rating < 3) * 100 , 2) poor_query_percentage
因为 AVG 中的语句表达式成立则为 1，不成立则为 0。

相关题目：🟩 1661. 每台机器的进程平均运行时间 - 力扣（LeetCode）

条件统计

利用 COUNT、AVG 对于 NULL 值进行忽略统计的特点，我们可以进行按条件统计。比如：

• COUNT(IF(state = 'approved', 1, NULL)) AS approved_count
• SUM(IF(state = 'approved', amount, NULL)) AS approved_total_amount。其中 NULL 换成 0 的效果是一样的。

相关题目：

• 🟨 1341. 电影评分 - 力扣（LeetCode）

分类统计 UNION

题目：🟨 1907. 按分类统计薪水 - 力扣（LeetCode）

表：Accounts

列名	类型
account_id	int
income	int

在 SQL 中，account_id 是这个表的主键。
每一行都包含一个银行帐户的月收入的信息。

查询每个工资类别的银行账户数量。 工资类别如下：

• "Low Salary"：所有工资 严格低于 20000 美元。
• "Average Salary"： 包含 范围内的所有工资 $[20000, 50000]$ 。
• "High Salary"：所有工资 严格大于 50000 美元。

结果表 必须 包含所有三个类别。 如果某个类别中没有帐户，则报告 0 。

按 任意顺序 返回结果表。

查询结果格式如下示例。

示例 1：

输入：
Accounts 表：

account_id	income
3	108939
2	12747
8	87709
6	91796

输出：

category	accounts_count
Low Salary	1
Average Salary	0
High Salary	3

解释：
低薪：有一个账户 2.
中等薪水：没有。
高薪：有三个账户，他们是 3, 6 和 8.

SELECT 
    'Low Salary' AS category,
    SUM(CASE WHEN income < 20000 THEN 1 ELSE 0 END) AS accounts_count
FROM 
    Accounts
    
UNION
SELECT  
    'Average Salary' category,
    SUM(CASE WHEN income >= 20000 AND income <= 50000 THEN 1 ELSE 0 END) 
    AS accounts_count
FROM 
    Accounts

UNION
SELECT 
    'High Salary' category,
    SUM(CASE WHEN income > 50000 THEN 1 ELSE 0 END) AS accounts_count
FROM 
    Accounts

要点：官方利用 SUM 进行分类统计，然后再组合。不会遗漏统计数为 0 的项目。

而我的方法会忽略掉统计数为 0 的类别，所以还是要自己组装统计数为 0 的项目。

select *
from (
	select if(income < 20000,"Low Salary",IF(income>50000,"High Salary","Average Salary")) category,count(*) accounts_count       
	from  accounts
	group by category
	
	union
		select "Average Salary",0
	
	union
		select "High Salary",0
	
	union
		select "Low Salary",0
	
	) ttt
group by category

自连接的妙用 FROM

找出表中存在连续特征的元组

在一些题目中我们可能要找出以下内容：

• 连续两天、三天登录的用户（可以使用 BETWEEN AND、DATE_SUB 等函数进行区间选择）
• 连续出现两次、三次的数字（连续次数不宜过大）

我们可以对同一张表笛卡尔积多次后，利用 WHERE 条件筛选连续特征的行，然后再进行处理。

题目：🟨 180. 连续出现的数字 - 力扣（LeetCode）

找出所有至少连续出现三次的数字。

输入：
Logs 表：

id	num
1	1
2	1
3	1
4	2
5	1
6	2
7	2

输出：
Result 表：

ConsecutiveNums
1

解释：1 是唯一连续出现至少三次的数字。

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
;

相关题目：

• 🟨 1321. 餐馆营业额变化增长 - 力扣（LeetCode）

前缀和的计算

题目：🟨 1204. 最后一个能进入巴士的人 - 力扣（LeetCode）

有一队乘客在等着上巴士。然而，巴士有 1000 千克 的重量限制，所以其中一部分乘客可能无法上巴士。

编写解决方案找出 最后一个 上巴士且不超过重量限制的乘客，并报告 person_name 。题目测试用例确保顺位第一的人可以上巴士且不会超重。

输入：Queue 表

person_id	person_name	weight	turn
5	Alice	250	1
4	Bob	175	5
3	Alex	350	2
6	John Cena	400	3
1	Winston	500	6
2	Marie	200	4

person_id 是这个表具有唯一值的列。
该表展示了所有候车乘客的信息。
表中 person_id 和 turn 列将包含从 1 到 n 的所有数字，其中 n 是表中的行数。
turn 决定了候车乘客上巴士的顺序，其中 turn=1 表示第一个上巴士，turn=n 表示最后一个上巴士。
weight 表示候车乘客的体重，以千克为单位。

输出：

person_name
John Cena

解释：
为了简化，Queue 表按 turn 列由小到大排序。

Turn	ID	Name	Weight	Total Weight
1	5	Alice	250	250
2	3	Alex	350	600
3	6	John Cena	400	1000	（最后一个上巴士）
4	2	Marie	200	1200	（无法上巴士）
5	4	Bob	175	___
6	1	Winston	500	___

要点：

• 自连接并对顺序进行限制，成组后得到前缀和

SELECT a.person_name
FROM Queue a, Queue b
WHERE a.turn >= b.turn
GROUP BY a.person_id
HAVING sum(b.weight) <=1000
ORDER BY a.turn desc
LIMIT 1

本文参考

• SELECT不加FROM子句的用法_select没有from-CSDN博客