📊 SQL 入门 Day 11：CASE 表达式：SQL 里的 if-else 魔法

场景引入：上一节我们学会了用递归 CTE 生成斐波那契数列和遍历树形数据。但很多查询的真实需求是根据不同条件返回不同值——比如把成绩表里的数字分数转成"优秀/良好/及格/不及格"，或者把行数据转成列展示。这时就需要 CASE 表达式登场了。

---

1. 简单 CASE vs 搜索 CASE

CASE 有两种写法，本质都是表达式（返回一个值），不是控制流语句。

1.1 简单 CASE（Simple CASE）

-- 语法：CASE expr WHEN value THEN result [ELSE default] END
-- 相当于 switch(expr) { case value: ... }

SELECT 
    student_name,
    score,
    CASE score / 10                      -- 对 score/10 的整数结果做匹配
        WHEN 10 THEN '满分'
        WHEN 9  THEN '优秀'
        WHEN 8  THEN '良好'
        WHEN 7  THEN '中等'
        WHEN 6  THEN '及格'
        ELSE        '不及格'
    END AS grade_label
FROM (
    VALUES 
        ('张三', 95), ('李四', 82), ('王五', 67), 
        ('赵六', 55), ('钱七', 100)
) AS t(student_name, score);

执行结果：

student_name	score	grade_label
张三	95	优秀
李四	82	良好
王五	67	及格
赵六	55	不及格
钱七	100	满分

⚠️ 局限：简单 CASE 只做等值比较，且 score/10 会被计算一次然后跟 WHEN 值比较。

1.2 搜索 CASE（Searched CASE）

-- 语法：CASE WHEN condition THEN result [ELSE default] END
-- 每个 WHEN 后面可以写任意布尔表达式

SELECT 
    product,
    price,
    CASE 
        WHEN price < 10    THEN '白菜价'
        WHEN price < 50    THEN '实惠'
        WHEN price < 200   THEN '中等'
        WHEN price < 1000  THEN '高端'
        ELSE                    '奢侈品'
    END AS price_level
FROM (
    VALUES 
        ('矿泉水', 2), ('便当', 25), ('机械键盘', 399),
        ('显示器', 2499), ('T恤', 89)
) AS t(product, price);

执行结果：

product	price	price_level
矿泉水	2	白菜价
便当	25	实惠
机械键盘	399	高端
显示器	2499	奢侈品
T恤	89	中等

💡 搜索 CASE 更灵活，可以写范围、子查询、甚至其他 CASE。95% 的场景推荐用搜索 CASE。

---

2. 行转列 Pivot（用 CASE 实现交叉表）

没有 PIVOT 语法时，CASE + GROUP BY 是做行转列的经典方法。

-- 场景：按季度统计每个品类的销售额
-- 原始数据是行式（品类 + 季度 + 销售额），想转为列式表

CREATE TEMP TABLE sales_data AS
SELECT * FROM (VALUES
    ('电子产品', 'Q1', 1200),
    ('电子产品', 'Q2', 1500),
    ('电子产品', 'Q3', 1800),
    ('电子产品', 'Q4', 2100),
    ('服装',     'Q1',  800),
    ('服装',     'Q2',  900),
    ('服装',     'Q3',  750),
    ('服装',     'Q4', 1100),
    ('食品',     'Q1',  500),
    ('食品',     'Q2',  600),
    ('食品',     'Q3',  550),
    ('食品',     'Q4',  700)
) AS t(category, quarter, amount);

-- CASE 行转列
SELECT 
    category,
    SUM(CASE WHEN quarter = 'Q1' THEN amount ELSE 0 END) AS Q1,
    SUM(CASE WHEN quarter = 'Q2' THEN amount ELSE 0 END) AS Q2,
    SUM(CASE WHEN quarter = 'Q3' THEN amount ELSE 0 END) AS Q3,
    SUM(CASE WHEN quarter = 'Q4' THEN amount ELSE 0 END) AS Q4
FROM sales_data
GROUP BY category
ORDER BY category;

执行结果：

category	Q1	Q2	Q3	Q4
服装	800	900	750	1100
食品	500	600	550	700
电子产品	1200	1500	1800	2100

核心思路：SUM(CASE WHEN ...) 把满足条件的行"投"到对应的列。

---

3. 条件聚合（CASE 套聚合函数）

除了行转列，CASE 在聚合里还能做更多筛选统计，一次查询出多个指标。

-- 统计每门课程的：总人数、及格人数、优秀人数、及格率、优秀率
SELECT 
    course,
    COUNT(*)                                         AS total_students,
    COUNT(CASE WHEN score >= 60  THEN 1 END)         AS passed,
    COUNT(CASE WHEN score >= 90  THEN 1 END)         AS excellent,
    ROUND(
        COUNT(CASE WHEN score >= 60  THEN 1 END)::numeric 
        / COUNT(*) * 100, 1
    )                                                AS pass_rate_pct,
    ROUND(
        COUNT(CASE WHEN score >= 90  THEN 1 END)::numeric 
        / COUNT(*) * 100, 1
    )                                                AS excellent_rate_pct
