大多数报告需要一些逻辑：将订单标记为大或小，并排计算已付款订单和待处理订单，或者首先显示紧急项目。 SQL CASE WHEN 直接在查询内处理所有这些，并且它适用于所有主要数据库。

我们在 SQLite 3.50 中运行了每个查询，显示的结果来自该运行。 CASE 是标准 SQL 的一部分，因此对于这些示例，它在 PostgreSQL、MySQL、SQL Server 和 Oracle 中的行为方式相同。

什么是“案例何时”？

CASE 是一个表达式，而不是一个单独的命令。它按顺序评估条件并返回一个值，因此它可以到达允许值的任何位置：在 SELECT、WHERE、GROUP BY、ORDER BY 中或在函数内部。它总是以 END 结尾。

CASE
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  ELSE fallback
END

样本表

CREATE TABLE orders (id INT, customer TEXT, total INT, status TEXT);

INSERT INTO orders VALUES
 (1,'Asha',1200,'paid'), (2,'Ben',300,'pending'),
 (3,'Asha',800,'paid'), (4,'Chitra',2500,'shipped'),
 (5,'Dev',150,'cancelled'), (6,'Ben',NULL,'pending');

请注意，订单 6 没有总计。该行稍后很重要。

搜索案例

这是更灵活的形式，因为每个 WHEN 都有自己的完整条件：

SELECT id, total,
       CASE
         WHEN total >= 1000 THEN 'large'
         WHEN total >= 500 THEN 'medium'
         ELSE 'small'
       END AS size
FROM orders;

-- 1 | 1200 | large
-- 2 |  300 | small
-- 3 |  800 | medium
-- 4 | 2500 | large
-- 5 |  150 | small
-- 6 | NULL | small

看最后一行。订单 6 没有总数，但标记为“小”。与 NULL 的两次比较都不正确，因此落到了 ELSE。我们展示了如何在 NULL 部分修复此问题。始终使用 AS 为结果命名，否则您可能会得到一个尴尬的列名称。

简单案例

当您将一列与固定值进行比较时，较短的形式读起来更好：

SELECT id,
       CASE status
         WHEN 'paid' THEN 'Complete'
         WHEN 'shipped' THEN 'Complete'
         WHEN 'pending' THEN 'Waiting'
       END AS label
FROM orders;

-- 1 | Complete
-- 2 | Waiting
-- 3 | Complete
-- 4 | Complete
-- 5 | NULL
-- 6 | Waiting

订单事宜

CASE 在第一个为真条件时停止，这是最常见的逻辑错误。交换我们之前查询中的两个条件：

SELECT id, total,
       CASE
         WHEN total >= 500 THEN 'medium'
         WHEN total >= 1000 THEN 'large'
         ELSE 'small'
       END AS size
FROM orders WHERE id IN (1, 4);

-- 1 | 1200 | medium
-- 4 | 2500 | medium

现在没有什么可以是“大”的。总共 2500 也是至少 500，因此第一个分支获胜，而第二个分支永远不会达到。没有出现错误，只有错误的标签。对于范围，将最严格的条件放在前面，或者写出完整的范围，例如 total >= 500 AND total < 1000。

没有 ELSE 会发生什么？

在上面的简单 CASE 示例中，订单 5 被取消，并且没有分支处理该问题，因此结果是 NULL。有时这正是您想要的，如下一节所示。但在构建标签时，清晰的 ELSE 'other' 通常更好，因为它使意外的值可见，而不是在报告中留下空白。

使用 SUM 的数据透视式报告

这是日常 SQL 中最有用的模式之一。将 CASE 包装在聚合中会将行转换为列，每个客户一行：

SELECT customer,
       SUM(CASE WHEN status = 'paid' THEN total ELSE 0 END) AS paid,
       SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS pending_orders
FROM orders
GROUP BY customer
ORDER BY customer;

-- Asha   | 2000 | 0
-- Ben    |    0 | 2
-- Chitra |    0 | 0
-- Dev    |    0 | 0

现在，一个查询即可提供已付费收入和挂单数量，无需单独的查询或联接。该模式几乎适用于所有数据库，与只有某些数据库提供的专用 PIVOT 语法不同。如果 GROUP BY 对您来说是新的，请阅读 SQL GROUP BY 和 HAVING。

条件计数

COUNT 会跳过 NULL，因此没有 ELSE 的 CASE 仅计算匹配的行：

