PHP小码农

Appearance

CASE WHEN 表达式 ​

CASE 是 SQL 中唯一的标准条件表达式，用来在查询中实现「分支判断」：把不同条件映射为不同结果。它既能做行级标签（如按价格分档），也能做列级透视（行转列），还能与聚合函数组合实现条件统计。由于 CASE 是表达式而非语句，它可以出现在 SELECT、WHERE、ORDER BY、GROUP BY、UPDATE SET 等任何需要值的位置。

前置知识

阅读本节前，建议先了解：BETWEEN / IN / ANY / ALL

基础概念 ​

概念术语说明
条件表达式CASE Expression按条件返回不同值的表达式，分简单 CASE 与搜索 CASE
简单 CASESimple CASE形式为 CASE expr WHEN v THEN ...，对单一表达式做等值比较
搜索 CASESearched CASE形式为 CASE WHEN condition THEN ...，每个分支可独立写条件
分支WHEN / THEN一个条件与对应结果；自上而下依次判断，命中即返回
兜底分支ELSE所有 WHEN 都不命中时的默认结果；省略则返回 NULL
条件聚合Conditional Aggregation在聚合函数内部用 CASE 只统计满足条件的行
过滤聚合FILTER 子句count(*) FILTER (WHERE ...)，条件聚合的更清晰写法
行转列Pivot用 CASE + 聚合把行数据转成列，实现透视表
分桶Bucketing用 CASE 把连续数值映射到离散区间
惰性求值Lazy Evaluation分支自左至右短路，命中后不再计算后续分支

CASE 的关键特性：

  • 返回单一类型。所有 THEN / ELSE 分支的结果类型必须能归一到同一个类型，否则需要显式转换。
  • 自上而下短路。先命中的分支直接返回，后续分支不再求值，因此分支顺序会影响结果。
  • 没有 ELSE 时默认 NULL。忘记写 ELSE 会静默产出 NULL，是最常见的疏漏。
sql
-- 两种 CASE 形式的最小对照
SELECT
  CASE 2 WHEN 1 THEN '一' WHEN 2 THEN '二' ELSE '其他' END         AS simple_case,   -- 二
  CASE WHEN 2 > 1 THEN '大' WHEN 2 = 1 THEN '等' ELSE '小' END      AS searched_case; -- 大

语法与用法 ​

sql
-- 简单 CASE：对同一个表达式做多次等值比较
CASE 表达式
  WHEN 匹配值1 THEN 结果1
  WHEN 匹配值2 THEN 结果2
  ELSE 默认结果
END

-- 搜索 CASE：每个分支可写独立的布尔条件
CASE
  WHEN 条件1 THEN 结果1
  WHEN 条件2 THEN 结果2
  ELSE 默认结果
END
sql
-- 最小可运行示例
SELECT
  CASE 'a'
    WHEN 'a' THEN '第一名'
    WHEN 'b' THEN '第二名'
    ELSE '未上榜'
  END AS rank_label,
  CASE
    WHEN 95 >= 90 THEN 'A'
    WHEN 95 >= 80 THEN 'B'
    ELSE 'C'
  END AS grade;

详细说明 ​

简单 CASE 与搜索 CASE 的区别 ​

两者最大的区别在于「比较的对象」：

  • 简单 CASE 只做等值比较，无法表达范围或复合条件，且匹配值与表达式都只求值一次。
  • 搜索 CASE 每个分支都能写完整布尔条件，表达力更强。
sql
-- 简单 CASE：只能做等值比较
SELECT sku,
       CASE category
         WHEN '键盘' THEN '输入设备'
         WHEN '鼠标' THEN '输入设备'
         ELSE '其他'
       END AS category_group
FROM products;

-- 搜索 CASE：可以做范围与复合条件
SELECT sku,
       CASE
         WHEN price IS NULL          THEN '待定价'
         WHEN price >= 1000          THEN '高端'
         WHEN price >= 100           THEN '中端'
         ELSE                             '入门'
       END AS price_tier
FROM products;