FROM (
    VALUES 
        ('数学', 88), ('数学', 92), ('数学', 55), ('数学', 73), ('数学', 100),
        ('英语', 65), ('英语', 78), ('英语', 82), ('英语', 45), ('英语', 90),
        ('物理', 95), ('物理', 88), ('物理', 76), ('物理', 69), ('物理', 58)
) AS t(course, score)
GROUP BY course;

执行结果：

course	total_students	passed	excellent	pass_rate_pct	excellent_rate_pct
数学	5	4	2	80.0	40.0
英语	5	4	1	80.0	20.0
物理	5	4	1	80.0	20.0

💡 条件聚合 避免了多次扫描表，性能远优于用多个子查询分别统计。

---

4. CASE 在 ORDER BY / GROUP BY 中的妙用

4.1 自定义排序规则

-- 让 "待处理" 排最前面，"处理中" 第二，"已完成" 最后
SELECT task_id, title, status
FROM (
    VALUES 
        (1, '修复登录Bug',    '已完成'),
        (2, '优化首页性能',  '待处理'),
        (3, '更新接口文档',  '处理中'),
        (4, '数据库备份',    '已完成'),
        (5, '修复支付超时',  '待处理')
) AS t(task_id, title, status)
ORDER BY 
    CASE status
        WHEN '待处理' THEN 1
        WHEN '处理中' THEN 2
        WHEN '已完成' THEN 3
        ELSE 99
    END,
    task_id;

执行结果：（待处理→处理中→已完成）

task_id	title	status
2	优化首页性能	待处理
5	修复支付超时	待处理
3	更新接口文档	处理中
1	修复登录Bug	已完成
4	数据库备份	已完成

4.2 CASE 在 GROUP BY 中做分桶

-- 按价格区间分组统计商品数量
SELECT 
    CASE 
        WHEN price < 50    THEN '低价 (<50)'
        WHEN price < 200   THEN '中价 (50-200)'
        ELSE                    '高价 (>200)'
    END AS price_bucket,
    COUNT(*) AS product_count,
    ROUND(AVG(price), 2) AS avg_price
FROM (
    VALUES 
        (1, 15), (2, 30), (3, 99), (4, 180),
        (5, 250), (6, 500), (7, 45), (8, 120)
) AS t(id, price)
GROUP BY 
    CASE 
        WHEN price < 50    THEN '低价 (<50)'
        WHEN price < 200   THEN '中价 (50-200)'
        ELSE                    '高价 (>200)'
    END
ORDER BY MIN(price);  -- 按价格排序展示

执行结果：

price_bucket	product_count	avg_price
低价 (<50)	3	30.00
中价 (50-200)	3	133.00
高价 (>200)	2	375.00

⚠️ GROUP BY 里的 CASE 表达式必须和 SELECT 中的完全一致（或使用位置编号），否则会语法报错。

---

5. 嵌套 CASE 与性能考量

CASE 可以嵌套，但过度嵌套会降低可读性。

-- 嵌套 CASE 示例（不太推荐）
SELECT 
    score,
    CASE 
        WHEN score >= 60 THEN
            CASE 
                WHEN score >= 90 THEN '优秀'
                ELSE '合格'
            END
        ELSE '不合格'
    END AS result
FROM (VALUES (55), (75), (95)) AS t(score);

💡 性能建议：

• CASE 是表达式，不是语句，不会导致短路优化问题（标准 SQL 要求只有第一个匹配的 WHEN 被执行）
• 嵌套不超过 2~3 层，再多就拆成子查询或用 CTE 明确
• 相比同功能的 COALESCE/NULLIF，CASE 通常更通用但代码更长
• 在 WHERE 里用 CASE 通常不如直接写 OR/AND 高效

---

6. COALESCE / NULLIF 与 CASE 的关系

这两个函数其实就是 CASE 的语法糖。

COALESCE → CASE

-- 以下等价
SELECT COALESCE(nickname, full_name, '匿名用户') AS display_name FROM users;

SELECT CASE 
    WHEN nickname IS NOT NULL THEN nickname
    WHEN full_name IS NOT NULL THEN full_name
    ELSE '匿名用户'
END AS display_name FROM users;

NULLIF → CASE

-- 如果 a = b 返回 NULL，否则返回 a
SELECT NULLIF(a, b) FROM t;

-- 等价于
SELECT CASE WHEN a = b THEN NULL ELSE a END FROM t;

实际代码里直接使用 COALESCE/NULLIF 更简洁，了解它们与 CASE 的关系有助于理解底层逻辑。

---

7. 注意事项 ⚠️

7.1 ELSE 的重要性

