MySQL Workbench：合并地点用于竞赛、培训、俱乐部的统计查询

你可以通过子查询关联或者带DISTINCT的多表关联两种方式，把三个统计结果合并到一张表里，避免重复计数的问题：

方法一：子查询关联（推荐，效率更高） #

先分别统计每个类型的使用次数，再关联到地点表，确保不会出现笛卡尔积导致的统计错误：

SELECT 
    l.LocId,
    l.Postcode,
    -- 用COALESCE把NULL转为0，避免无记录的地点显示空值
    COALESCE(comp.competition_count, 0) AS competition_count,
    COALESCE(train.training_count, 0) AS training_count,
    COALESCE(club.club_meeting_count, 0) AS club_meeting_count
FROM locations l
-- 关联竞赛统计子查询
LEFT JOIN (
    SELECT LocId, COUNT(CId) AS competition_count
    FROM competitions
    GROUP BY LocId
) comp ON l.LocId = comp.LocId
-- 关联培训统计子查询
LEFT JOIN (
    SELECT LocId, COUNT(TId) AS training_count
    FROM trainings
    GROUP BY LocId
) train ON l.LocId = train.LocId
-- 关联俱乐部会议统计子查询
LEFT JOIN (
    SELECT LocId, COUNT(Cno) AS club_meeting_count
    FROM clubs
    GROUP BY LocId
) club ON l.LocId = club.LocId;

方法二：多表关联+DISTINCT（写法简洁） #

如果数据量不大，可以直接关联三个表，用COUNT(DISTINCT)避免笛卡尔积导致的重复计数：

SELECT 
    l.LocId,
    l.Postcode,
    COUNT(DISTINCT c.CId) AS competition_count,
    COUNT(DISTINCT t.TId) AS training_count,
    COUNT(DISTINCT cl.Cno) AS club_meeting_count
FROM locations l
LEFT JOIN competitions c ON l.LocId = c.LocId
LEFT JOIN trainings t ON l.LocId = t.LocId
LEFT JOIN clubs cl ON l.LocId = cl.LocId
GROUP BY l.LocId, l.Postcode;

为什么你之前的LEFT JOIN没成功？ #

直接把三个单独的统计结果JOIN时，容易忽略两点：

1. 没有以locations表为基础关联，导致缺失无任何使用记录的地点；
2. 多表直接关联会产生笛卡尔积，同一个地点的多条竞赛、培训记录会交叉组合，让COUNT的结果远大于实际次数。

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