SQL Server多表关联查询gc_count统计结果不准确问题求助

问题原因 #

同时左连接group_company和Role表时，两个表的记录会形成笛卡尔积：比如cert01对应1条group_company记录和2条Role记录，连接后会生成2条重复的group_company关联记录，导致count(gc.cert_id)统计出错误数值（实际应为1，却统计为2）。

修复方案 #

分别对group_company和Role表做分组统计，再将统计结果与Certificate表关联，彻底避免笛卡尔积的影响。

正确SQL语句 #

SELECT
    ct.id,
    ct.name,
    ISNULL(gc_stats.gc_count, 0) AS gc_count,
    ISNULL(r_stats.role_count, 0) AS role_count
FROM certificate ct
LEFT JOIN (
    SELECT cert_id, COUNT(*) AS gc_count
    FROM group_company
    WHERE company_id = 1001
    GROUP BY cert_id
) gc_stats ON ct.id = gc_stats.cert_id AND ct.company_id = 1001
LEFT JOIN (
    SELECT cert_id, COUNT(*) AS role_count
    FROM role
    WHERE company_id = 1001
    GROUP BY cert_id
) r_stats ON ct.id = r_stats.cert_id AND ct.company_id = 1001
WHERE ct.company_id = 1001;

执行结果 #

执行后将得到预期的正确结果：

+----+--------+----------+------------+
| id | name   | gc_count | role_count |
+----+--------+----------+------------+
| 1  | cert01 | 1        | 2          |
| 2  | cert02 | 1        | 2          |
| 3  | cert03 | 1        | 1          |
+----+--------+----------+------------+

补充说明 #

ISNULL函数用于兼容无对应关联记录的场景：如果某个证书未关联group_company或Role，统计值会显示为0而非NULL，更符合业务统计的常规需求。

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