PostgreSQL CASE条件表达式

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

简单CASE表达式

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

CASE expressionWHEN value1 THEN result1WHEN 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;

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

搜索CASE 表达式

搜索CASE表达式的语法如下

CASEWHEN condition1 THEN result1WHEN 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;

如果薪水低于5000，满足第一个WHEN子句的条件，返回“低”；否则进入第二个WHEN子句，如果小于15000（同时大于等于5000），返回“中”；否则进入ELSE子句，返回“高”。既然是表达式，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); --返回 null 

COALEASE函数的语法如下：

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

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

CASE WHEN expression_1 IS NOT NULL THEN expression_1WHEN expression_2 IS NOT NULL THEN expression_2WHEN 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;