PostgreSQL子查询返回多行错误修复请求：实现多维度多条件聚合统计查询

我看了你的代码，核心问题在于两个子查询没有和主查询的分组字段关联——它们会独立执行并返回所有分组的汇总结果，而主查询的每一行都试图引用这些多值结果，自然会触发"more than one row returned by a subquery used as an expression"这类错误。

你的需求是按年份、月份、区域、客户经理分组，分别计算两个不同条件下的总进件量和总销量，完全可以用条件聚合来实现，不用嵌套子查询，代码更简洁高效：

SELECT
    x.year_s_date AS Tahun,
    x.month_s_date AS Bulan,
    y.regionalname AS Region,
    y.namarm AS nama_rm,
    -- 总进件量：仅满足db_source不为空的条件
    SUM(CASE WHEN x.db_source IS NOT NULL THEN x.s_totalfinancing ELSE 0 END) AS incoming,
    -- 总销量：满足db_source、db_source_2不为空且lineage='1'的条件
    SUM(CASE WHEN x.db_source IS NOT NULL AND x.db_source_2 IS NOT NULL AND x.lineage = '1' THEN x.s_totalfinancing ELSE 0 END) AS sales
FROM staging.main_eform_kpm_final x
INNER JOIN (
    SELECT branchid, regionalname, namarm
    FROM landing.confins_dwh_rmkmb
    WHERE enddate > '2020-01-01'
) y ON x.branch_code = y.branchid
-- 过滤掉不需要的行，减少计算量
WHERE x.db_source IS NOT NULL
GROUP BY x.year_s_date, x.month_s_date, y.regionalname, y.namarm
ORDER BY Tahun, Bulan, Region, nama_rm;

为什么这样改更合适？ #

• 条件聚合通过CASE语句在同一分组中分别计算不同条件的总和，避免了多次连接表和嵌套子查询的额外开销。
• 去掉了不必要的DISTINCT：因为已经按目标字段GROUP BY，结果自然是唯一的分组，不需要额外去重。
• 子查询y只保留需要的字段，减少了数据传输和处理的量级。

如果你坚持要用子查询的方式，也必须让子查询和主查询的分组字段关联，确保每个子查询只返回当前主查询行对应分组的结果，示例如下：

SELECT
    x.year_s_date AS Tahun,
    x.month_s_date AS Bulan,
    y.regionalname AS Region,
    y.namarm AS nama_rm,
    (
        SELECT SUM(a.s_totalfinancing)
        FROM staging.main_eform_kpm_final a
        INNER JOIN landing.confins_dwh_rmkmb b ON a.branch_code = b.branchid
        WHERE b.enddate > '2020-01-01'
          AND a.db_source IS NOT NULL
          -- 关联主查询的分组字段，限制子查询返回当前分组的结果
          AND a.year_s_date = x.year_s_date
          AND a.month_s_date = x.month_s_date
          AND b.regionalname = y.regionalname
          AND b.namarm = y.namarm
    ) AS incoming,
    (
        SELECT SUM(a.s_totalfinancing)
        FROM staging.main_eform_kpm_final a
        INNER JOIN landing.confins_dwh_rmkmb b ON a.branch_code = b.branchid
        WHERE b.enddate > '2020-01-01'
          AND a.db_source IS NOT NULL
          AND a.db_source_2 IS NOT NULL
          AND a.lineage = '1'
          -- 同样关联主查询的分组字段
          AND a.year_s_date = x.year_s_date
          AND a.month_s_date = x.month_s_date
          AND b.regionalname = y.regionalname
          AND b.namarm = y.namarm
    ) AS sales
FROM staging.main_eform_kpm_final x
INNER JOIN (
    SELECT branchid, regionalname, namarm
    FROM landing.confins_dwh_rmkmb
    WHERE enddate > '2020-01-01'
) y ON x.branch_code = y.branchid
GROUP BY x.year_s_date, x.month_s_date, y.regionalname, y.namarm
ORDER BY Tahun, Bulan, Region, nama_rm;

不过这种方式性能远不如条件聚合，因为每个分组都会触发两次子查询执行，数据量大时会明显变慢，所以优先推荐第一种方案。

内容的提问来源于stack exchange，提问作者Handri Mauludin Maulana