PostgreSQL多内连接GROUP BY计数结果异常求助

直接同时内连表B和表C会产生笛卡尔积：同一id_grp下的B表记录与C表记录会两两组合，导致count()统计的是组合后的行数，而非两张表各自的实际记录数，这就是你得到错误结果的原因。

以下是两种可行的修正方案：

方案一：子查询预统计分组数据（推荐） #

先分别对表B、表C按id_grp分组完成计数，再将统计结果与主表A连接，从根源避免笛卡尔积的产生。

SELECT 
    a.id_grp, 
    a.group_name, 
    a.created_on, 
    a.status, 
    COALESCE(b.users, 0) AS users,
    COALESCE(c.policy, 0) AS policy
FROM a
LEFT JOIN (
    SELECT id_grp, COUNT(id_utilis) AS users
    FROM b
    GROUP BY id_grp
) b ON a.id_grp = b.id_grp
LEFT JOIN (
    SELECT id_grp, COUNT(id_pol) AS policy
    FROM c
    GROUP BY id_grp
) c ON a.id_grp = c.id_grp
-- 若需仅保留同时存在B、C关联记录的分组，将上面的LEFT JOIN改为INNER JOIN，并添加以下WHERE条件
-- WHERE b.id_grp IS NOT NULL AND c.id_grp IS NOT NULL;

说明：子查询提前完成分组计数，每个id_grp在B、C的统计结果中仅占一行，与A表连接时不会产生重复组合。COALESCE用于处理分组无对应记录的情况，返回0而非NULL。

方案二：使用COUNT(DISTINCT)去重计数 #

如果你的需求是统计唯一用户数和唯一策略数，可以通过COUNT(DISTINCT)去除笛卡尔积带来的重复值：

SELECT 
    a.id_grp, 
    a.group_name, 
    a.created_on, 
    a.status, 
    COUNT(DISTINCT b.id_utilis) AS users,
    COUNT(DISTINCT c.id_pol) AS policy
FROM a
INNER JOIN b ON a.id_grp = b.id_grp 
INNER JOIN c ON a.id_grp = c.id_grp 
GROUP BY a.id_grp, a.group_name, a.created_on, a.status;

注意：如果表B中同一id_grp下存在重复的id_utilis（同一用户多次关联），COUNT(DISTINCT)会统计唯一用户数而非记录总数；表C的id_pol重复时同理。若需要统计记录总数，方案一更合适。

正确结果示例 #

id_grp|group_name    |created_on             |status|users|policy|
------+--------------+-----------------------+------+-----+------+
    17|Teller        |2022-09-09 16:00:44.842|     1|    2|     2|
    16|admnistrator  |2022-09-08 10:11:14.313|     1|    3|     1|
    18|Combined Group|2022-09-09 10:16:42.473|     1|    3|     3|

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