PHP小码农

Appearance

CASE WHEN（函数式用法） ​

CASE 是 SQL 标准里唯一的条件表达式（不是语句），因此它可以出现在任何"需要值"的位置：SELECT 列表、WHERE、ORDER BY、GROUP BY、UPDATE SET、DEFAULT、CHECK、CREATE INDEX 的表达式里，甚至函数体内部。本节把它当作一个多分支函数来看待——关注它的返回类型统一规则、与其他条件函数（COALESCE / GREATEST / LEAST）的等价替换关系、在 DDL 与索引中的落地方式，以及条件聚合的两种写法如何取舍。

与「CASE WHEN 表达式」运算符章节的分工

CASE WHEN 表达式 一章从查询写法出发，重点讲"两种 CASE 形式、分组、排序怎么用"；本节从函数视角出发，重点讲把 CASE 当成多分支函数时的类型推导、用 COALESCE / GREATEST / LEAST 简化 CASE 的对照、在 DEFAULT / CHECK / 生成列 / 表达式索引 / 函数体里的用法，以及条件聚合的写法取舍。两节互补。

前置知识

阅读本节前，建议先了解：GREATEST / LEAST

基础概念 ​

概念术语说明
条件表达式CASE Expression按条件返回不同值，唯一进入 SQL 标准的条件表达式
简单 CASESimple CASECASE expr WHEN v THEN ...，对单一表达式做等值比较
搜索 CASESearched CASECASE WHEN cond THEN ...，每个分支可独立写条件
分支WHEN / THEN条件与结果对，自左向右短路，命中即返回
兜底分支ELSE全部 WHEN 不命中时的结果，省略则返回 NULL
结果类型统一Result Type Unification所有 THEN / ELSE 结果归约到同一类型
多分支函数Multi-Branch Function把 CASE 当作"输入条件、输出值"的函数来看待
条件聚合Conditional Aggregation在聚合函数内部用 CASE 只统计命中行
行转列PivotCASE + 聚合把行的取值变成列
表达式固化Expression Freezing把 CASE 写进生成列 / 索引 / 函数，一次定义多处复用

CASE 作为"函数"的三个关键性质：

  1. 返回单一类型——所有 THEN / ELSE 的结果必须能归约到同一类型，否则报错；
  2. 自左向右短路——命中一个分支后，后续分支不再求值；
  3. 可出现在任何值的位置——包括 DEFAULT、CHECK、生成列、索引表达式、函数体。

最小对照：

sql
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; -- 大
text
 simple_case | searched_case
-------------+---------------
 二           | 大
(1 row)

语法与用法 ​

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

-- 搜索形态：每个分支独立写布尔条件
CASE
  WHEN 条件1 THEN 结果1
  WHEN 条件2 THEN 结果2
  ELSE 默认结果
END

两条硬规则：

  1. 分支自上而下短路，顺序直接影响结果——宽泛条件写在前面会吞掉后面更具体的分支；
  2. 结果的公共类型即整体类型，与 COALESCE / GREATEST 的类型归约规则一致。
sql
-- 标准形态
SELECT
  CASE status
    WHEN 'paid'    THEN '已支付'
    WHEN 'shipped' THEN '已发货'
    ELSE '其他'
  END AS status_cn
FROM orders;

最小可运行示例，覆盖返回值边界：

sql
SELECT
  CASE WHEN false THEN 'a' END              AS no_else,      -- NULL（省略 ELSE）
  CASE WHEN true  THEN NULL ELSE 'b' END    AS then_null,    -- NULL
  CASE 1 WHEN 1 THEN 'hit' ELSE 'miss' END  AS simple_hit,   -- hit
  CASE WHEN NULL THEN 'a' ELSE 'b' END      AS null_cond;    -- b（条件为 NULL 视为不成立）
text
 no_else | then_null | simple_hit | null_cond
---------+-----------+------------+-----------
         |           | hit        | b
(1 row)

详细说明 ​

CASE 是一个"多分支函数" ​

把 CASE 写成函数视角，它的签名可以概括为：

text
CASE (条件1, 结果1, 条件2, 结果2, ..., [默认结果])
   → 返回第一个命中条件对应的结果，全不命中则返回默认结果（或 NULL）

它的三个参数类别与一个普通函数完全同构：

函数要素CASE 中的对应
输入WHEN 后的布尔条件
输出候选THEN / ELSE 后的结果表达式
返回类型所有输出候选的公共类型
求值顺序自左向右，短路
默认值ELSE（缺省为 NULL）

