# 基于特定类别已解决主题数量的自定义徽章 SQL 查询

**URL:** https://meta.discourse.org/t/custom-badge-sql-query-based-on-how-many-solved-topics-in-a-certain-category/198865
**Category:** Data & reporting
**Tags:** solved, sql-triggered-badge
**Created:** [2021年八月2日 04:52 UTC](https://meta.discourse.org/t/custom-badge-sql-query-based-on-how-many-solved-topics-in-a-certain-category/198865 "2021-08-02T04:52:44Z")
**Posts on this page:** 1
**Page:** 1

<div class="post-metadata">

### Author: ![AquaL1te](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/aqual1te/32/201966_2.png) [@AquaL1te](https://meta.discourse.org/u/AquaL1te)
#### Post date: [2021年八月2日 04:52 UTC](https://meta.discourse.org/t/custom-badge-sql-query-based-on-how-many-solved-topics-in-a-certain-category/198865/1 "2021-08-02T04:52:44Z")

</div>

继续讨论来自 [您想出了哪些有趣的徽章查询？](https://meta.discourse.org/t/what-cool-badge-queries-have-you-come-up-with/18978)：

我试图解决以下问题。我希望下面的 SQL 查询能在单个类别 ID 中运行：

```sql
SELECT id user_id, current_timestamp granted_at
FROM users
WHERE id IN (
       SELECT p1.user_id
       FROM post_custom_fields pc
       JOIN badge_posts p1 ON p1.id = pc.post_id
       JOIN topics t1 ON p1.topic_id = t1.id
       WHERE p1.user_id <> t1.user_id AND
                    name = 'is_accepted_answer' AND
            p1.user_id IN (
                   SELECT user_id
                   FROM posts
                   WHERE :backfill OR p1.id IN (:post_ids)
            )
        GROUP BY p1.user_id
        HAVING COUNT(*) > 9
)

```

现在它检查的是全局论坛，但我想将其与如下查询合并：

```sql
SELECT DISTINCT ON (t.user_id) t.user_id, t.created_at granted_at
FROM topics t
WHERE t.category_id = 28

```

有什么建议吗？抱歉，我已经很久没碰 SQL 了。