注意一个陷阱：简单 CASE 的 WHEN NULL 永远不成立，因为它使用 = 比较。要判断 NULL 必须用搜索 CASE。

sql
-- 错误！NULL 不会匹配 WHEN NULL
SELECT CASE price WHEN NULL THEN '无价' ELSE '有价' END FROM products;

-- 正确：改用搜索 CASE + IS NULL
SELECT CASE WHEN price IS NULL THEN '无价' ELSE '有价' END FROM products;

分支顺序决定结果 ​

CASE 自上而下短路求值，因此「宽泛条件写在前面」会吞掉后面更具体的分支。

sql
-- 错误！>= 0 会吞掉所有非负值，>= 100 的分支永远不会命中
SELECT CASE
         WHEN stock >= 0   THEN '有货'
         WHEN stock >= 100 THEN '充足'
         ELSE '缺货'
       END AS stock_label
FROM products;

-- 正确：从最严格的条件开始写
SELECT CASE
         WHEN stock >= 100 THEN '充足'
         WHEN stock >  0   THEN '有货'
         ELSE '缺货'
       END AS stock_label
FROM products;

结果类型统一与类型转换 ​

所有分支必须归一到同一类型。若分支分别是整数与字符串，会报类型错误或发生隐式转换。

sql
-- 错误！分支类型不一致
SELECT CASE WHEN true THEN 1 ELSE 'x' END;

-- 正确：显式转换
SELECT CASE WHEN true THEN 1::text ELSE 'x' END;

-- 数值分支统一精度
SELECT CASE WHEN price >= 1000 THEN price::numeric(10,2) ELSE 0.00 END FROM products;

条件聚合：用 CASE 实现「按条件统计」 ​

把 CASE 放进聚合函数里，就能只统计满足条件的行。这是 CASE 最重要的实战用法之一。

sql
-- 一次查询得到「总数 / 有货数 / 缺货数」
SELECT
  count(*)                                          AS total,
  count(CASE WHEN stock > 0 THEN 1 END)             AS in_stock,
  count(CASE WHEN stock = 0 THEN 1 END)             AS out_of_stock
FROM products;

count(CASE WHEN cond THEN 1 END) 之所以成立，是因为 CASE 在不命中时返回 NULL，而 count(col) 会跳过 NULL。

FILTER 子句：条件聚合的现代写法 ​

PostgreSQL 9.4+ 提供 FILTER (WHERE ...) 子句，专门用于条件聚合，比 CASE 更直观，且通常更快。

sql
-- 等价于上面的 CASE 写法，但更清晰
SELECT
  count(*)                              AS total,
  count(*) FILTER (WHERE stock > 0)     AS in_stock,
  count(*) FILTER (WHERE stock = 0)     AS out_of_stock,
  sum(amount) FILTER (WHERE qty > 1)    AS bulk_amount
FROM orders;

两者的选择建议：只做计数、求和这类标准聚合时优先 FILTER；需要自定义表达式（如 CASE 返回值再参与运算）时用 CASE。

行转列（Pivot） ​

用 CASE + GROUP BY 可以实现透视表：把某一列的不同取值变成多个列。

sql
-- 把订单按商品类别透视成列
SELECT
  date_trunc('month', o.created_at)::date AS month,
  sum(CASE WHEN p.category = '显示器' THEN o.amount ELSE 0 END) AS monitor_amount,
  sum(CASE WHEN p.category = '键盘'   THEN o.amount ELSE 0 END) AS keyboard_amount,
  sum(CASE WHEN p.category = '鼠标'   THEN o.amount ELSE 0 END) AS mouse_amount
FROM orders o
JOIN products p ON p.id = o.product_id
GROUP BY 1
ORDER BY 1;

注意 ELSE 0 而非省略 ELSE：省略会让不匹配的行变成 NULL，sum 忽略 NULL 后结果虽相同，但可读性差且易在 coalesce 之前出现意外。

CASE 用于排序与分组键 ​

CASE 可以直接作为 ORDER BY 与 GROUP BY 的表达式，实现自定义排序或动态分组。

