如何在MySQL中从多表统计城市关联次数并分组？

你可以通过两种常见方式实现多表联合统计：

方法一：使用UNION ALL合并数据集后统计 #

先将users和companies表中的city_id合并成包含所有关联记录的数据集，再与cities表关联并分组统计：

SELECT 
    c.name AS city,
    COUNT(*) AS count
FROM (
    SELECT city_id FROM users
    UNION ALL
    SELECT city_id FROM companies
) AS combined
JOIN cities c ON combined.city_id = c.id
GROUP BY c.name
ORDER BY count DESC;

说明 #

• UNION ALL保留两个表的所有记录（含重复项），确保统计的是总关联次数
• 子查询combined生成所有关联城市的用户、公司city_id集合
• 关联cities表获取城市名称，按名称分组统计总数

方法二：分别统计后求和 #

如果需要同时查看用户、公司的单独统计数，可通过LEFT JOIN分别统计后相加：

SELECT 
    c.name AS city,
    COALESCE(u.user_count, 0) + COALESCE(comp.company_count, 0) AS count
FROM cities c
LEFT JOIN (
    SELECT city_id, COUNT(*) AS user_count
    FROM users
    GROUP BY city_id
) u ON c.id = u.city_id
LEFT JOIN (
    SELECT city_id, COUNT(*) AS company_count
    FROM companies
    GROUP BY city_id
) comp ON c.id = comp.city_id
ORDER BY count DESC;

说明 #

• 子查询分别统计每个城市的用户数、公司数
• LEFT JOIN确保无关联记录的城市也能被纳入统计（COALESCE将NULL转为0）
• 两个统计数相加得到总关联次数

两种方法均可输出你期望的结果，例如：

city           count  
Amsterdam      5
London         7

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