SQL查询结果异常相乘：裁判与助理裁判次数统计错误求助

嘿，这个问题我太熟悉了——十有八九是**笛卡尔积（Cartesian Product）**在搞鬼！你看到的66其实是11×6的结果，说明你的查询让主裁判的记录和助理裁判的记录发生了无意义的交叉匹配，每一条主裁记录都对应了所有的助理裁记录，最终统计出了重复的总数。

问题根源分析 #

举个简单的例子：如果某裁判有11次主裁记录、6次助理裁记录，而你的查询直接把主裁表和助理裁表做了关联却没处理好分组/过滤逻辑，数据库就会生成11×6=66条交叉记录。这时候用COUNT()统计的话，就会把这些重复的记录都算进去，导致两个统计值都变成66，而不是各自的真实次数。

两种常见场景的解决方案 #

场景1：所有裁判记录存在于同一张表中

假设你的数据存在matches表，里面有referee_id（主裁判ID）和assistant_referee_id（助理裁判ID）字段，推荐用条件聚合或者先通过UNION ALL拆分角色再统计：

-- 方法1：条件聚合
SELECT
    official_id,
    SUM(CASE WHEN role = 'referee' THEN 1 ELSE 0 END) AS referee_count,
    SUM(CASE WHEN role = 'assistant referee' THEN 1 ELSE 0 END) AS assistant_count
FROM (
    -- 把主裁和助理裁的记录拆分成统一的角色格式
    SELECT referee_id AS official_id, 'referee' AS role FROM matches
    UNION ALL
    SELECT assistant_referee_id AS official_id, 'assistant referee' AS role FROM matches
) AS official_roles
GROUP BY official_id;

场景2：主裁和助理裁记录分属两张不同表

如果主裁记录在referee_assignments表，助理裁记录在assistant_ref_assignments表，那应该先分别统计各表的次数，再关联合并结果，而不是直接连接两张表：

SELECT
    -- 处理只担任一种裁判的情况
    COALESCE(r.official_id, ar.official_id) AS official_id,
    COALESCE(r.referee_count, 0) AS referee_count,
    COALESCE(ar.assistant_count, 0) AS assistant_count
FROM (
    -- 单独统计主裁次数
    SELECT referee_id AS official_id, COUNT(*) AS referee_count
    FROM referee_assignments
    GROUP BY referee_id
) r
-- 用全外连接确保两种裁判的记录都能被包含
FULL OUTER JOIN (
    -- 单独统计助理裁次数
    SELECT assistant_referee_id AS official_id, COUNT(*) AS assistant_count
    FROM assistant_ref_assignments
    GROUP BY assistant_referee_id
) ar ON r.official_id = ar.official_id;

避坑提醒 #

千万别直接写类似这样的查询：

-- 错误示例！会产生笛卡尔积
SELECT 
    COUNT(r.id) AS referee_count,
    COUNT(ar.id) AS assistant_count
FROM referee_assignments r
JOIN assistant_ref_assignments ar ON r.official_id = ar.official_id
WHERE r.official_id = '目标裁判ID';

这种写法会让该裁判的11条主裁记录和6条助理裁记录两两匹配，生成66条重复记录，最终COUNT()统计的就是这个重复后的总数，而非真实次数。

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