mysql - sql - 每个组的排序依据

Question

我有一个如下所示的查询:

SELECT
    USER .id,
    listing.country,
    count(listing.id) AS count
FROM    USER
INNER JOIN listing ON USER.id = listing.user_id
GROUP BY listing.country,   USER.id
ORDER BY count DESC;

我得到的结果如下所示:

但是我希望它按国家/地区分组，以便用户可以按国家/地区的列表数量进行排名。有办法解决这个问题吗？

Answer 1

您可以有多个 ORDER BY 列:

SELECT user.id, 
       listing.country,
       count(listing.id) as count 
FROM user
INNER JOIN listing ON (user.id = listing.user_id) 
GROUP BY listing.country, user.id 
ORDER BY country, count DESC;