sql
-- 自定义排序：把「缺货」排在最前，其余按价格升序
SELECT sku, name, stock, price
FROM products
ORDER BY
  CASE WHEN stock = 0 THEN 0 ELSE 1 END,
  price;

-- 自定义分组：按价格档位分组统计
SELECT
  CASE
    WHEN price IS NULL THEN '待定价'
    WHEN price >= 1000 THEN '高端'
    WHEN price >= 100  THEN '中端'
    ELSE '入门'
  END AS price_tier,
  count(*) AS cnt
FROM products
GROUP BY 1
ORDER BY 1;

CASE 在 UPDATE 中的批量赋值 ​

CASE 可以让一条 UPDATE 完成「按条件赋不同值」，避免多次往返数据库。

sql
UPDATE products
SET stock = CASE
              WHEN stock = 0 THEN 10
              WHEN stock < 10 THEN stock + 5
              ELSE stock
            END
WHERE id = ANY (ARRAY[1, 2, 3]);

实战示例 ​

步骤 1：建表并造数据 ​

sql
-- 统一的示例库
CREATE DATABASE demo;

DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS products;

CREATE TABLE products (
  id       integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  sku      text    NOT NULL UNIQUE,
  name     text    NOT NULL,
  category text,
  price    numeric(10, 2),
  stock    integer NOT NULL DEFAULT 0
);

CREATE TABLE orders (
  id         integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_no   text NOT NULL,
  product_id integer,
  qty        integer NOT NULL,
  amount     numeric(10, 2),
  status     text,
  created_at timestamp NOT NULL DEFAULT now()
);

INSERT INTO products (sku, name, category, price, stock) VALUES
  ('SKU-1001', '27 英寸显示器', '显示器', 1299.00,  12),
  ('SKU-1002', '机械键盘',       '键盘',    399.00,  30),
  ('SKU-1003', '无线鼠标',       '鼠标',    159.00,   0),
  ('SKU-1004', 'HDMI 线缆',      '线缆',     49.00, 200),
  ('SKU-1005', 'USB 集线器',     '配件',     89.00,   5),
  ('SKU-1006', '显卡支架',       '配件',    129.00,   8),
  ('SKU-1007', '散热硅脂',       '配件',     NULL,     0);

INSERT INTO orders (order_no, product_id, qty, amount, status, created_at) VALUES
  ('SO-2001', 1,    2, 2598.00, 'paid',     '2025-03-05 10:12:00'),
  ('SO-2002', 2,    1,  399.00, 'paid',     '2025-03-18 15:30:00'),
  ('SO-2003', NULL, 1,  159.00, 'pending',  '2025-03-31 23:59:00'),
  ('SO-2004', 3,    3,  477.00, 'shipped',  '2025-04-01 08:00:00'),
  ('SO-2005', NULL, 2,  178.00, 'cancelled','2025-04-20 12:00:00'),
  ('SO-2006', 4,    5,  245.00, 'paid',     '2025-05-02 09:15:00');

步骤 2：搜索 CASE 做分档标签 ​

sql
SELECT sku, name, price,
       CASE
         WHEN price IS NULL THEN '待定价'
         WHEN price >= 1000 THEN '高端'
         WHEN price >= 100  THEN '中端'
         ELSE '入门'
       END AS price_tier
FROM products
ORDER BY sku;
text
   sku    |    name     |  price  | price_tier
----------+-------------+---------+------------
 SKU-1001 | 27 英寸显示器 | 1299.00 | 高端
 SKU-1002 | 机械键盘      |  399.00 | 中端
 SKU-1003 | 无线鼠标      |  159.00 | 中端
 SKU-1004 | HDMI 线缆     |   49.00 | 入门
 SKU-1005 | USB 集线器    |   89.00 | 入门
 SKU-1006 | 显卡支架      |  129.00 | 中端
 SKU-1007 | 散热硅脂      |         | 待定价
(7 rows)

步骤 3：条件聚合统计库存状态 ​

sql
SELECT
  count(*)                                AS total,
  count(*) FILTER (WHERE stock >= 100)    AS ample,
  count(*) FILTER (WHERE stock > 0 AND stock < 100) AS normal,
  count(*) FILTER (WHERE stock = 0)       AS out_of_stock
