MySQL多表关联统计：查询用户在其他关联表中的对应记录总数

对应MySQL查询语句 #

SELECT 
    u.name,
    COALESCE(v.visit_count, 0) AS `total in visitors`,
    COALESCE(t.team_count, 0) AS `total in teams`
FROM users u
LEFT JOIN members m ON u.user_id = m.user_id
LEFT JOIN (
    SELECT fingerprint_id, COUNT(*) AS visit_count 
    FROM visitors 
    GROUP BY fingerprint_id
) v ON m.fingerprint_id = v.fingerprint_id
LEFT JOIN (
    SELECT user_id, COUNT(*) AS team_count 
    FROM teams 
    GROUP BY user_id
) t ON u.user_id = t.user_id
ORDER BY u.user_id;

逻辑说明 #

• 先通过子查询分别统计visitors表每个指纹ID的总记录数、teams表每个用户ID的总记录数，避免直接关联多表产生笛卡尔积导致计数错误
• 用LEFT JOIN关联各表，保证所有用户都会展示在结果中
• 用COALESCE函数将无记录的空值转换为0，符合统计场景的展示习惯

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