PostgreSQL15——CASE 条件表达式

CASE 表达式的作用就是为 SQL 语句增加类似于 IF-THEN-ELSE 的逻辑处理功能，可以根据不同的条件返回不同的结果。PostgreSQL 支持两种形式的条件表达式：简单 CASE 表达式和搜索 CASE 表达式。另外，为了方便空值处理，PostgreSQL 还提供了两个缩写形式的 CASE 表达式（函数）：NULLIF 和COALEASE。

一、简单 CASE 表达式

简单CASE 表达式的语法如下：

CASE expression
	WHEN value1 THEN result1
	WHEN value2 THEN result2
	[...]
	[ELSE default_result]
END;

表达式的计算过程如下图所示：

首先计算表达式（expression）的值，然后依次与 WHEN 列表中的值（value1，value2，…）进行比较，找到第一个匹配的值，然后返回对应 THEN 列表中的结果（result1，result2，…）；如果没有找到匹配的值，返回 ELSE 中的默认值；如果没有指定ELSE，返回 NULL。

下面的查询使用简单CASE 表达式统计每个部门的人数，并且转换为列的方式显示：

SELECT SUM(CASE department_id WHEN 10 THEN 1 ELSE 0 END) AS dept_10_count,
	   SUM(CASE department_id WHEN 20 THEN 1 ELSE 0 END) AS dept_20_count,
	   SUM(CASE department_id WHEN 30 THEN 1 ELSE 0 END) AS dept_30_count
FROM employees;

需要注意的是每个分支的结果必须具有相同的数据类型，否则会产生类型错误。例如，以下示例对于不同条件返回的数据类型不一致：

SELECT first_name,
	   last_name,
	   CASE department_id
		  WHEN 10 THEN 'Administration'
		  WHEN 20 THEN 20
		  WHEN 30 THEN 'Purchasing'
		  ELSE 'Others' END AS department_name
FROM employees;

-- ERROR:  invalid input syntax for type integer: "Others"
-- LINE 7:     ELSE 'Others' END AS department_name

简单CASE 表达式在进行计算的时候，使用的是等值比较（=），能够支持简单的逻辑处理。如果想要基于更加复杂的条件进行判断，例如根据某个列的取值范围返回不同的信息，或者判断表达式的值是否为空，都需要使用更加强大的搜索CASE 表达式。

二、搜索 CASE 表达式

搜索CASE 表达式的语法如下：

CASE
	WHEN condition1 THEN result1
	WHEN condition2 THEN result2
	...
	[ELSE default_result]
END

表达式的计算过程如下图所示：

按照顺序依次计算 WHEN 子句中的条件（condition1, condition2, …），找到第一个结果为真的分支，返回相应的结果；如果没有任何条件为真，返回 ELSE 中的默认值；如果此时没有指定 ELSE，返回空值。

搜索CASE 表达式可以在 WHEN 子句中构造复杂的条件，完成各种逻辑处理。首先，所有的简单 CASE 表达式都可以替换称等价的搜索CASE 表达式。我们将前面的示例改写如下：

SELECT SUM(CASE WHEN department_id = 10 THEN 1 ELSE 0 END) AS dept_10_count,
	   SUM(CASE WHEN department_id = 20 THEN 1 ELSE 0 END) AS dept_20_count,
	   SUM(CASE WHEN department_id = 30 THEN 1 ELSE 0 END) AS dept_30_count
FROM employees;

以下示例根据薪水的范围将员工的收入分为高中低三个档次：

SELECT e.first_name,
	   e.last_name,
	   e.salary,
	   CASE
		  WHEN e.salary < 5000 THEN '低'
		  WHEN e.salary < 15000 THEN '中'
		  ELSE '高'
	   END AS salary_level
FROM employees e;

既然是表达式， CASE 表达式除了可以用于SELECT 列表，也可以出现在其他 SQL 子句中，例如 WHERE 条件子句、GROUP BY 分组子句、ORDER BY 排序子句等。以下示例除了将薪水显示为三个档次，同时还按照档次和名字进行排序：

SELECT e.first_name,
	   e.last_name,
	   e.salary,
	   CASE
		  WHEN e.salary < 5000 THEN '低'
		  WHEN e.salary < 15000 THEN '中'
		  ELSE '高'
	   END AS salary_level
FROM employees e
ORDER BY CASE
	WHEN e.salary < 5000 THEN 3
	WHEN e.salary < 15000 THEN 2
	ELSE 1 END, first_name;

三、缩写函数

除了以上两种形式的 CASE 表达式之外，PostgreSQL 还提供了两个与 NULL 相关的缩写CASE 表达式（函数）：NULLIF 和COALEASE。

NULLIF 函数的用法如下：

NULLIF(expression_1, expression_2)

NULLIF 函数包含 2 个参数，如果第一个参数等于第二个参数，返回 NULL；否则，返回第一个参数的值。它可以使用等价的CASE 表达式表示为：

CASE
	WHEN expression_1 = expression_2 THEN NULL
	ELSE expression_1
END

以下示例说明了 NULLIF 函数的效果：

SELECT NULLIF(1, 1), NULLIF('A', 'B');

NULLIF 函数的一个常见用途是防止除零错误：

SELECT 1 / 0; -- 除零错误
SELECT 1 / NULLIF(0 , 0);

COALEASE 函数的语法如下：

COALESCE(expression_1, expression_2, expression_3, ...)

COALESCE 函数接受多个参数，并且返回第一个非空的参数值；如果所有参数都为空值，返回 NULL 值。它可以使用等价的 CASE 表达式表示为：

CASE
	WHEN expression_1 IS NOT NULL THEN expression_1
	WHEN expression_2 IS NOT NULL THEN expression_2
	WHEN expression_3 IS NOT NULL THEN expression_3
	...
END

以下示例将佣金比率为空的数据显示为 0：

SELECT e.first_name,
	   e.last_name,
	   e.commission_pct,
	   COALESCE(e.commission_pct, 0)
FROM employees e;