16.空值函数

最新推荐文章于 2025-01-17 17:55:48 发布

weixin_34203832

最新推荐文章于 2025-01-17 17:55:48 发布

阅读量314

点赞数

本文介绍了SQL中处理空值的三个重要函数：Coalesce、IsNull和NullIf。通过实例展示了如何利用这些函数来有效管理和操作数据库中的空值，特别强调了Coalesce函数在聚合多个可能为空的列时的应用。

1.Coalesce

coalesce的意思是聚合、联合、合并，这个函数主要用来进行空值处理，其参数格式如下：

coalesce(expression1，...，expressionn)，所有表达式必须是相同类型，或者可以隐性转换为相同的类型。该函数用于返回参数列表中第一个非空值，如果所有的表达式都为空值，则返回NULL。

与此 CASE 函数等价：

Case
   When (expression1 IS NOT NULL) then expression1
   ...
   When (expressionN IS NOT NULL) then expressionN
   else NULL

在下面的示例中，显示包含三列有关某个雇员每年工资收入信息的 wages表：hourly_wage(小时工资)、salary(薪水) 和 commission(佣金)。但是，每个雇员只能接受一种付款方式。若要确定支付给所有雇员的工资总额，请使用 Coalesce 函数接受在 hourly_wage、salary 和 commission 中找到的非空值。

SET NOCOUNT ON
GO
USE master
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'wages')
DROP TABLE wages
GO
CREATE TABLE wages
(
   emp_id      tinyint    identity,
   hourly_wage   decimal   NULL,
   salary      decimal    NULL,
   commission   decimal   NULL,
   num_sales   tinyint   NULL
)
GO
INSERT wages VALUES(10.00, NULL, NULL, NULL)
INSERT wages VALUES(20.00, NULL, NULL, NULL)
INSERT wages VALUES(30.00, NULL, NULL, NULL)
INSERT wages VALUES(40.00, NULL, NULL, NULL)
INSERT wages VALUES(NULL, 10000.00, NULL, NULL)
INSERT wages VALUES(NULL, 20000.00, NULL, NULL)
INSERT wages VALUES(NULL, 30000.00, NULL, NULL)
INSERT wages VALUES(NULL, 40000.00, NULL, NULL)
INSERT wages VALUES(NULL, NULL, 15000, 3)
INSERT wages VALUES(NULL, NULL, 25000, 2)
INSERT wages VALUES(NULL, NULL, 20000, 6)
INSERT wages VALUES(NULL, NULL, 14000, 4)
GO
SET NOCOUNT OFF
GO
SELECT coalesce(hourly_wage * 40 * 52,salary,commission*num_sales) AS 'Total Salary' 
FROM wages
GO

2.IsNull

语法：IsNull(check_expression ,replacement_value )。如果 check_expression 不为 NULL，那么返回该表达式的值；否则返回 replacement_value。

参数：check_expression :将被检查是否为null的表达式，可以是任何类型。

replacement_value :在 check_expression 为 NULL时将返回的表达式。replacement_value 必须与 check_expresssion 具有相同的类型。

SELECT AVG(ISNULL(price, $10.00))
FROM titles

3.NullIf

NullIf(expression , expression)：expression可以是常量、列名、函数、子查询或算术运算符、按位运算符以及字符串运算符的任意组合。如果两个表达式不相等，返回第一个 expression 的值。如果相等，返回第一个 expression 类型的空值。返回类型与第一个 expression 相同。