理解SQL中的COALESCE()函数 在SQL中，COALESCE()函数是一种用于处理NULL值的函数。它可以接受多个参数，并返回第一个非NULL值。如果所有参数都为NULL，则返回NULL。 使用COALESCE()函数可以更方便地处理NULL值，避免在查询中出现错误或不完整的结果。 例如，假设我们有一个名为”customers”的表，其中包含以下列：id，name和phone。如果某些客户的电话号码为空，则我们可以使用COALESCE()函数来选择一个备用联系方式。 以下是一个使用COALESCE()函数的示例查询： SELECT id, name, COALESCE(phone, ‘N/A’) AS contact FROM customers; 这将返回一个结果集，其中包含客户的id、name和contact列。如果电话号码为空，则contact列将显示为”N/A”。 在上面的查询中，COALESCE()函数的第一个参数是phone列，第二个参数是字符串”N/A”。如果phone列的值为NULL，则COALESCE()函数将返回字符串”N/A”作为contact列的值。 通过使用COALESCE()函数，我们可以轻松地处理NULL值，确保我们的查询结果始终是完整和准确的。

随着技术的发展和演变，作为开发人员，保持与最新趋势的同步是至关重要的。无论是初学者还是专家，对字符串操作的扎实理解有助于您准备数据（例如，从现有数据中生成不同形式的数据，使其适用于您的业务）并使用内置的sql服务器函数进行管理。

除了数据操作，您还可以检查数据集、评估数据值并对其进行编码或解码，以获得更有意义的数据。因此，这有助于您浏览数据集中的缺失值，理解它们对计算的影响，并简化与数据处理的整体工作流程，以避免可能破坏操作结果的空值。

本指南介绍了sql中的coalesce函数，该函数有助于构建复杂的程序。本文假设您已经接触过并使用过sql，并且只是希望加强对此特定函数的理解。我们的 sql指南系列可以帮助您快速入门。

sql中的coalesce()是什么及其用途？

sql中的coalesce函数按照指定的顺序（类似于列表）评估参数（参数），并返回第一个非空值。简单地说，该函数会顺序评估您的列表，并在第一个非空值的实例处终止。如果列表中的所有参数都为null，则函数返回null。

此外，该函数是包容性的，并且在其他数据库中也受到支持，例如 mysql、azure sql数据库、oracle和postgresql。

您可以在以下情况下使用coalesce：

• 处理空值。
• 将多个查询合并为一个。
• 避免冗长、耗时的case语句。

当用于替代case语句（或isnull函数）时，coalesce可以接受多个参数，而case只能接受两个参数。这种方法可以让您编写更少的代码并简化编写过程。

以下是语法：

coalesce(valueone, valuetwo, valuethree, …, valuex);

sql服务器中的coalesce具有多个属性，包括相同数据类型的参数、接受多个参数以及将整数类型的参数级联的yield函数，以返回一个整数作为输出。

还可以阅读：ultimate sql cheat sheet to bookmark for later

但在介绍如何使用coalesce之前，让我们先了解null。

sql中的null值是什么？

sql中的唯一标记null表示数据库中不存在值。您可以将其视为未定义或未知的值。请不要将其视为空字符串或零值；它表示不存在值。表列中的null表示缺失的信息。

在实际的使用案例中，如果客户未提供其id，则电子商务网站数据库列中的数据列可以填充为null值。在sql中，null是独特的；它是一种状态，而不是其他编程语言中表示“不指向特定对象”的含义。

在sql中，null值对关系数据库有重要影响。首先，它们允许你在使用其他内部函数时排除特定的值。例如，你可以在生产环境中生成一个总订单列表，但其他订单仍需要完成。使用null作为占位符可以让内部的sum函数对总数进行求和。

此外，考虑到使用avg函数生成平均值的情况。如果使用零值，结果会被偏差。相反，数据库可以删除这样的字段并使用null，从而得到准确的输出。

null值并非没有缺点。它们被认为是可变长度的值，可以是字节或多个字节。由于数据库为这些字节留出空间，如果它们超出了存储在数据库中的内容，那么结果就是与使用常规值相比，你的数据库在硬盘上占用更多空间。

此外，在使用某些函数时，你需要对它们进行自定义以消除null值。结果就是你的sql过程变得更长。

使用coalesce()处理null值

