五种主流数据库：条件表达式

最新推荐文章于 2025-03-17 21:21:04 发布

不剪发的Tony老师

最新推荐文章于 2025-03-17 21:21:04 发布

阅读量1.1k

点赞数 30

文章标签：数据库 sql mysql oracle sqlserver postgresql sqlite

本文链接：https://blog.csdn.net/horses/article/details/136554689

版权

SQL 条件表达式（CASE）可以基于不同条件产生不同的结果，实现类似于编程语言中的 IF-THEN-ELSE 逻辑处理功能。例如，根据员工的 KPI 计算相应的涨薪幅度，根据学生考试成绩评出优秀、良好、及格等。

本文比较五种主流数据库条件表达式的实现和差异，包括 MySQL、Oracle、SQL Server、PostgreSQL 以及 SQLite。

条件表达式	MySQL	Oracle	SQL Server	PostgreSQL	SQLite
简单 CASE 表达式	✔️	✔️	✔️	✔️	✔️
搜索 CASE 表达式	✔️	✔️	✔️	✔️	✔️
nullif 函数	✔️	✔️	✔️	✔️	✔️
coalesce 函数	✔️	✔️	✔️	✔️	✔️
专有函数	if、ifnull	decode、nvl、nvl2	isnull、iif		ifnull、iif

简单 CASE 表达式

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

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

语句执行时首先计算 expression 的值，然后将该值和第 1 个 WHEN 子句中的数据（value1）进行比较。如果二者相等，则返回对应 THEN 子句中的结果（result1）；如果二者不相等，则继续将该值和第 2 个 WHEN 子句中的数据（value2）进行比较，如果二者相等，则返回对应 THEN子句中的结果（result2）；依此类推。如果没有找到相等的数据，返回 ELSE 子句中的默认结果（default）；如果没有指定 ELSE 子句，返回空值。

简单 CASE 达式的计算过程如下图所示。

在这里插入图片描述

例如，以下查询使用简单 CASE 表达式将员工的部门编号转换为相应的名称：

SELECT emp_name,
  CASE dept_id
    WHEN 1 THEN '行政管理部'
    WHEN 2 THEN '人力资源部'
    WHEN 3 THEN '财务部'
    WHEN 4 THEN '研发部'
    WHEN 5 THEN '销售部'
    WHEN 6 THEN '保卫部'
    ELSE '其他部门'
  END AS "部门名称"
FROM employee;

查询返回的结果如下：

员工姓名|部门编号|部门名称
------|-------|--------
 刘备  |      1|行政管理部
 关羽  |      2|行政管理部
 张飞  |      3|行政管理部
诸葛亮 |      4|人力资源部
 黄忠  |      5|人力资源部
 魏延  |      6|人力资源部
孙尚香 |      7|财务部 
孙丫鬟 |      8|财务部 
...

简单 CASE 表达式在进行条件判断时使用的是等值比较（=），只能处理简单的比较逻辑。如果想要实现复杂的逻辑处理，例如根据学生考试成绩范围评出优秀、良好等，就需要使用更加强大的搜索 CASE 表达式。

搜索 CASE 表达式

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

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

语句执行时首先判断第 1 个 WHEN 子句中的条件（condition1）是否成立。如果成立，则返回对应 THEN 子句中的结果（result1）；如果不成立，则继续判断第 2 个 WHEN 子句中的条件（condition2）是否成立，如果成立则返回对应 THEN 子句中的结果（result2）；依此类推。如果没有任何条件成立，返回 ELSE 子句中的默认结果（default）；如果没有指定 ELSE 子句，返回空值。

搜索 CASE 达式的计算过程如下图所示。

在这里插入图片描述

前文中的简单 CASE 表达式示例可以使用等价的搜索 CASE 表达式实现：

