PostgreSQL中如何在SELECT内按RLIMIT统计UBURN各值出现次数

错误原因 #

你编写的语句存在语法错误的核心原因是：SQL属于声明式查询语言，SELECT子句中不支持直接使用FOR循环这类过程式逻辑，且查询返回的列名必须在查询执行前就确定，无法通过循环动态生成。 #

方案1：RLIMIT固定时使用静态聚合（最简便） #

如果你可以提前确定RLIMIT的取值上限（比如示例中固定为4），直接手动编写所有统计列即可：

SELECT
  COUNT(*) FILTER (WHERE b.UBURN = 1) AS "TALLY 1",
  COUNT(*) FILTER (WHERE b.UBURN = 2) AS "TALLY 2",
  COUNT(*) FILTER (WHERE b.UBURN = 3) AS "TALLY 3",
  COUNT(*) FILTER (WHERE b.UBURN = 4) AS "TALLY 4"
FROM tableB b
INNER JOIN tableA a ON b.AID = a.ID;

执行后会直接返回你需要的结果格式。 #

方案2：RLIMIT动态时使用动态SQL #

如果RLIMIT是动态取值，不同ID对应的上限不同，需要用PostgreSQL的动态SQL逻辑生成查询语句：

DO $$
DECLARE
    v_rlimit INT;
    v_sql TEXT;
    v_idx INT;
BEGIN
    -- 取对应AID的RLIMIT，此处示例取ID=1，可根据需求调整过滤条件
    SELECT RLIMIT INTO v_rlimit FROM tableA WHERE ID = 1;
    -- 拼接查询SQL
    v_sql := 'SELECT ';
    FOR v_idx IN 1..v_rlimit LOOP
        v_sql := v_sql || format('COUNT(*) FILTER (WHERE UBURN = %s) AS "TALLY %s", ', v_idx, v_idx);
    END LOOP;
    -- 去除末尾多余的逗号，拼接查询条件
    v_sql := rtrim(v_sql, ', ') || ' FROM tableB WHERE AID = 1';
    -- 打印生成的SQL，复制后直接执行即可返回结果
    RAISE NOTICE '生成的查询语句：%', v_sql;
END $$;

如果是在psql交互环境中，你也可以直接拼接变量后用\gexec命令直接执行生成的SQL，无需编写匿名块。 #

方案3：纵向输出统计结果（无需动态SQL） #

如果不要求严格的横向多列输出，也可以用分组统计返回纵向结果，适配任意动态RLIMIT：

SELECT
  'TALLY ' || series.val AS tally_name,
  COUNT(b.ID) AS tally_count
FROM tableA a
CROSS JOIN generate_series(1, a.RLIMIT) AS series(val)
LEFT JOIN tableB b ON b.AID = a.ID AND b.UBURN = series.val
WHERE a.ID = 1
GROUP BY series.val
ORDER BY series.val;

返回结果格式为：

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