mysql - 空表上的 LEFT JOIN NULL 结果

Question

当我加入一个空表并将其添加到 kills_total 字段时，kills_total 值显示 NULL。

SELECT 
       alias.name alias, team.name team, sid.steam_id steam_id,
       (SUM(ws1.kills) + SUM(ws2.kills) + SUM(spnr.kills)) kills_total
FROM pickup
            JOIN player ON player.pickup_id = pickup.id
            JOIN team ON player.team_id = team.id
            JOIN sid ON player.sid_id = sid.id
            JOIN alias ON player.alias_id = alias.id
            LEFT JOIN weapon_stats_1 ws1 ON ws1.pickup_id = pickup.id AND ws1.player_id = player.id
            LEFT JOIN weapon_stats_2 ws2 ON ws2.pickup_id = pickup.id AND ws2.player_id = player.id
            LEFT JOIN spanner_stats spnr ON spnr.pickup_id = pickup.id AND spnr.player_id = player.id
WHERE pickup.logfile_name = 'srv-20130725-2151-log' GROUP BY player.id ORDER BY kills_total DESC

这是结果:

SELECT 
       alias.name alias, team.name team, sid.steam_id steam_id,
       (SUM(ws1.kills) + SUM(ws2.kills)) kills_total
FROM pickup
            JOIN player ON player.pickup_id = pickup.id
            JOIN team ON player.team_id = team.id
            JOIN sid ON player.sid_id = sid.id
            JOIN alias ON player.alias_id = alias.id
            LEFT JOIN weapon_stats_1 ws1 ON ws1.pickup_id = pickup.id AND ws1.player_id = player.id
            LEFT JOIN weapon_stats_2 ws2 ON ws2.pickup_id = pickup.id AND ws2.player_id = player.id
WHERE pickup.logfile_name = 'srv-20130725-2151-log' GROUP BY player.id ORDER BY kills_total DESC

这是我从查询中删除空表时的结果:

我如何加入 spanner_stats 表，如果它是空的，什么都不做(即 SUM(0))，所以 ws1 和 ws2 即使正在连接的第三个表没有匹配 "thirdTable.pickup_id = pickup.id AND thirdTable.player_id = player .id"?

Answer 1

试试这个:

SELECT 
       alias.name alias, team.name team, sid.steam_id steam_id,
       (SUM(IFNULL(ws1.kills,0)) + SUM(IFNULL(ws2.kills,0)) + SUM(IFNULL(spnr.kills,0))) kills_total
FROM pickup
            JOIN player ON player.pickup_id = pickup.id
            JOIN team ON player.team_id = team.id
            JOIN sid ON player.sid_id = sid.id
            JOIN alias ON player.alias_id = alias.id
            LEFT JOIN weapon_stats_1 ws1 ON ws1.pickup_id = pickup.id AND ws1.player_id = player.id
            LEFT JOIN weapon_stats_2 ws2 ON ws2.pickup_id = pickup.id AND ws2.player_id = player.id
            LEFT JOIN spanner_stats spnr ON spnr.pickup_id = pickup.id AND spnr.player_id = player.id
WHERE pickup.logfile_name = 'srv-20130725-2151-log' GROUP BY player.id ORDER BY kills_total DESC

本质上，如果左连接没有记录，您是在告诉 MySql 使用 0 而不是 NULL。看看 IFNULL() http://www.w3schools.com/sql/sql_isnull.asp .

我相信，如果任何组件为 NULL，SQL SUM() 函数将返回 NULL。