FROM products;
text
 total | ample | normal | out_of_stock
-------+-------+--------+--------------
     7 |     1 |      4 |            2
(1 row)
sql
-- 用 CASE 写等价查询（兼容不支持 FILTER 的场景）
SELECT
  count(*)                              AS total,
  count(CASE WHEN stock >= 100 THEN 1 END) AS ample,
  count(CASE WHEN stock > 0 AND stock < 100 THEN 1 END) AS normal,
  count(CASE WHEN stock = 0 THEN 1 END) AS out_of_stock
FROM products;
text
 total | ample | normal | out_of_stock
-------+-------+--------+--------------
     7 |     1 |      4 |            2
(1 row)

步骤 4：行转列做订单状态透视 ​

sql
SELECT
  date_trunc('month', created_at)::date AS month,
  sum(CASE WHEN status = 'paid'      THEN amount ELSE 0 END) AS paid_amount,
  sum(CASE WHEN status = 'shipped'   THEN amount ELSE 0 END) AS shipped_amount,
  sum(CASE WHEN status = 'pending'   THEN amount ELSE 0 END) AS pending_amount,
  sum(CASE WHEN status = 'cancelled' THEN amount ELSE 0 END) AS cancelled_amount
FROM orders
GROUP BY 1
ORDER BY 1;
text
   month    | paid_amount | shipped_amount | pending_amount | cancelled_amount
------------+-------------+----------------+----------------+------------------
 2025-03-01 |     2997.00 |           0.00 |         159.00 |             0.00
 2025-04-01 |        0.00 |         477.00 |           0.00 |           178.00
 2025-05-01 |      245.00 |           0.00 |           0.00 |             0.00
(3 rows)

步骤 5：自定义排序与分组 ​

sql
-- 缺货商品排最前，其余按价格升序
SELECT sku, name, stock, price
FROM products
ORDER BY
  CASE WHEN stock = 0 THEN 0 ELSE 1 END,
  price NULLS LAST;
text
   sku    |    name     | stock |  price
----------+-------------+-------+---------
 SKU-1003 | 无线鼠标      |     0 |  159.00
 SKU-1007 | 散热硅脂      |     0 |
 SKU-1004 | HDMI 线缆     |   200 |   49.00
 SKU-1005 | USB 集线器    |     5 |   89.00
 SKU-1006 | 显卡支架      |     8 |  129.00
 SKU-1002 | 机械键盘      |    30 |  399.00
 SKU-1001 | 27 英寸显示器 |    12 | 1299.00
(7 rows)
sql
-- 按价格档位分组统计
SELECT
  CASE
    WHEN price IS NULL THEN '待定价'
    WHEN price >= 1000 THEN '高端'
    WHEN price >= 100  THEN '中端'
    ELSE '入门'
  END AS price_tier,
  count(*)       AS cnt,
  min(price)     AS min_price,
  max(price)     AS max_price
FROM products
GROUP BY 1
ORDER BY 1;
text
 price_tier | cnt | min_price | max_price
------------+-----+-----------+-----------
 中端        |   3 |    129.00 |    399.00
 入门        |   2 |     49.00 |     89.00
 待定价      |   1 |           |
 高端        |   1 |   1299.00 |   1299.00
(4 rows)

步骤 6：CASE 在 UPDATE 中批量赋值 ​

sql
-- 一次 UPDATE 完成不同商品的补货策略
BEGIN;

UPDATE products
SET stock = CASE
              WHEN stock = 0    THEN 10
              WHEN stock < 10   THEN stock + 5
              ELSE stock
            END
WHERE category = '配件';

SELECT sku, name, stock FROM products WHERE category = '配件' ORDER BY sku;

ROLLBACK;   -- 仅演示，回滚以保持数据一致
text
   sku    |    name    | stock
----------+------------+-------
 SKU-1005 | USB 集线器  |    10
 SKU-1006 | 显卡支架    |    13
 SKU-1007 | 散热硅脂    |    10
(3 rows)