null值意味着你可能有一个值，但你不知道该值是什么。在收集填充真实值的数据之前，null值是临时代理。

虽然你可以在数据库中的多个数据类型中使用null值，包括小数、字符串、二进制大对象和整数，但在处理数值数据时最好避免使用它们。

缺点在于当用于数值时，你可能需要在开发与数据一起工作的代码时进行澄清。稍后会详细介绍。

coalesce()有不同的用法来处理null值：

使用coalesce()将null值替换为特定值

你可以使用coalesce()来返回所有null值的特定值。例如，你可能有一个名为”employees”的表，其中包含一个”salary”列，如果员工的工资尚未到账，则该列可能包含null值。因此，在进行一些计算时，你可能希望使用一个特定的值，比如零，来处理所有null条目。下面是如何实现。

select coalesce(salary, 0) as adjusted_salary
from employees;

使用coalesce()从多个选项中选择第一个非null值

有时，你可能希望在一系列表达式中使用第一个非null值。在这种情况下，你通常有多个包含相关数据的列，并且希望优先考虑它们的非null值。语法保持不变。

coalesce (expression1, expression2, …)

在一个实际案例中，假设你有一个名为contacts的表，其中包含preferred_name和full_name两列。你希望生成一个联系人列表，同时显示他们的首选名称（如果有）或全名。下面是解决方法。

select coalesce(preferred_name, full_name) as display_name
from contacts.

如果对于这个测试案例，preferred_name不是null，则会返回它。否则，将full_name作为显示名称返回。

使用sql的coalesce函数进行字符串拼接

如果涉及到空值时，在sql中进行字符串拼接可能会遇到问题。在这种情况下，null作为一个不可取的结果返回。既然null不是我们想要的结果，你可以使用coalesce函数来解决这个问题。以下是一个示例。

简单的字符串拼接方法为：

select ‘你好，你在哪里，’ || ‘john ’ || ? as example

代码返回：

示例

你好，你在哪里，john?

然而，如果你使用一个null值，如下所示：

select ‘你好，你在哪里，’ || null || ‘？’ as example

输出结果为null。

示例

null

由于每次涉及到null值的文本字符串拼接都会返回null，上面的结果是null。然而，可以使用coalesce()函数来解决这个问题。使用该函数，你可以返回一个空字符串（或空格）而不是null。例如，假设你要列出汽车名称及其制造商，下面是你的查询。

select 
car || '，制造商：' || coalesce(manufacturer, '—') as car_brand
from stock

如果制造商为null，则会显示‘—’，而不是null。下面是预期的结果。

car_brand

奥特兰德，制造商：—

飞驰，制造商：宾利

皇家运动员，制造商：—

皇家轿车，制造商：冠军

如你所见，null的结果被消除了，同时你还可以插入你自己的替代字符串值。

sql的coalesce函数和数据透视

sql透视是一种将行转换为列的技术。它允许你将数据从“规范化”形式（具有许多行和少数列）转置（旋转）到“非规范化”形式（少数行和更多列）。coalesce函数可与sql透视一起使用，处理透视结果中的空值。

当你在sql中进行pivot操作，将行转换为列时，结果列是一些数据的聚合函数。如果某个单元格的聚合结果为空，你可以使用coalesce将空值替换为默认值或有意义的表示。以下是一个示例。

考虑一个名为sales的表，它有列year、quarter和revenue，你希望将数据透视，使得年份为列，每个季度的收入总和为值。但是，有些季度没有收入数据，在透视结果中会产生空值。在这种情况下，你可以使用coalesce将透视结果中的空值替换为零（0）。

select
    年份,
    coalesce(sum(case when 季度 = 'q1' then 收入 end), 0) as q1_收入,
    coalesce(sum(case when 季度 = 'q2' then 收入 end), 0) as q2_收入,
    coalesce(sum(case when 季度 = 'q3' then 收入 end), 0) as q3_收入,
    coalesce(sum(case when 季度 = 'q4' then 收入 end), 0) as q4_收入
from 销售
group by 年份;

标量用户定义函数和sql coalesce函数

您可以使用标量udf和coalesce来执行处理空值的复杂逻辑。结合这些特性将帮助您在sql查询中实现更复杂的数据转换和计算。考虑一个名为员工的表，具有以下结构。

