理解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)
);

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

计算列total_price被定义为(coalesce(price, 0) – coalesce(price*discount, 0))* coalesce(1+tax_rate, 1)。
如果price为空，coalesce(price*discount, 0)确保它被视为0。
如果discount为空，coalesce(price*discount)确保它被视为0，并且乘法不会影响计算。
如果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中创建外键约束。

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

sql中的null值是什么？

使用coalesce()处理null值

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

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

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

sql的coalesce函数和数据透视

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

使用sql coalesce进行数据验证

sql coalesce和计算列

sql coalesce和case表达式

最后一句话

相关

如何在Ubuntu 18上安装Kubernetes？

10个适合初学者的微软PowerApps课程

如何在本地主机上安装WordPress进行测试和开发？

7个最佳的Mac数据恢复软件

如何在HTML中使用JavaScript创建交互式网页

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

sql中的null值是什么？

使用coalesce()处理null值

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

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

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

sql的coalesce函数和数据透视

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

使用sql coalesce进行数据验证

sql coalesce和计算列

sql coalesce和case表达式

最后一句话

相关

类似文章