[mysql]左联接对空表空值结果

标签: MySQL
发布时间: 2015/6/11 19:28:48
注意事项: 本文中文内容可能为机器翻译,如要查看英文原文请点击上面连接.

早上好

当我加入是空表,将它添加到kills_total字段,kills_total 值显示为空

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

这就是结果:

fail

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

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

enter image description here

如何加入spanner_stats表和什么都不做 (ie。SUM(0)) 如果被加入其空,所以ws1ws2值总和 () med 起来并显示在kills_total甚至当第三个表,时有没有单列匹配"thirdTable.pickup_id = pickup.id 和 thirdTable.player_id = player.id"?

解决方法 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。

赞助商