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

**URL:** https://meta.discourse.org/t/user-badge-counts-with-badge-name-filter/168098
**Category:** Data & reporting
**Tags:** sql-query
**Created:** [2020年十月19日 14:27 UTC](https://meta.discourse.org/t/user-badge-counts-with-badge-name-filter/168098 "2020-10-19T14:27:15Z")
**Posts on this page:** 4
**Page:** 1

<div class="post-metadata">

### Author: ![alefattorini](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/alefattorini/32/119928_2.png) [@alefattorini](https://meta.discourse.org/u/alefattorini)
#### Post date: [2020年十月19日 14:27 UTC](https://meta.discourse.org/t/user-badge-counts-with-badge-name-filter/168098/1 "2020-10-19T14:27:15Z")

</div>

> [@SidV](#):
>
> 测试一下这个：
> 
> ```plaintext
> -- [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
> 
> ```

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

---

<div class="post-metadata">

### Author: ![alefattorini](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/alefattorini/32/119928_2.png) [@alefattorini](https://meta.discourse.org/u/alefattorini)
#### Post date: [2020年十月22日 14:38 UTC](https://meta.discourse.org/t/user-badge-counts-with-badge-name-filter/168098/2 "2020-10-22T14:38:14Z")

</div>

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

---

<div class="post-metadata">

### Author: ![simon](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/simon/32/339122_2.png) [@simon](https://meta.discourse.org/u/simon)
#### Post date: [2020年十月23日 20:34 UTC](https://meta.discourse.org/t/user-badge-counts-with-badge-name-filter/168098/3 "2020-10-23T20:34:41Z")

</div>

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

```sql
-- [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

```

---

<div class="post-metadata">

### Author: ![alefattorini](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/alefattorini/32/119928_2.png) [@alefattorini](https://meta.discourse.org/u/alefattorini)
#### Post date: [2020年十月26日 11:10 UTC](https://meta.discourse.org/t/user-badge-counts-with-badge-name-filter/168098/4 "2020-10-26T11:10:33Z")

</div>

就是这样 🙂 非常感谢