我想做的是从一个表中检索所有数据，并根据用户在特定类别中玩的游戏数量对它们进行排序。有什么方法可以使用某种"COUNT WHERE"sql语句吗？
这是我目前所拥有的。它只会在用户玩过"fps"类别的游戏时返回用户，但我希望它能按降序显示所有用户，即使他们没有玩过fps游戏。请原谅我糟糕的桌子
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
播放_游戏表
id | user_id | game | game_cat
1 | 2 | kill | fps
2 | 1 | shoot| fps
3 | 2 | COD | fps
4 | 3 | dogs | cas
即使其他表中不存在相应的记录，也需要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 |
+------+-------+
我就是这么做的。没有子查询，没有COALESCE，没有COUNTIF垃圾。
SELECT `users`.`user`, COUNT(`played_games`.id) AS `c`
FROM `users`
LEFT OUTER JOIN `played_games` ON
`users`.`user_id` = `played_games`.`user_id`
AND `played_games`.`game_cat` = "fps"
GROUP BY `users`.`user_id`
ORDER BY `c` DESC, `user` ASC
SQLFiddle（不确定是否可以这样链接它们…）
试试这个：
SELECT ud.user, coalesce(sum(pg.game_cat = 'fps'), 0) Total
FROM user_data ud
LEFT JOIN played_games pg ON ud.user_id = pg.user_id
GROUP BY ud.user_id
ORDER BY Total DESC
这将显示所有用户以及他们玩过"fps"类游戏的次数。
合并的方法很有前途，但对我来说不起作用，唉~我刚刚发现NULLIF是解决这个问题的好方法。记住使用LEFT JOIN
COUNT( NULLIF(TABLE.ATTR, 1) ) AS total_count
TABLE.ATTR是一些可以为NULL的字段，下面是一个示例：
SELECT Posts.*, COUNT( NULLIF(Comments.user_email, 1) ) as comment_num
FROM (`Posts`)
LEFT OUTER JOIN `Comments` ON `Comments`.`post_id` = `Posts`.`id`
GROUP BY `Posts`.`id`
LIMIT 5
这个想法来自http://www.bennadel.com/blog/579-SQL-COUNT-NULLIF-Is-Totally-Awesome.htm
下面通过计数查询所有带有用户id和订单的游戏类别
select * from (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(+) GROUP BY user_data.user_id)
order by count desc