mysql order by 子句不适用于我的查询

Question

假设我有以下 mysql 查询

SELECT friends.id, sum(if(friends.online =1,1,0)) AS online_friends, ...,...
FROM some_table
GROUP BY id
ORDER BY sum(if(friends.online =1,1,0)) ASC;

我想要的是根据 online_friends ASC 或 DESC 对结果表进行排序，问题是此查询不按 online_friends 对它们进行排序。

有人可以帮我解决我的问题吗？

这是结果的示例

id      group         online_friends
--     -------       ---------------- 
1       west               5
2       east               3
3       north              7
4       south              2

我想根据 online_friends 对结果进行排序

Answer 1

您的查询应该有效:

SELECT friends.id, sum(if(friends.online =1,1,0)) AS online_friends, ...,...
FROM friends
GROUP BY id
ORDER BY sum(if(friends.online =1,1,0)) ASC;

但是，我会把它写成:

SELECT friends.id, sum(friends.online = 1) AS online_friends, ...,...
FROM friends
GROUP BY id
ORDER BY sum(friends.online = 1) ASC;

您还可以将order by更改为:

ORDER BY online_friends

如果您发现它仍然不起作用，请设置一个 SQL Fiddle，以便我们可以查看发生了什么。