SQL 查询不显示空值

Question

我已经设法一起破解了一个SQL查询，并且它一直在工作，直到我意识到由于我们使用的插件删除了非ASCII名称，数据库中的某些成员具有空名称。我在我的页面上显示这些统计数据，让一个没有名字的人效果不太好。我知道使用 WHERE 子句时如何不显示 NULL，但我不太确定何时不使用 WHERE 子句。

到目前为止，我所拥有的——

SELECT player_stats.player_name, COUNT(player_kill.killer) 
FROM player_stats 
LEFT JOIN player_kill ON player_stats.player_id = player_kill.killer  
GROUP BY player_stats.player_name 
HAVING COUNT(player_kill.killer) > 1 
ORDER BY COUNT(player_kill.killer) DESC;

Answer 1

WHERE 子句位于所有JOIN子句和GROUP BY 之间。如果WHERE player_name IS NOT NULL不起作用，则名称可能是空字符串，而不是NULL，因此您还需要检查这一点。

SELECT s.player_name, COUNT(*) AS count
FROM player_stats AS s
INNER JOIN player_kill AS k ON s.player_id = k.killer  
WHERE s.player_name IS NOT NULL AND s.player_name != ''
GROUP BY s.player_name 
ORDER BY count DESC;

此外，如果您不想获得 0 计数，请使用 INNER JOIN 而不是 LEFT JOIN 。

Answer 2

如果您不想将查询更改为太多，可以通过检查它是否为 null 来替换玩家名称。

SELECT ISNULL(s.player_name, "RandomPlayer"), COUNT(*) AS count
FROM player_stats AS s
INNER JOIN player_kill AS k ON s.player_id = k.killer  
WHERE s.player_name IS NOT NULL AND s.player_name != ''
GROUP BY s.player_name 
ORDER BY count DESC;

https://msdn.microsoft.com/en-us/library/ms184325.aspx