PostgreSQL与HQL中含子表的多结果提取优化方案咨询

需求说明 #

需要从PostgreSQL的三张表中获取以下三个统计值：

• parent表中2020年id为101、102、103的记录金额总和（预期结果：1300）
• 关联上述parent记录的child-a表记录数量（预期结果：4）
• 关联上述parent记录的child-b表记录数量（预期结果：2）

表结构 #

parent表 #

| id   |  name     | amount    | year  |
----------------------------------------
| 101  |  Henry    | 300       | 2020  |
| 102  |  Carol    | 100       | 2020  |
| 103  |  Tom      | 900       | 2020  |
| 104  |  Fredrick | 150       | 2022  |
| 105  |  Mary     | 180       | 2023  |

child-a表 #

| id   |  parent_id  | value         |
--------------------------------------
| 1    |  101        | value-a       |
| 2    |  101        | value-b       |
| 3    |  101        | value-c       |
| 4    |  102        | value-d       |

child-b表 #

| id   |  parent_id  | value         |
--------------------------------------
| 1    |  101        | value-x       |
| 2    |  102        | value-y       |
| 3    |  105        | value-z       |
| 4    |  106        | value-t       |

原查询问题 #

原查询存在三个核心问题：

1. 子查询未过滤2020年的parent记录，会统计所有子表数据，不符合需求
2. HQL不支持在子查询的FROM子句中使用JOIN，执行会报错
3. 直接多表关联会产生笛卡尔积，导致parent的金额被重复计算

兼容HQL的解决方案 #

采用独立标量子查询+EXISTS过滤的方式，既避免金额重复计算，又符合HQL语法规范：

SELECT
    (SELECT COALESCE(SUM(p.amount), 0) FROM Parent p WHERE p.year = 2020) AS totalAmount,
    (SELECT COUNT(ca.id) FROM ChildA ca WHERE EXISTS (
        SELECT 1 FROM Parent p 
        WHERE p.id = ca.parentId AND p.year = 2020
    )) AS childACount,
    (SELECT COUNT(cb.id) FROM ChildB cb WHERE EXISTS (
        SELECT 1 FROM Parent p 
        WHERE p.id = cb.parentId AND p.year = 2020
    )) AS childBCount
FROM Parent p
WHERE p.year = 2020
FETCH FIRST 1 ROW ONLY

方案说明 #

1. 金额统计：单独的标量子查询直接统计2020年parent的金额总和，用COALESCE处理无匹配记录时的空值
2. 子表统计：通过EXISTS子句关联parent表，仅统计属于2020年parent的子表记录，避免全表统计
3. 结果行控制：FETCH FIRST 1 ROW ONLY（不同数据库可替换为LIMIT 1或ROWNUM <=1）确保只返回一行统计结果，避免外层查询因多条parent记录返回重复结果

如果HQL版本不支持FETCH FIRST，可改用聚合分组确保单行结果：

SELECT
    COALESCE(SUM(p.amount), 0) AS totalAmount,
    (SELECT COUNT(ca.id) FROM ChildA ca WHERE EXISTS (
        SELECT 1 FROM Parent p2 
        WHERE p2.id = ca.parentId AND p2.year = 2020
    )) AS childACount,
    (SELECT COUNT(cb.id) FROM ChildB cb WHERE EXISTS (
        SELECT 1 FROM Parent p3 
        WHERE p3.id = cb.parentId AND p3.year = 2020
    )) AS childBCount
FROM Parent p
WHERE p.year = 2020
GROUP BY childACount, childBCount

内容的提问来源于stack exchange，提问作者Murat Yıldız