多表联查统计求助：子表无数据时需返回主表数据且统计值为0

你的问题出在使用聚合函数但未添加GROUP BY子句，导致数据库将所有行视为一个分组进行聚合，从而丢失了tournament表的独立记录，尤其是当tournament_players无数据时，结果完全不符合预期。

修复方案1：添加GROUP BY子句 #

直接在原查询末尾添加GROUP BY，确保每个tournament的记录单独统计对应玩家数：

SELECT 
    tournament.*,
    categories.title AS category,
    categories.slug AS category_slug,
    games.game_name,
    games.slug AS game_slug,
    COUNT(tournament_players.id) AS total_players
FROM tournament
LEFT JOIN categories ON categories.id = tournament.category_id
LEFT JOIN games ON games.game_id = tournament.game_id
LEFT JOIN tournament_players ON tournament_players.tournamentID = tournament.id
GROUP BY tournament.id, categories.title, categories.slug, games.game_name, games.slug

说明 #

• COUNT(tournament_players.id)会自动忽略NULL值（即无玩家匹配的情况），直接返回0
• GROUP BY需要包含所有非聚合字段（或数据库支持的依赖字段），确保每个tournament的记录独立存在，不会被合并成一行

修复方案2：用子查询预先统计玩家数 #

如果觉得GROUP BY字段过多，也可以用子查询先统计每个赛事的玩家数量，再左连接到主查询：

SELECT 
    t.*,
    c.title AS category,
    c.slug AS category_slug,
    g.game_name,
    g.slug AS game_slug,
    COALESCE(tp.total_players, 0) AS total_players
FROM tournament t
LEFT JOIN categories c ON c.id = t.category_id
LEFT JOIN games g ON g.game_id = t.game_id
LEFT JOIN (
    SELECT tournamentID, COUNT(id) AS total_players
    FROM tournament_players
    GROUP BY tournamentID
) tp ON tp.tournamentID = t.id

说明 #

• 子查询先按赛事ID分组统计玩家数，再通过左连接关联到主查询
• COALESCE确保当子查询无匹配结果时，显示0而非NULL

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