这个视角的价值在于：凡是"输入→输出"的映射逻辑，都可以封装成一个 CASE，并进一步固化为函数或生成列。

sql
-- 把分档逻辑当作函数看待
SELECT
  sku,
  CASE
    WHEN price IS NULL THEN '待定价'
    WHEN price >= 1000 THEN '高端'
    WHEN price >= 100  THEN '中端'
    ELSE '入门'
  END AS tier
FROM products
ORDER BY sku;

结果类型统一规则 ​

这是 CASE 作为函数最容易出错的地方，规则与 COALESCE 完全一致：

text
1. 收集所有 THEN / ELSE 结果的类型；
2. unknown 字面量先不参与候选；
3. 在已知类型中选出公共类型，其余结果统一转换到该类型；
   若无法归约 → 报错。
条件分支组合推导结果
全为整数THEN 1 ELSE 2integer
整数 + 小数THEN 1 ELSE 2.5numeric
整数 + 字符串字面量THEN 1 ELSE 'x'报错（'x' 归约到 integer 失败）
整数 + 布尔THEN 1 ELSE true报错（跨类型类别）
NULL + 字符串THEN NULL ELSE 'x'text
sql
-- 错误！分支类型不一致
SELECT CASE WHEN true THEN 1 ELSE 'x' END;
text
ERROR:  invalid input syntax for type integer: "x"
LINE 1: SELECT CASE WHEN true THEN 1 ELSE 'x' END;
sql
-- 错误！跨类型类别
SELECT CASE WHEN true THEN 1 ELSE true END;
text
ERROR:  CASE types integer and boolean cannot be matched
LINE 1: SELECT CASE WHEN true THEN 1 ELSE true END;

正确做法是显式转型，把意图写清：

sql
SELECT
  CASE WHEN true THEN 1::text ELSE 'x' END          AS unified_text,   -- 1
  CASE WHEN true THEN 1 ELSE 2.5 END                AS unified_numeric; -- 1.0

验证整体类型：

sql
SELECT
  pg_typeof(CASE WHEN true THEN 1 ELSE 2.5 END)     AS numeric_type,
  pg_typeof(CASE WHEN true THEN NULL ELSE 'x' END)  AS text_type;
text
 numeric_type | text_type
--------------+-----------
 numeric      | text
(1 row)

用 CASE 替换（或被替换为）其他条件函数 ​

CASE 是万能的条件表达，但很多场景用专用函数更短、更快、更不容易写错。判断依据很简单：能用专用函数的，就不要用 CASE。

需求CASE 写法专用函数推荐
NULL 兜底CASE WHEN a IS NOT NULL THEN a ELSE b ENDcoalesce(a, b)函数
多级兜底多层嵌套 CASEcoalesce(a, b, c, '默认')函数
取较大值CASE WHEN a > b THEN a ELSE b ENDgreatest(a, b)函数
取较小值CASE WHEN a < b THEN a ELSE b ENDleast(a, b)函数
夹取区间CASE WHEN x < 0 THEN 0 WHEN x > 100 THEN 100 ELSE x ENDleast(greatest(x, 0), 100)函数
相等转 NULLCASE WHEN a = b THEN NULL ELSE a ENDnullif(a, b)函数
分段映射CASE WHEN ... THEN ...无专用函数CASE
等值多分支映射CASE col WHEN ... THEN ...无专用函数CASE
布尔转文本CASE WHEN flag THEN '是' ELSE '否' ENDCASE（或 format/iif 无标准）CASE
sql
-- 冗长写法
SELECT CASE WHEN a IS NOT NULL THEN a WHEN b IS NOT NULL THEN b ELSE '默认' END FROM t;
-- 简洁写法（等价且更清晰）
SELECT coalesce(a, b, '默认') FROM t;

反向也成立：专用函数无法表达"分段映射"，这时只能回到 CASE：

sql
-- 分段映射没有对应的专用函数
SELECT CASE
         WHEN age < 18 THEN '未成年'
         WHEN age < 60 THEN '成年'
         ELSE '老年'
       END AS age_group
FROM users;

把 CASE 固化为函数 ​

CASE 一旦在多个查询里重复出现，就应该抽成 IMMUTABLE 的 SQL 函数——这正是"把多分支逻辑当函数"的直接落地：

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;
$$;
text
CREATE FUNCTION

