coalesce函数

发表于 分类于 阅读次数： 本文字数： 539 阅读时长 ≈ 1 分钟

coalesce函数的使用说明

用法

返回其参数中第一个非空表达式。

语法

coalesce ( 表达式1 ,…表达式n )

返回类型

返回数据类型优先级最高的表达式的数据类型，如果所有参数均为null，则coalesce返回null。

1
2
SELECT COALESCE(NULL,1)
SELECT COALESCE(NULL,NULL,NULL)

第一行，由于第一个表达式为null，依次寻找不为null的结果，所以返回值应该是1。

第二行，由于所有的表达式都为null，所以返回值为null。

等效的case函数
case语句
1
2
3
4
5
6
SELECT COALESCE(expression1,expression2,...expressionN)
CASE
   WHEN (expression1 IS NOT NULL) THEN expression1
   WHEN (expressionN IS NOT NULL) THEN expressionN
   ELSE NULL
END

尽管 IS NULL 等效于 COALESCE，但它们的行为是不同的。包含具有非空参数的 IS NULL 的表达式将视为 NOT NULL，而包含具有非空参数的 COALESCE 的表达式将视为 NULL。

SQL Server的非空参数问题

在 SQL Server 中，若要对包含具有非空参数的 COALESCE 的表达式创建索引，可以使用 PERSISTED 列属性将计算列持久化，例如下面案例

1
2
3
4
5
6
CREATE TABLE #CheckSumTest
(
   ID int identity ,
   Num int DEFAULT ( RAND() * 100 ) ,
   RowCheckSum AS COALESCE( CHECKSUM( id , num ) , 0 ) PERSISTED PRIMARY KEY
)
COALESCE 使用示例

在以下示例中，wages 表中包括以下三列有关雇员的年薪的信息：hourly wage、salary 和 commission。但是，每个雇员只能接受一种付款方式。若要确定支付给所有雇员的金额总数，请使用 COALESCE 函数，它只接受在 hourly_wage、salary 和 commission 中找到的非空值。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
SET NOCOUNT ON;

GO

USE master;
IF EXISTS (SELECT name FROM sys.tables
      WHERE 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 CAST(COALESCE(hourly_wage * 40 * 52,
   salary,
   commission * num_sales) AS money) AS 'Total Salary'
FROM wages;

GO

结果集

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
Total Salary
------------
20800.0000

41600.0000

62400.0000

83200.0000

10000.0000

20000.0000

30000.0000

40000.0000

45000.0000

50000.0000

120000.0000

56000.0000

(12 row(s) affected)