在SQL中,COALESCE
函数是一个非常有用的函数,用于从其参数列表中返回第一个非NULL
值。如果所有给定的参数都是NULL
,那么COALESCE
函数将返回NULL
。这个函数可以接受多个参数,使其在处理可能出现的NULL
值时非常灵活和强大。
语法
COALESCE(expression1, expression2, ..., expressionN)
-
expression1, expression2, ..., expressionN
:是COALESCE
函数要检查的表达式列表。函数会从左到右评估这些表达式,返回第一个非NULL
的表达式值。
使用场景
-
默认值设置:当你希望某个列或表达式返回一个默认值(而不是
NULL
)时,COALESCE
可以提供这个默认值。这对于数据报告和用户界面显示特别有用,因为你可以避免显示NULL
值,而是显示一个更有意义的默认值。 -
数据清洗:在处理含有
NULL
值的数据时,COALESCE
可以帮助你将这些NULL
值转换为实际的数值或文本,便于分析和计算。 -
条件选择:
COALESCE
可以用于基于数据存在性(是否为NULL
)条件性地选择值。
示例
假设你有一个Employees
表,其中包含员工的salary
列,你想要选择一个列,显示员工的薪水,如果薪水是NULL
,则显示0
。
SELECT COALESCE(salary, 0) AS effective_salary FROM Employees;
这个查询通过COALESCE
函数确保了effective_salary
列不会包含NULL
值;如果salary
是NULL
,则effective_salary
会显示为0
。
小结
COALESCE
函数提供了一种简单有效的方式来处理SQL查询中的NULL
值,使得数据分析和展示更加灵活和清晰。它是处理NULL
值时应该考虑的首选函数之一,特别是当你需要从一组可能的NULL
值中选择第一个实际存在的值时。
leetcode例题:1378. 使用唯一标识码替换员工ID
题目描述
Employees
表:
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| id | int |
| name | varchar |
+---------------+---------+
在 SQL 中,id 是这张表的主键。
这张表的每一行分别代表了某公司其中一位员工的名字和 ID 。
EmployeeUNI
表:
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| id | int |
| unique_id | int |
+---------------+---------+
在 SQL 中,(id, unique_id) 是这张表的主键。
这张表的每一行包含了该公司某位员工的 ID 和他的唯一标识码(unique ID)。
展示每位用户的 唯一标识码(unique ID );如果某位员工没有唯一标识码,使用 null 填充即可。
你可以以 任意 顺序返回结果表。
返回结果的格式如下例所示。
示例 1:
输入:
Employees 表:
+----+----------+
| id | name |
+----+----------+
| 1 | Alice |
| 7 | Bob |
| 11 | Meir |
| 90 | Winston |
| 3 | Jonathan |
+----+----------+
EmployeeUNI 表:
+----+-----------+
| id | unique_id |
+----+-----------+
| 3 | 1 |
| 11 | 2 |
| 90 | 3 |
+----+-----------+
输出:
+-----------+----------+
| unique_id | name |
+-----------+----------+
| null | Alice |
| null | Bob |
| 2 | Meir |
| 3 | Winston |
| 1 | Jonathan |
+-----------+----------+
解释:
Alice and Bob 没有唯一标识码, 因此我们使用 null 替代。
Meir 的唯一标识码是 2 。
Winston 的唯一标识码是 3 。
Jonathan 唯一标识码是 1 。
解答
要解决这个问题,你可以使用 SQL 的 LEFT JOIN
语句来连接 Employees
表和 EmployeeUNI
表,并且使用 COALESCE
函数来处理那些没有匹配 unique_id
的情况,将它们填充为 NULL
。LEFT JOIN
会返回左表 (Employees
) 的所有行,如果左表的行在右表 (EmployeeUNI
) 中没有匹配行,则结果中对应行的 EmployeeUNI
表列会包含 NULL
值。
以下是实现该逻辑的 SQL 查询:
SELECT
COALESCE(EU.unique_id, NULL) AS unique_id,
E.name
FROM
Employees E
LEFT JOIN
EmployeeUNI EU ON E.id = EU.id
ORDER BY
E.id; -- 或者根据需要排序,比如按照 name 或 unique_id
这个查询做了以下事情:
-
FROM Employees E
– 从Employees
表开始,为表设置了一个别名E
以简化后续引用。 -
LEFT JOIN EmployeeUNI EU ON E.id = EU.id
– 通过LEFT JOIN
将Employees
表和EmployeeUNI
表连接起来,基于两表的id
字段。EmployeeUNI
表也被赋予了别名EU
。 -
COALESCE(EU.unique_id, NULL) AS unique_id
–COALESCE
函数返回其参数列表中的第一个非NULL
值。在这里,如果EU.unique_id
是NULL
(意味着LEFT JOIN
没有找到匹配的行),则结果仍然是NULL
。虽然在这种情况下使用COALESCE
函数可能看起来多余(因为EU.unique_id
本身在没有匹配的情况下就是NULL
),但它在这里说明了如何处理可能的NULL
值。实际上,你可以直接选择EU.unique_id
。 -
ORDER BY E.id
– 结果按照员工的id
排序。这一步是可选的,取决于你想如何展示结果。
注意,这个查询确保了即使某些员工没有对应的 unique_id
,他们的名字仍然会出现在查询结果中,unique_id
列用 NULL
表示他们缺少唯一标识码。