抽成函数后有三个好处：

  1. 避免各查询各写一套，分档标准只维护一处；
  2. IMMUTABLE 标记让它可用于生成列与表达式索引（见下节）；
  3. 可以给函数起有业务含义的名字，查询可读性更高。
sql
SELECT sku, price, price_tier(price) AS tier FROM products ORDER BY sku;

在 DEFAULT 与 CHECK 约束中的用法 ​

CASE 可以用在列默认值与检查约束里，但受同样的限制。

sql
CREATE TABLE tickets (
  id       bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  priority smallint NOT NULL DEFAULT 3,
  level    text     NOT NULL
             DEFAULT CASE WHEN current_setting('app.env', true) = 'prod' THEN 'normal' ELSE 'debug' END,
  score    integer  NOT NULL,
  CONSTRAINT score_in_range CHECK (score = least(greatest(score, 0), 100)),
  CONSTRAINT priority_valid  CHECK (priority IN (1, 2, 3))
);
text
CREATE TABLE

DEFAULT 不能引用其他列

DEFAULT CASE WHEN priority = 1 THEN ... END 是非法的——默认值表达式只能引用常量、current_setting、now() 等，不能引用同行的其他列。要表达"根据同行其他列推导"，必须用生成列。

在生成列中的用法 ​

生成列（GENERATED ALWAYS AS ... STORED）是"把 CASE 固化为列"的手段，它相当于在表定义里放了一个恒定的派生表达式：

sql
ALTER TABLE tickets
  ADD COLUMN urgency text
  GENERATED ALWAYS AS (
    CASE
      WHEN priority = 1 THEN '紧急'
      WHEN priority = 2 THEN '较急'
      ELSE '一般'
    END
  ) STORED;
text
ALTER TABLE

生成列的好处是自动维护（插入/更新时由数据库计算，不需要应用层参与），代价是占磁盘且表达式必须 IMMUTABLE。

sql
INSERT INTO tickets (priority, score) VALUES (1, 95), (2, 60), (3, 120);
text
ERROR:  new row for relation "tickets" violates check constraint "score_in_range"
DETAIL:  Failing row contains (3, 3, normal, 120, 一般).

第 3 行的 score = 120 被 CHECK 拒绝，整条 INSERT 失败（PostgreSQL 的约束是行级即时报错）。把 120 改成 100 才能插入：

sql
INSERT INTO tickets (priority, score) VALUES (1, 95), (2, 60), (3, 100);
SELECT id, priority, score, urgency FROM tickets ORDER BY id;
text
 id | priority | score | urgency
----+----------+-------+---------
  1 |        1 |    95 | 紧急
  2 |        2 |    60 | 较急
  3 |        3 |   100 | 一般
(3 rows)

在表达式索引与部分索引中的用法 ​

CASE 是 IMMUTABLE 表达式时可以进索引；但更常见、更有价值的是用布尔表达式（而非 CASE）来写部分索引的条件：

sql
-- 只索引「待处理」的订单，索引体积小、命中快
CREATE INDEX idx_orders_pending
  ON orders (created_at)
  WHERE status = 'pending';

把 CASE 直接写进 WHERE 虽然语法上可行，但可读性与优化器可用性都不如布尔表达式，仅当条件本身需要分支映射时才用：

sql
-- 等价写法：把 NULL 视为 pending 后再比较
CREATE INDEX idx_orders_pending_v2
  ON orders (created_at)
  WHERE coalesce(status, 'pending') = 'pending';
sql
-- 表达式索引：把分档结果当作检索键
CREATE INDEX idx_products_tier ON products (price_tier(price));

-- 查询必须写成与索引表达式一致的形式才能命中
EXPLAIN (COSTS OFF)
SELECT sku FROM products WHERE price_tier(price) = '高端';
text
                                     QUERY PLAN
-------------------------------------------------------------------------------------
 Index Scan using idx_products_tier on products
   Index Cond: (price_tier(price) = '高端'::text)
(2 rows)

WHERE 里包裹索引列会让普通索引失效

WHERE CASE WHEN flag THEN a ELSE b END = 1 无法使用 a / b 上的普通索引。要保留索引能力，改写为等价的 OR：

sql
-- 不推荐
WHERE CASE WHEN flag THEN a ELSE b END = 1
-- 推荐：两个分支各自可走索引
WHERE (flag AND a = 1) OR (NOT flag AND b = 1)

条件聚合：CASE 与 FILTER 的取舍 ​

把 CASE 放进聚合函数，是"按条件统计"的经典手段；FILTER (WHERE ...) 是它的现代替代。二者在结果上等价，差异在可读性与适用面：