SELECT emp_name AS "员工姓名",emp_id AS "部门编号",
  CASE
    WHEN dept_id = 1 THEN '行政管理部'
    WHEN dept_id = 2 THEN '人力资源部'
    WHEN dept_id = 3 THEN '财务部'
    WHEN dept_id = 4 THEN '研发部'
    WHEN dept_id = 5 THEN '销售部'
    WHEN dept_id = 6 THEN '保卫部'
  ELSE '其他部门'
  END AS "部门名称"
FROM employee;

搜索 CASE 表达式中的判断条件可以像 WHERE 子句中的过滤条件一样复杂。例如，以下查询基于员工的月薪将他们的收入分为“高”、“中”、“低”三个级别：

SELECT emp_name AS "员工姓名",salary AS "月薪",
  CASE 
    WHEN salary < 10000 THEN '低收入'
    WHEN salary < 20000 THEN '中收入'
    ELSE '高收入'
  END AS "收入级别"
FROM employee;

月薪低于 10 000 元时，返回“低收入”；否则，如果月薪低于 20 000 元（大于或等于 10 000元），返回“中收入”；月薪高于或等于 20 000 元，返回“高收入”。查询返回的结果如下：

员工姓名|月薪    |收入级别
------|--------|-------
 刘备 |30000.00|高收入
 关羽 |26000.00|高收入
 张飞 |24000.00|高收入
诸葛亮 |24000.00|高收入
 黄忠 | 8000.00|低收入
 魏延 | 7500.00|低收入
孙尚香 |12000.00|中收入
孙丫鬟 | 6000.00|低收入
...

CASE 表达式也可以在其他子句中使用，包括 WHERE、ORDER BY 等子句。例如，以下语句使用 CASE 表达式实现了空值的自定义排序：

SELECT emp_name AS "员工姓名",
  CASE
    WHEN bonus IS NULL THEN 0
    ELSE bonus
 END AS "奖金"
FROM employee
WHERE dept_id = 2
ORDER BY CASE
           WHEN bonus IS NULL THEN 0
           ELSE bonus
         END;

其中，ORDER BY 子句中的 CASE 表达式将 bonus 为空的数据转换为 0，从而实现了空值排在最前的效果。查询返回的结果如下：

员工姓名|奖金 
------|-------
 黄忠 | 0
 魏延 | 0
诸葛亮 |8000.00

CASE 表达式是 SQL 中的一个非常实用的功能，而且在 5 种数据库中的实现一致。

案例分析

假如公司即将成立 20 周年，打算给全体员工发放一个周年庆礼品。发放礼品的规则如下：

截至 2020 年入职年限不满 10 年的员工，男性员工的礼品为手表一块，女性员工的礼品为化妆品一套；
截至 2020 年入职年限满 10 年且不满 15 年的员工，男性员工的礼品为手机一部，女性员工的礼品为项链一条；
截至 2020 年入职年限满 15 年的员工，不论男女，礼品统一为电脑一台。

现在，人事部门需要知道为每位员工发放什么礼品。如何通过查询语句得到这些信息呢？搜索 CASE 表达式非常合适这类逻辑条件的处理，我们可以使用以下语句：

-- Oracle、MySQL 以及 PostgreSQL
SELECT emp_name AS "员工姓名", hire_date AS "入职日期",
  CASE
    WHEN EXTRACT(YEAR FROM hire_date)> 2011 AND sex = '男' THEN '手表'
    WHEN EXTRACT(YEAR FROM hire_date) > 2011 AND sex = '女' THEN '化妆品'
    WHEN EXTRACT(YEAR FROM hire_date) > 2006 AND sex = '男' THEN '手机'
    WHEN EXTRACT(YEAR FROM hire_date) > 2006 AND sex = '女' THEN '项链'
    ELSE '电脑'
  END AS "礼品"
FROM employee;

除搜索 CASE 表达式外，我们还使用了 EXTRACT 函数提取员工的入职年份。以上查询适用于 Oracle、MySQL 以及 PostgreSQL。查询返回的结果如下：

