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