-- 没有 ELSE 时，不满足任何 WHEN 返回 NULL
SELECT 
    score,
    CASE 
        WHEN score >= 60 THEN '及格'
        -- 没有 ELSE！不及格的人会得到 NULL
    END AS result
FROM (VALUES (55), (75)) AS t(score);
-- 结果：75 → '及格', 55 → NULL（而不是 '不及格'）

✅ 黄金法则：除非你明确想要 NULL，否则永远写 ELSE。

7.2 类型一致性

CASE 所有分支的返回值类型必须兼容：

-- ❌ 错误：字符串和数字混用
SELECT CASE WHEN 1=1 THEN 'yes' ELSE 42 END;

-- ✅ 正确：统一类型
SELECT CASE WHEN 1=1 THEN 'yes' ELSE '42' END;

PostgreSQL 会尝试隐式转换，但最好显式保持类型一致。

7.3 执行顺序

CASE 按从上到下的顺序匹配，第一个匹配的 WHEN 生效后不再继续判断：

-- 注意顺序！如果先写 price < 200，`price < 50` 的条件永远走不到
SELECT 
    price,
    CASE 
        WHEN price < 200  THEN '中等'  -- 先匹配了这个
        WHEN price < 50   THEN '低价'  -- 永远不会走到这里
        ELSE '高价'
    END AS level
FROM (VALUES (30), (150)) AS t(price);

✅ 正确做法：从最严格的条件写到最宽松的条件（窄→宽）。

---

8. 综合实战：月度销售仪表盘

-- 统计 2026 年上半年各品类月度销售趋势（模拟数据）
WITH monthly_sales AS (
    SELECT * FROM (VALUES
        ('数码', '2026-01', 120), ('数码', '2026-02', 135), ('数码', '2026-03', 110),
        ('数码', '2026-04', 150), ('数码', '2026-05', 170), ('数码', '2026-06', 190),
        ('服饰', '2026-01', 200), ('服饰', '2026-02', 180), ('服饰', '2026-03', 220),
        ('服饰', '2026-04', 190), ('服饰', '2026-05', 210), ('服饰', '2026-06', 230),
        ('食品', '2026-01', 300), ('食品', '2026-02', 280), ('食品', '2026-03', 310),
        ('食品', '2026-04', 290), ('食品', '2026-05', 320), ('食品', '2026-06', 350)
    ) AS t(category, month, amount)
)
SELECT 
    category,
    SUM(CASE WHEN RIGHT(month, 2) = '01' THEN amount ELSE 0 END) AS jan,
    SUM(CASE WHEN RIGHT(month, 2) = '02' THEN amount ELSE 0 END) AS feb,
    SUM(CASE WHEN RIGHT(month, 2) = '03' THEN amount ELSE 0 END) AS mar,
    SUM(CASE WHEN RIGHT(month, 2) = '04' THEN amount ELSE 0 END) AS apr,
    SUM(CASE WHEN RIGHT(month, 2) = '05' THEN amount ELSE 0 END) AS may,
    SUM(CASE WHEN RIGHT(month, 2) = '06' THEN amount ELSE 0 END) AS jun,
    SUM(amount) AS total,
    ROUND(AVG(amount), 1) AS avg_monthly,
    -- 趋势判断
    CASE 
        WHEN SUM(amount) > 0 
         AND SUM(CASE WHEN RIGHT(month, 2) = '06' THEN amount ELSE 0 END) >
              SUM(CASE WHEN RIGHT(month, 2) = '01' THEN amount ELSE 0 END)
        THEN '📈 增长'
        WHEN SUM(amount) > 0
         AND SUM(CASE WHEN RIGHT(month, 2) = '06' THEN amount ELSE 0 END) =
              SUM(CASE WHEN RIGHT(month, 2) = '01' THEN amount ELSE 0 END)
        THEN '➡️ 持平'
        ELSE '📉 下降'
    END AS trend
FROM monthly_sales
GROUP BY category
ORDER BY total DESC;

执行结果：

category	jan	feb	mar	apr	may	jun	total	avg_monthly	trend
食品	300	280	310	290	320	350	1850	308.3	📈 增长
服饰	200	180	220	190	210	230	1230	205.0	📈 增长
数码	120	135	110	150	170	190	875	145.8	📈 增长

---

今日总结

知识点	要点
简单 CASE	等值匹配，类似 switch
搜索 CASE	任意条件，推荐使用
行转列	SUM(CASE WHEN ...) 是标准 Pivot 手法
条件聚合	单次扫描出多个统计指标，比子查询高效
ORDER BY 妙用	CASE 可控制排序优先级
GROUP BY 分桶	用 CASE 将连续值映射到离散分组
COALESCE/NULLIF	本质是 CASE 的语法糖
ELSE 很重要	忘写 ELSE = 意外 NULL
类型一致	所有 THEN/ELSE 需同类型
执行顺序	窄条件在前，宽条件在后