php - 当 WHERE 不匹配时，在 COUNT 列中显示 "0"？

Question

我想做的是从一个表中检索所有数据，并根据用户在特定类别中玩的游戏数量对它们进行排序。有什么方法可以使用某种“COUNT WHERE”sql 语句吗？

这是我目前所拥有的。如果他们玩过“fps”类别的游戏，它只会返回用户，但我希望它以降序显示所有用户，即使他们没有玩过 fps 游戏。请原谅我糟糕的 table

SELECT user_data.user, COUNT(played_games.game_cat) as 'count'
FROM user_data, played_games
WHERE user_data.user_id = played_games.user_id and played_games.game_cat = 'fps'
GROUP BY user_data.user_id
ORDER BY 'count' DESC;

用户数据表

user_id | user

1       | jeff
2       | herb
3       | dug

played_games 表

id | user_id | game | game_cat

1  |    2    | kill | fps
2  |    1    | shoot| fps
3  |    2    | COD  | fps
4  |    3    | dogs | cas

Answer 1

你需要一个 LEFT OUTER JOIN即使在另一个表中不存在相应的记录，也可以获取记录。

SELECT user, coalesce(count(game_cat), 0) as count
FROM user_data LEFT OUTER JOIN played_games
ON user_data.user_id = played_games.user_id AND played_games.game_cat='fps'
GROUP BY user_data.user_id 
ORDER BY count desc;

在我的屏幕上给出以下结果

+------+-------+
| user | count |
+------+-------+
| herb |     2 |
| jeff |     1 |
| dug  |     0 |
+------+-------+