用户徽章计数，带徽章名称过滤器

[alefattorini (Alessio Fattorini)]

@SidV

测试一下这个：

-- [params]
-- 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

我该如何筛选特定的徽章？我需要统计每个人获得该徽章的次数。
例如，“如何撰写者徽章”，我需要知道谁撰写了更多的“如何”指南并生成排名。

[alefattorini (Alessio Fattorini)]

有什么提示吗？我尝试添加一行：
AND b.id = 136
但不起作用。

[simon]

这是该查询的一个略微修改版本，添加了 badge name 过滤器。badge name 的默认值为 'all badges'。当设置为该值时，将返回所有徽章的结果。如果您将 badge name 设置为特定徽章的名称，则仅返回该徽章的结果。

-- [params]
-- int :posts = 1
-- int :top = 10
-- string :badge_name = all badges

SELECT
username,
COUNT(ub.id) as badge_count
FROM user_badges ub
JOIN users u ON u.id = ub.user_id
JOIN user_stats us
ON us.user_id = ub.user_id
JOIN badges b ON b.id = ub.badge_id
WHERE us.post_count > :posts
AND (u.admin = 'f' AND u.moderator = 'f')
AND CASE
        WHEN 'all badges' = :badge_name
            THEN true
        ELSE b.name = :badge_name
    END
GROUP BY u.username
ORDER BY badge_count DESC

[alefattorini (Alessio Fattorini)]

就是这样 非常感谢