维度count(CASE WHEN cond THEN 1 END)count(*) FILTER (WHERE cond)
引入版本一直可用PostgreSQL 9.4+
可读性需要理解"不命中返回 NULL"条件直观
适用聚合任意聚合（sum / avg / array_agg…）任意聚合
能带 ORDER BY可以（写在聚合参数里）不能（FILTER 与 ORDER BY 不能同时用）
需要自定义表达式可以（THEN amount * rate）可以，但表达式要写进聚合参数
跨数据库基本通用PostgreSQL 特有扩展
sql
-- CASE 写法
SELECT
  count(*)                                 AS total,
  count(CASE WHEN status = 'paid' THEN 1 END)    AS paid_cnt,
  sum(CASE WHEN status = 'paid' THEN amount ELSE 0 END) AS paid_sum
FROM orders;

-- FILTER 写法（更清晰）
SELECT
  count(*)                                AS total,
  count(*) FILTER (WHERE status = 'paid') AS paid_cnt,
  sum(amount) FILTER (WHERE status = 'paid') AS paid_sum
FROM orders;

两者结果完全一致：

text
 total | paid_cnt | paid_sum
-------+----------+----------
     6 |        3 |  3242.00
(1 row)

但当聚合需要 ORDER BY 时，FILTER 就用不了了，只能回到 CASE：

sql
-- FILTER 无法表达「按条件筛选 + 指定顺序」的聚合
SELECT
  coalesce(
    string_agg(CASE WHEN status = 'paid' THEN order_no END, ',' ORDER BY created_at),
    ''
  ) AS paid_order_nos
FROM orders;

行转列（Pivot） ​

把某一列的不同取值变成多个列，是 CASE + 聚合最典型的应用。关键点是给不匹配分支写 ELSE 0，避免算术列出现 NULL：

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;

更灵活的 Pivot

CASE 写死的列数量是固定的。需要动态列时可用 crosstab()（来自 tablefunc 扩展）或让应用层拼 SQL。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 NULLS LAST;

-- 自定义分组：按分档结果分组统计
SELECT
  price_tier(price) AS tier,
  count(*)          AS cnt
FROM products
GROUP BY 1
ORDER BY 1;

在函数体中的用法 ​

CASE 既能作为 SQL 函数体，也能嵌入 PL/pgSQL 作为表达式（但不能当作流程控制语句——那是 IF 的职责）：

sql
-- SQL 函数体：CASE 作为整个返回表达式
CREATE OR REPLACE FUNCTION shipping_fee(amount numeric)
RETURNS numeric
LANGUAGE sql IMMUTABLE AS $$
  SELECT CASE
           WHEN amount IS NULL THEN 0
           WHEN amount >= 200  THEN 0
           WHEN amount >= 100  THEN 6
           ELSE 12
         END;
$$;
sql
-- PL/pgSQL 函数体：CASE 仍是表达式，不是控制流
CREATE OR REPLACE FUNCTION shipping_fee_v2(amount numeric)
RETURNS numeric
LANGUAGE plpgsql IMMUTABLE AS $$
DECLARE
  fee numeric;
BEGIN
  fee := CASE
           WHEN amount IS NULL THEN 0
           WHEN amount >= 200  THEN 0
           WHEN amount >= 100  THEN 6
           ELSE 12
         END;
  RETURN fee;
END;
$$;

CASE 不是语句

CASE 只返回值，不能执行 INSERT / UPDATE。过程化分支要用 PL/pgSQL 的 IF ... THEN ... ELSIF ... ELSE ... END IF;：

sql
IF amount >= 200 THEN
  fee := 0;
ELSIF amount >= 100 THEN
  fee := 6;
ELSE
  fee := 12;
END IF;

实战示例 ​

场景：订单分档标签 + 条件聚合 + 行转列 + 生成列固化。

步骤 1：建表并造数据

sql
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-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');
text
CREATE TABLE
CREATE TABLE
INSERT 0 6
INSERT 0 6

步骤 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-1007 | 散热硅脂      |         | 待定价
(6 rows)

步骤 3：类型统一失败的复现与修正

sql
-- 错误！分支类型不一致
SELECT CASE WHEN true THEN 1 ELSE 'x' END;
text
ERROR:  invalid input syntax for type integer: "x"
LINE 1: SELECT CASE WHEN true THEN 1 ELSE 'x' END;
sql
-- 正确：显式转型
SELECT CASE WHEN true THEN 1::text ELSE 'x' END AS unified;
text
 unified
