是否有人编写过 SQL 语句来显示用户列表（例如前 10 名），并按用户拥有的徽章总数排序？
我曾在数据浏览器中查看过"user_badges"表，发现其中包含"user_id"列和"badge_id"列，但我的 SQL 技能尚不足以执行计数或连接查询来生成前 10 名的列表。
是否有人已经做过类似的事情？
按徽章数量排序的用户
测试以下内容：
-- [参数]
-- int :posts = 100
-- int :top = 10
SELECT u.username, count(ub.id) as "Badges"
FROM user_badges ub, users u, user_stats us
WHERE u.id = ub.user_id
AND u.id = us.user_id
AND us.post_count > :posts
AND (u.admin = 'f' AND u.moderator = 'f')
GROUP BY u.username
ORDER BY count(ub.id) desc
LIMIT :top
是的。许多徽章都有多次获得的选项。
如果这样更准确，请查看：
-- [params]
-- int :posts = 100
-- int :top = 10
SELECT u.username, count(ub.id) as "Badges"
FROM user_badges ub, users u, user_stats us, badges b
WHERE u.id = ub.user_id
AND u.id = us.user_id
AND b.id = ub.badge_id
AND us.post_count > :posts
AND (u.admin = 'f' AND u.moderator = 'f')
AND b.multiple_grant = 'f'
GROUP BY u.username
ORDER BY count(ub.id) desc
LIMIT :top
