PostgreSQL含ltree列的资产组数量累加查询问题求助

核心问题原因 #

你的查询结果缺失grp3和grp4的直接原因有两个：

1. 别名拼写错误：关联子组时使用了错误的别名grp_Child（大写C），但你定义的子组别名是grp_child（小写c），导致层级关联逻辑完全不生效，grp3和grp4找不到对应的子组记录，后续的INNER JOIN Asset直接过滤掉了这些组。
2. 内连接过滤问题：即使修复拼写，INNER JOIN Asset会过滤掉所有没有关联资产的组（包括临时无资产的组），无法保证返回全部4个组的记录。

修正后的查询语句 #

SELECT 
    grp_parent."Id",
    grp_parent."Name",
    grp_parent."Path",
    grp_parent."Level",
    COUNT(asset."Id") AS "AssetQuantity"
FROM "AssetGroup" grp_parent
LEFT JOIN "AssetGroup" grp_child
    ON grp_parent."Path" @> grp_child."Path"
    AND grp_child."DelFlag" = FALSE -- 若表中无DelFlag字段可删除此条件
LEFT JOIN "Asset" asset
    ON grp_child."Id" = asset."GroupId"
    AND asset."DelFlag" = FALSE -- 若表中无DelFlag字段可删除此条件
WHERE grp_parent."DelFlag" = FALSE -- 过滤未删除的父组，不需要可移除
GROUP BY grp_parent."Id" -- 主键分组，自动覆盖其他列
ORDER BY grp_parent."Id";

关键修正说明 #

• 修复别名拼写：将grp_Child改为grp_child，确保ltree的层级关联（@>运算符表示父路径包含子路径）正常工作。
• 替换内连接为左连接：LEFT JOIN Asset保证即使组没有关联资产，也会保留记录，统计数为0。
• 优化统计逻辑：使用COUNT(asset."Id")而非COUNT("Asset".*)，因为资产ID无匹配时为NULL，COUNT会自动忽略NULL值，得到准确的资产数量；若需要明确显示0而非空值，可改用COALESCE(COUNT(asset."Id"), 0)。
• 简化分组：由于Id是AssetGroup表的主键，PostgreSQL允许仅按主键分组，自动包含SELECT中的其他非聚合列。

预期结果 #

执行修正后的查询会返回全部4个组的统计数据，父组的资产数量包含自身及所有子组的资产总数：

内容的提问来源于stack exchange，提问作者Nguyen Quoc Bao