员工姓名|入职日期 |礼品
------|----------|---
 刘备 |2000-01-01|电脑
 关羽 |2000-01-01|电脑
 张飞 |2000-01-01|电脑
...
 廖化 |2009-02-17|手机
 关平 |2011-07-24|手机
 赵氏 |2011-11-10|项链
...

Microsoft SQL Server 可以使用 DATEPART 函数提取日期中的信息，例如：

-- Microsoft SQL Server
SELECT emp_name AS "员工姓名", hire_date AS "入职日期",
  CASE
    WHEN DATEPART(YEAR, hire_date) > 2011 AND sex = '男' THEN '手表'
    WHEN DATEPART(YEAR, hire_date) > 2011 AND sex = '女' THEN '化妆品'
    WHEN DATEPART(YEAR, hire_date) > 2006 AND sex = '男' THEN '手机'
    WHEN DATEPART(YEAR, hire_date) > 2006 AND sex = '女' THEN '项链'
    ELSE '电脑'
  END AS "礼品"
FROM employee;

SQLite 可以使用 STRFTIME 函数提取日期中的信息，例如：

-- SQLite
SELECT emp_name AS "员工姓名", hire_date AS "入职日期",
  CASE
    WHEN CAST(STRFTIME('%Y', hire_date) AS INT) > 2011 AND sex = '男' THEN '手表'
    WHEN CAST(STRFTIME('%Y', hire_date) AS INT) > 2011 AND sex = '女' THEN '化妆品'
    WHEN CAST(STRFTIME('%Y', hire_date) AS INT) > 2006 AND sex = '男' THEN '手机'
    WHEN CAST(STRFTIME('%Y', hire_date) AS INT) > 2006 AND sex = '女' THEN '项链'
    ELSE '电脑'
  END AS "礼品"
FROM employee;

STRFTIME 函数返回的数据类型为字符串，我们通过 CAST 函数将其转换为整数。

NULLIF 函数

NULLIF(expr1, expr2)函数接受两个输入参数，如果第 1 个参数等于第 2 个参数，返回空值；否则，返回第 1 个参数的值。例如，以下查询演示了 NULLIF 函数的作用：

SELECT NULLIF(1, 2), NULLIF(2, 2)
FROM employee
WHERE emp_id = 1;

查询返回的结果如下：

NULLIF(1,2)|NULLIF(2,2)
-----------|-----------
          1|          |

因为 1 不等于 2，所以查询结果中的第 1 列返回了数字 1。因为 2 等于 2，所以查询结果中的第 2 列返回了空值。

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

-- 除零错误
SELECT *
FROM employee
WHERE 1 / 0 = 1;


-- 避免除零错误
SELECT *
FROM employee
WHERE 1 / NULLIF(0 , 0) = 1;

第 1 个查询语句中的除数为 0，因此会产生除零错误（MySQL 和 SQLite 可能不会提示错误）。第 2 个查询语句使用了 NULLIF 函数将除数 0 转换为空值，1 除以空值的结果为空值，不会产生错误。

COALESCE 函数

COALESCE (expr1, expr2, expr3, …)函数接受一个输入参数的列表，返回第 1 个非空的参数。如果所有的参数都为空，则返回空值。例如，以下查询返回了“人力资源部”员工的全年收入：

SELECT emp_name AS "员工姓名",
 salary*12 + bonus AS "全年收入",
 salary*12 + COALESCE(bonus, 0) AS "全年收入"
FROM employee
WHERE dept_id = 2;

查询返回的结果如下：

员工姓名|全年收入  |全年收入 
-------|---------|---------
诸葛亮 |296000.00 |296000.00
  黄忠 |          | 96000.00
  魏延 |          | 90000.00

查询结果中的第 2 列没有对 bonus 中的空值进行处理，导致“黄忠”和“魏延”的全年收入为空。第 3 列通过 COALESCE 函数将 bonus 中的空值转换为 0，返回了正确的全年收入。

我们也可以使用 COALESCE 函数实现空值的自定义排序：

