PostgreSQL根据inp字段条件分支编写SELECT查询的实现方法

现有定义CTE测试数据集的SQL如下：

WITH final AS (
    SELECT 'a' AS gname, 'b' AS pname, 'c' AS name, 1 AS gid, 2 AS pid, 3 AS did, 3 AS inp
    UNION
    SELECT 'a1' AS gname, 'b1' AS pname, 'c1' AS name, 11 AS gid, 21 AS pid, 31 AS did, 3 AS inp
)

需求为根据inp列的取值走不同的查询逻辑：

• 当inp = 1时：查询去重后的gid作为id、gname作为name，结果按gname字段排序
• 当inp = 2时：筛选gid等于传入参数$1的记录，查询去重后的pid作为id、pname作为name，结果按pname字段排序
• 当inp = 3时：筛选pid等于传入参数$1的记录，查询去重后的did作为id、name字段作为name，结果按name字段排序

最初尝试的写法不符合PostgreSQL语法规范，核心问题是CASE表达式无法返回多行多列的结果集，无法实现子查询关联外层字段、参数过滤、排序的逻辑。

原有写法不成立的核心原因有两个：一是PostgreSQL的CASE表达式每个分支只能返回单个标量值，不能返回多行多列的结果集；二是标量子查询如果返回多行也会直接报错。根据常见的两种业务场景，给出可直接运行的写法：

场景1：按全表inp值分分支，返回对应维度的去重结果集 #

这个场景对应最初写法的逻辑：全表扫描时根据inp的取值走不同的查询分支，支持传入参数过滤、按分支要求排序，用UNION ALL拼接独立分支查询是最直观、性能最好的写法：

-- $1为传入的过滤参数
SELECT id, name
FROM (
    -- inp=1 分支：返回去重的gid、gname，按gname排序
    SELECT DISTINCT gid AS id, gname AS name, gname AS sort_key
    FROM final
    WHERE inp = 1

    UNION ALL

    -- inp=2 分支：过滤gid=$1，返回去重的pid、pname，按pname排序
    SELECT DISTINCT pid AS id, pname AS name, pname AS sort_key
    FROM final
    WHERE inp = 2 AND gid = $1

    UNION ALL

    -- inp=3 分支：过滤pid=$1，返回去重的did、name，按name排序
    SELECT DISTINCT did AS id, name, name AS sort_key
    FROM final
    WHERE inp = 3 AND pid = $1
) res
ORDER BY sort_key;

这种写法的优势：

• 每个分支独立编写过滤、去重、排序逻辑，不存在跨层关联的问题，参数可以直接传入使用
• 执行计划清晰，PostgreSQL可以分别对每个分支做索引优化，性能远高于嵌套关联子查询
• 返回标准的结构化结果，不需要额外做类型转换，适配所有下游数据消费场景

场景2：逐行根据inp值，返回该行对应维度的id和name #

如果你需要的是对final表的每一行，根据该行自身的inp值取对应的id、name字段，不需要返回多行集合，直接写两个CASE表达式取字段即可，排序逻辑也可以通过CASE表达式动态指定排序字段：

-- $1为传入的过滤参数
SELECT
    CASE inp
        WHEN 1 THEN gid
        WHEN 2 THEN pid
        WHEN 3 THEN did
    END AS id,
    CASE inp
        WHEN 1 THEN gname
        WHEN 2 THEN pname
        WHEN 3 THEN name
    END AS name
FROM final
-- 按需加过滤条件
WHERE
    (inp = 1)
    OR (inp = 2 AND gid = $1)
    OR (inp = 3 AND pid = $1)
-- 动态按对应字段排序
ORDER BY
    CASE inp
        WHEN 1 THEN gname
        WHEN 2 THEN pname
        WHEN 3 THEN name
    END;

注意：如果需要对结果去重，直接在最外层SELECT加DISTINCT关键字即可。

内容的提问来源于stack exchange，提问作者Pygirl