create table 员工 (
    员工id int primary key,
    名字 varchar(50),
    姓氏 varchar(50),
    薪水 int,
    奖金 int
);

您可能想要计算每个员工的总收入（薪水加奖金）。然而，有一些缺失值。在这种情况下，您的标量udf可以处理薪水和奖金的相加，而coalesce处理空值。下面是计算总收入的标量udf。

create function dbo.计算总收入 (@薪水 int, @奖金 int)
returns int
as
begin
    declare @总收入 int;
    set @总收入 = @薪水 + coalesce(@奖金, 0);
    return @总收入;
end;
然后可以在查询中使用标量udf和coalesce：
select 员工id, 名字, 姓氏,
       薪水, 奖金, dbo.计算总收入(薪水, 奖金) as 总收入
from 员工;

使用sql coalesce进行数据验证

在使用数据库时，您可能需要验证数值。例如，假设您在表产品中有列产品名称、价格和折扣。您想检索每个项目的产品名称、价格和折扣。但是，您希望将所有null的折扣值视为0。coalesce函数可以帮助实现这一点。以下是如何使用它。

select 产品名称, 价格, coalesce(折扣, 0) as 折扣 
from 产品

sql coalesce和计算列

计算列是基于表内的表达式或其他列计算的虚拟列。由于计算列在数据库中没有物理存储，因此在处理复杂的场景和转换时，可以利用它们与coalesce函数相结合。以下是一个实际用例示例。

考虑使用列价格、折扣和税率的产品表。在这种情况下，您想要创建一个计算列总价，以表示应用折扣和税率后的最终产品价格。如果折扣或税率未指定（为null），您希望使用零进行计算。以下是如何利用coalesce适应此操作。

create table products(
price decimal(10, 2),
discount decimal(10, 2),
tax_rate decimal(5, 2),
total_price as (coalesce(price, 0) – coalesce(price*discount, 0))* coalesce(1+tax_rate, 1)
);

在上面的代码中，发生了以下情况。

1. 计算列total_price被定义为(coalesce(price, 0) – coalesce(price*discount, 0))* coalesce(1+tax_rate, 1)。
2. 如果price为空，coalesce(price*discount, 0)确保它被视为0。
3. 如果discount为空，coalesce(price*discount)确保它被视为0，并且乘法不会影响计算。
4. 如果tax_rate为空，coalesce(1 + tax_rate, 1)确保它被视为0，这意味着不应用税金，并且乘法不会影响计算。

以上设置允许您生成total_price，即计算列，具有实际的最终价格，即使缺少或具有null值。

sql coalesce和case表达式

可以通过case表达式在语法上使用coalesce。以下是一个例子：

select
productname + ‘ ’+ deliverydate productdetails,
dealer,
case
when cellphone is not null then cellphone
when workphone is not null then workphone
else ‘na’
end
emergencycontactnumber
from
dbo.tb_emergencycontact

在上述设置中，case查询类似于coalesce函数。

此外，在同一查询中使用coalesce和case表达式是可行的。这两种技术可以处理null值并同时应用条件逻辑。让我们通过一个例子来说明这一点。

考虑这样一种情况，你有一张名为products的表，其中包含列product_id、product_name、price和discount。你的一些产品有特定的折扣，而其他产品没有。如果一个产品有折扣，你希望显示折扣后的价格，否则应显示正常价格。

select 
    product_id,
    product_name,
    price,
    coalesce(
        case
            when discount > 0 then price - (price * discount / 100)
            else null
        end,
        price
    ) as discounted_price
from products;

在上面的代码中，case检查discount是否大于零，并计算打折后的价格，否则返回null。coalesce函数将case和price的结果作为其参数。它返回第一个非null值，有效地返回折扣后的价格（如果有）或正常价格（如果没有）。

最后一句话

本文演示了在数据库查询中使用coalesce函数的各种方法。通过按照指定的顺序评估参数并返回第一个非null值，coalesce函数简化了查询，使其更有效。

合并函数是一个多功能的函数，无论您处理空值、字符串连接、数据透视、验证还是使用计算列，都可以使用它。通过掌握合并函数，开发人员可以处理缺失数据并创建无错误的数据库设计。请记住，要掌握这种技术，您可能需要进行更深入的实践。

您现在可以查看如何在sql中创建外键约束。

相关