---------
 1
(1 row)

步骤 4：条件聚合（CASE 与 FILTER 对照）

sql
SELECT
  count(*)                                        AS total,
  count(CASE WHEN status = 'paid' THEN 1 END)     AS paid_c,
  count(*) FILTER (WHERE status = 'paid')         AS paid_f,
  sum(CASE WHEN status = 'paid' THEN amount ELSE 0 END) AS paid_sum_c,
  sum(amount) FILTER (WHERE status = 'paid')      AS paid_sum_f
FROM orders;
text
 total | paid_c | paid_f | paid_sum_c | paid_sum_f
-------+--------+--------+------------+------------
     6 |      3 |      3 |    3242.00 |    3242.00
(1 row)

两列完全一致，验证了两种写法等价。

步骤 5：行转列 Pivot

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)

步骤 6：把 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;
$$;

-- 用函数作为生成列表达式
ALTER TABLE products
  ADD COLUMN tier text GENERATED ALWAYS AS (price_tier(price)) STORED;

SELECT sku, price, tier, price_tier(price) AS same_value FROM products ORDER BY sku;
text
   sku    |  price  |  tier  | same_value
----------+---------+--------+------------
 SKU-1001 | 1299.00 | 高端    | 高端
 SKU-1002 |  399.00 | 中端    | 中端
 SKU-1003 |  159.00 | 中端    | 中端
 SKU-1004 |   49.00 | 入门    | 入门
 SKU-1005 |   89.00 | 入门    | 入门
 SKU-1007 |         | 待定价  | 待定价
(6 rows)

生成列的值与函数调用结果一致，证明它确实被固化在表里。

步骤 7：表达式索引验证

sql
CREATE INDEX idx_products_tier ON products (tier);
ANALYZE products;

EXPLAIN (COSTS OFF)
SELECT sku FROM products WHERE tier = '高端';
text
                                QUERY PLAN
---------------------------------------------------------------------------
 Index Scan using idx_products_tier on products
   Index Cond: (tier = '高端'::text)
(2 rows)

步骤 8：清理

sql
DROP FUNCTION IF EXISTS price_tier(numeric);
DROP TABLE orders;
DROP TABLE products;

注意事项 ​

  • 忘记写 ELSE 会静默返回 NULL。只要分支不覆盖全部输入，就应显式写 ELSE：
sql
-- 不推荐
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
-- 错误！
CASE WHEN true THEN 1 ELSE 'x' END
  • 分支顺序错误会吞掉后续条件。宽泛条件写在前面，后面更具体的分支永不命中：
sql
-- 错误！>= 0 覆盖了所有非负值
CASE WHEN stock >= 0 THEN '有货' WHEN stock >= 100 THEN '充足' END

  • CASE 不是语句，不能执行 INSERT / UPDATE；过程化分支要用 PL/pgSQL 的 IF。

  • DEFAULT 表达式不能引用同行的其他列。想按同行列推导，必须用生成列。

  • WHERE 中包裹索引列会让普通索引失效，应改写为等价的 OR 组合。

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

  • FILTER 与聚合内的 ORDER BY 不能同时使用，需要指定顺序时回退到 CASE 写法。

  • CASE 中的子查询按行求值，大表上可能很慢，优先改用 JOIN / LEFT JOIN 或 LATERAL。

最佳实践 ​

  1. 能用专用函数就不用 CASE——coalesce / nullif / greatest / least 更短、更快、意图更明确：

    sql
    -- 不推荐
CASE WHEN a IS NOT NULL THEN a ELSE b END
-- 推荐
coalesce(a, b)

  2. 分档 / 映射逻辑抽成 IMMUTABLE 函数，一处维护，多处复用，并可用于生成列与索引：

    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;
$$;

  3. 条件聚合优先用 FILTER (WHERE ...)，可读性更好；需要排序或自定义表达式时用 CASE。

  4. CASE 分支从最严格写到最宽泛，利用短路求值保证逻辑正确。

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

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

  7. 分档结果若需长期检索，直接固化为生成列，比每次查询都算一遍更省 CPU：

    sql
    ALTER TABLE products ADD COLUMN tier text
  GENERATED ALWAYS AS (price_tier(price)) STORED;

  8. UPDATE ... SET col = CASE ... 前先 BEGIN 并 SELECT 复核，确认无误再 COMMIT。

下一节

继续学习：NVL / IFNULL 兼容

参考链接 ​