注意事项 ​

  • 忘记写 ELSE 会静默返回 NULL。 只要分支可能不覆盖全部输入，就应显式写出 ELSE。
sql
-- 不推荐：不命中时返回 NULL
CASE WHEN price >= 1000 THEN '高端' END
-- 推荐
CASE WHEN price >= 1000 THEN '高端' ELSE '其他' END
  • 简单 CASE 的 WHEN NULL 永远不成立。 它使用等值比较，判 NULL 必须改用搜索 CASE。
sql
-- 错误！
CASE price WHEN NULL THEN '无价' ELSE '有价' END
-- 正确
CASE WHEN price IS NULL THEN '无价' ELSE '有价' END
  • 分支顺序错误会吞掉后续条件。 宽泛条件写在具体条件之前，后面的分支永远不会命中。
sql
-- 错误！>= 0 覆盖了所有非负值
CASE WHEN stock >= 0 THEN '有货' WHEN stock >= 100 THEN '充足' END

  • 所有分支结果类型必须一致。 混用数字与字符串要么报错，要么发生隐式转换导致意外结果。

  • CASE 不是语句，不能用于流程控制。 它只返回值，不能执行 INSERT / UPDATE 等动作；过程化逻辑需用 PL/pgSQL 的 IF。

  • CASE 会阻止部分索引优化。 若在 WHERE 中对列使用 CASE，该列索引通常失效，应改写为 OR 条件。

sql
-- 不推荐：索引失效
WHERE CASE WHEN flag THEN a ELSE b END = 1
-- 推荐：拆成 OR，优化器可分别走索引
WHERE (flag AND a = 1) OR (NOT flag AND b = 1)

  • CASE 中的子查询会按行求值。 在大表上可能造成性能问题，优先用 JOIN 或 LEFT JOIN 代替。

  • count(CASE WHEN ... THEN 1 END) 中的 1 不能省略。 写成 THEN NULL END 就等价于不计数。

最佳实践 ​

  1. 条件聚合优先用 FILTER (WHERE ...)，它比 CASE 更直观，且优化器处理更高效。
sql
SELECT
  count(*) FILTER (WHERE status = 'paid') AS paid_cnt,
  sum(amount) FILTER (WHERE status = 'paid') AS paid_sum
FROM orders;
  1. CASE 分支从最严格写到最宽泛，利用短路求值保证逻辑正确。
sql
CASE
  WHEN price >= 1000 THEN '高端'
  WHEN price >= 100  THEN '中端'
  ELSE '入门'
END

  1. 始终显式写 ELSE，即便是 ELSE NULL，也让意图明确、便于排查。

  2. 行转列时给不匹配分支写 ELSE 0，避免数字列出现 NULL 影响后续算术。

sql
sum(CASE WHEN category = '键盘' THEN amount ELSE 0 END) AS keyboard_amount
  1. 把分档逻辑封装为函数或视图，避免在多个查询里重复同一段 CASE，降低维护成本。
sql
CREATE OR REPLACE FUNCTION price_tier(p numeric)
RETURNS text LANGUAGE sql IMMUTABLE AS $$
  SELECT CASE
           WHEN p IS NULL THEN '待定价'
           WHEN p >= 1000 THEN '高端'
           WHEN p >= 100  THEN '中端'
           ELSE '入门'
         END;
$$;

  1. 用 CASE 实现自定义排序时，ORDER BY 中的表达式要尽量简单，复杂表达式可先在子查询里算好再排序。

  2. 在 WHERE 中避免用 CASE 包裹索引列，改写成 OR 组合，让优化器仍能使用索引。

  3. UPDATE ... SET col = CASE ... 前先 BEGIN，用 SELECT 复核结果再提交，避免批量误改。

sql
BEGIN;
UPDATE products SET stock = CASE WHEN stock = 0 THEN 10 ELSE stock END;
SELECT sku, stock FROM products ORDER BY sku;  -- 复核
ROLLBACK;  -- 确认无误后改为 COMMIT

下一节

继续学习：COALESCE / NULLIF / GREATEST / LEAST

参考链接 ​