SELECT emp_name AS "员工姓名",
 COALESCE(bonus, 0) AS "奖金"
FROM employee
WHERE dept_id = 2
ORDER BY COALESCE(bonus, 0);

其中，ORDER BY 子句中的 COALESCE 函数用于将 bonus 为空的数据转换为 0，从而实现了空值排在最前的效果。查询返回的结果如下：

员工姓名|奖金 
------|-------
  黄忠 | 0
  魏延 | 0
诸葛亮 |8000.00

COALESCE 函数对于空值的处理比搜索 CASE 表达式更加简练。

专有函数

除标准的 CASE 表达式外，一些数据库还提供了专有的扩展函数。

MySQL 提供了一个 if(expr1, expr2, expr3) 函数，如果表达式 expr1 的结果为真（不等于 0 或者不是空值），返回表达式 expr2 的值；否则，返回表达式 expr3 的值。例如：

-- MySQL
SELECT IF(1<2, '1<2', '1>=2') AS result;

查询返回的结果如下：

result
------
1<2

MySQL 和 SQLite 提供了 IFNULL(expr1, expr2)函数，返回第 1 个非空的参数值，等价于只有两个参数的 COALESCE 函数。例如：

-- MySQL 和 SQLite
SELECT emp_name AS "员工姓名",
      salary*12 + IFNULL(bonus, 0) AS "全年收入"
FROM employee
WHERE dept_id = 2;

该查询返回了“人力资源部”员工的全年收入。

Oracle 提供了一个 decode 函数，可以实现类似于简单 CASE 表达式的功能。该函数的语法如下：

DECODE(expression, value1, result1, value2, result2, ...[, default ])

函数执行时依次比较表达式 expression 与 valueN 的值。如果找到相等的值，则返回对应的 resultN；如果没有找到任何相等的值，则返回默认值 default；如果没有指定默认值，则返回空值。

前文中的简单 CASE 表达式示例可以使用 decode 函数实现：

-- Oracle
SELECT emp_name,
       DECODE(dept_id, 1, '行政管理部', 2, '人力资源部',
                       3 ,'财务部', 4, '研发部',
                       5, '销售部', 6, '保卫部', '其他部门'
             ) AS "部门名称"
FROM employee;

Oracle 还提供了 NVL(expr1, expr2) 函数以及 NVL2(expr1, expr2, expr3) 函数。其中，NVL(expr1, expr2) 函数返回第 1 个非空的参数值，等价于只有两个参数的 COALESCE 函数。例如：

-- Oracle
SELECT emp_name AS "员工姓名",
 salary*12 + NVL(bonus, 0) AS "全年收入"
FROM employee
WHERE dept_id = 2;

该查询同样返回了“人力资源部”员工的全年收入。

NVL2(expr1, expr2, expr3) 函数包含 3 个参数，如果第 1 个参数不为空，返回第 2 个参数的值；否则，返回第 3 个参数的值。例如：

-- Oracle
SELECT emp_name AS "员工姓名",
       NVL2(bonus, salary*12 + bonus, salary*12) AS "全年收入"
FROM employee
WHERE dept_id = 2;

Microsoft SQL Server 提供了 ISNULL (expr1, expr2)函数，返回第 1 个非空的参数值，等价于只有两个参数的 COALESCE 函数。例如：

-- Microsoft SQL Server
SELECT emp_name AS "员工姓名",
 salary*12 + ISNULL(bonus, 0) AS "全年收入"
 FROM employee
WHERE dept_id = 2;

该查询同样返回了“人力资源部”员工的全年收入。

Microsoft SQL Server 和 SQLite 还提供了一个 IIF(boolean_expression, true_value, false_value) 函数，如果表达式 boolean_expression 的结果为真，返回表达式 true_value 的值，否则返回表达式 false_value 的值。例如：

-- Microsoft SQL Server 和 SQLite
SELECT IIF(1<2, '1<2', '1>=2') AS result;

查询返回的结果和上面的 IF 函数示例相同。