SELECT COUNT(*) AS all_orders,
       COUNT(CASE WHEN status = 'paid' THEN 1 END) AS paid_orders,
       COUNT(CASE WHEN total >= 1000 THEN 1 END) AS large_orders
FROM orders;

-- 6 | 2 | 2

请注意：如果在 COUNT 内添加 ELSE 0，则每一行都会被计数，因为 0 不为 NULL。将 ELSE 0 与 SUM 一起使用，并与 COUNT 一起省略。 PostgreSQL 和 SQLite 也支持 COUNT(*) FILTER (WHERE ...)，读起来很好，但并不是所有地方都可用。

按 CASE 分组

您可以创建自己的存储桶，然后总结它们：

SELECT CASE WHEN total >= 1000 THEN 'large' ELSE 'other' END AS bucket,
       COUNT(*)
FROM orders
GROUP BY bucket
ORDER BY bucket;

-- large | 2
-- other | 4

按别名分组适用于 SQLite、PostgreSQL 和 MySQL。 SQL Server 不允许这样做，因此您必须重复整个表达式，或者首先将查询放入 CTE 或子查询中。

使用 ORDER BY 自定义排序

按字母顺序排列很少是企业想要的。 CASE 允许您定义自己的顺序：

SELECT id, status FROM orders
ORDER BY CASE status
           WHEN 'pending' THEN 1
           WHEN 'paid' THEN 2
           ELSE 3
         END, id;

-- 2 | pending
-- 6 | pending
-- 1 | paid
-- 3 | paid
-- 4 | shipped
-- 5 | cancelled

待处理订单排在第一位，这对于工作队列来说很方便。第二个排序列 id 使每组中的行保持可预测的顺序。

更新中的案例

CASE 允许一条语句将不同的更改应用于不同的行。此处，小额挂单被标记以供审核：

UPDATE orders
SET status = CASE
               WHEN status = 'pending' AND total < 500 THEN 'review'
               ELSE status
             END;

SELECT id, status FROM orders WHERE customer = 'Ben';

-- 2 | review
-- 6 | pending

ELSE status 保持其他行不变。 忽略它是危险的，因为不匹配的行将被设置为 NULL。如果没有 WHERE 子句，此语句也会触及每一行，因此在大型表上添加一个过滤器，并考虑将更改包装在 事务 中，以便您可以在提交之前检查结果。还要注意，顺序 6 没有改变，因为它的 NULL 总数不满足 total < 500。

空陷阱

NULL 导致 CASE 出现两个不同的问题。首先，简单形式使用相等进行比较，没有任何东西等于 NULL，甚至 NULL 本身也不等于：

SELECT CASE NULL WHEN NULL THEN 'matched' ELSE 'not matched' END;

-- not matched

所以 CASE col WHEN NULL 永远不会匹配。请使用带有 IS NULL 的搜索形式。其次，正如我们在顺序 6 中看到的那样，NULL 值跳过了比较并落在 ELSE 中。修复方法是首先检查它：

SELECT id,
       CASE
         WHEN total IS NULL THEN 'unknown'
         WHEN total >= 1000 THEN 'large'
         ELSE 'other'
       END AS size
FROM orders WHERE id IN (1, 2, 6);

-- 1 | large
-- 2 | other
-- 6 | unknown

Order 6 现在获得了诚实标签。我们的 SQL NULL 值指南 完整地涵盖了这种行为。

CASE 与其他选项

最后一个选择值得考虑。如果 CASE 增长到 30 个分支并出现在 5 个查询中，请将映射移动到表中并 join 到它。那么更改就是单行更新，而不是五个地方的代码更改。

常见错误

• 条件顺序错误。 宽泛的条件首先隐藏了较窄的条件。
• 忘记END。这是最常见的语法错误。
• 使用WHEN NULL。它永远不会匹配，所以使用WHEN col IS NULL。
• 在COUNT中添加ELSE 0。它计算所有行。
• 在更新中省略ELSE。它可以用NULL擦除值。
• WHERE中按CASE过滤。它可以阻止索引被使用。请参阅我们的 SQL 查询优化指南。

最后的话

CASE WHEN 是一个小功能，但可以完成大量工作。请记住三个规则：第一个 true 分支获胜，不匹配的行在没有 ELSE 的情况下变为 NULL，并且 NULL 需要自己的检查。为了进行练习，请获取您当前使用多个查询构建的报告，并尝试使用 SUM(CASE ...) 将其生成为一个查询。

常见问题

继续学习