课程 3.5: SQL 中的条件运算符 CASE WHEN ... THEN ... END

CASE 运算符允许您在查询中直接添加条件逻辑。您可以使用它来分配类别、返回可读标签、根据分支规则过滤数据以及控制自定义排序。当您需要更智能的 SQL 而不将逻辑移动到应用程序代码时，它是最实用的工具之一。

在本课中，我们将涵盖：

• CASE 的工作原理；
• 如何在 SELECT 中使用它；
• 如何在 WHERE 中应用 CASE；
• 如何在 ORDER BY 中使用 CASE 构建自定义排序。

CASE 语法

CASE 有两种主要形式。

1) 简单形式 (simple CASE)

CASE expression
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ELSE default_result
END

这种形式将一个表达式（expression）与多个值进行比较。

2) 搜索形式 (searched CASE)

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

在这里，每个 WHEN 包含一个完整的条件。这种形式更灵活，更常用。

重要行为：

• 条件从上到下进行评估；
• 返回第一个匹配的 WHEN 分支；
• 如果没有条件匹配，则使用 ELSE；
• 如果省略 ELSE，结果为 NULL。

CASE 在 SELECT 中

最常见的用例是添加带有类别或状态标签的计算列。

示例：分类支付

SELECT
    payment_id,
    amount,
    CASE
        WHEN amount < 2 THEN '低支付'
        WHEN amount BETWEEN 2 AND 6 THEN '中等支付'
        ELSE '高支付'
    END AS payment_level
FROM payment
LIMIT 10;

此查询的作用：

• 评估 payment 中每一行的 amount；
• 分配三种类别之一；
• 在名为 payment_level 的新列中返回该类别。

示例：可读的租赁状态

SELECT
    rental_id,
    rental_date,
    return_date,
    CASE
        WHEN return_date IS NULL THEN '未归还'
        ELSE '已归还'
    END AS rental_status
FROM rental
LIMIT 10;

这种方法在报告和仪表板中非常有用，其中原始值应显示为清晰的状态。

CASE 在 WHERE 中

虽然 CASE 最常用于 SELECT 中，但它也可以用于过滤。这在过滤规则依赖于另一列或需要特定分支阈值时非常有用。

示例：每个员工的不同金额阈值

SELECT
    payment_id,
    staff_id,
    amount
FROM payment
WHERE amount >= CASE
    WHEN staff_id = 1 THEN 5
    WHEN staff_id = 2 THEN 3
    ELSE 4
END;

过滤逻辑：

• 对于 staff_id = 1，仅包含 amount >= 5 的支付；
• 对于 staff_id = 2，仅包含 amount >= 3 的支付；
• 对于所有其他员工，阈值为 amount >= 4。

何时替代方案可能更好

对于非常简单的条件，使用 OR 可能更易于阅读。但在 WHERE 中使用 CASE 对于真正分支的业务逻辑非常有帮助，并且应保持在一个表达式中。

CASE 在 ORDER BY 中

一个常见的要求是按业务优先级排序，而不是按字母或数字顺序。CASE 非常适合这一点。

示例：电影评级的自定义排序

SELECT
    title,
    rating
FROM film
ORDER BY CASE rating
    WHEN 'G' THEN 1
    WHEN 'PG' THEN 2
    WHEN 'PG-13' THEN 3
    WHEN 'R' THEN 4
    WHEN 'NC-17' THEN 5
    ELSE 6
END,
 title;

结果： 评级较轻的电影排在前面，然后是更严格的评级，无论默认字符串排序如何。

示例：首先显示未归还的租赁

SELECT
    rental_id,
    rental_date,
    return_date
FROM rental
ORDER BY CASE
    WHEN return_date IS NULL THEN 0
    ELSE 1
END,
 rental_date DESC
LIMIT 20;

这使您可以将最重要的记录放在顶部。

实际应用

1. 按消费进行客户细分：

   SELECT
       customer_id,
       SUM(amount) AS total_spent,
       CASE
           WHEN SUM(amount) < 50 THEN '基础'
           WHEN SUM(amount) < 100 THEN '活跃'
           ELSE 'VIP'
       END AS customer_segment
   FROM payment
   GROUP BY customer_id;
   

2. 按条件组计数行：

   SELECT
       SUM(CASE WHEN amount < 2 THEN 1 ELSE 0 END) AS low_count,
       SUM(CASE WHEN amount BETWEEN 2 AND 6 THEN 1 ELSE 0 END) AS medium_count,
       SUM(CASE WHEN amount > 6 THEN 1 ELSE 0 END) AS high_count
   FROM payment;
   

3. 自定义报告优先级：

   SELECT
       title,
       replacement_cost
   FROM film
   ORDER BY CASE
       WHEN replacement_cost >= 25 THEN 1
       WHEN replacement_cost >= 20 THEN 2
       ELSE 3
   END,
   replacement_cost DESC;
   

本课的关键要点

CASE WHEN ... THEN ... END 是用于条件 SQL 逻辑的通用工具。

关键点：

• 在 SELECT 中，它有助于构建类别和状态；
• 在 WHERE 中，它支持分支过滤逻辑；
• 在 ORDER BY 中，它提供对自定义排序顺序的完全控制；
• 在大多数情况下，包含 ELSE 以避免意外的 NULL 值。

一旦掌握了 CASE，您的 SQL 查询将变得更加灵活、可读，并更接近真实的业务逻辑。