拥有某些其他徽章的徽章

社区 数据与报告
1

我正在编写一个 SQL 徽章查询，用于当用户拥有指定列表中的所有徽章时授予其徽章（例如 ID 为 3、4 和 5 的徽章）。

我知道以下查询适用于单个徽章：

SELECT user_id, count(id), current_timestamp AS granted_at
FROM user_badges 
WHERE badge_id = 4
GROUP BY user_id

但如何使其适用于多个徽章 ID 呢？添加另一个 badge_id = X 条件行不通，因为这只会查找同时拥有这两个徽章的用户（逻辑上需要的是交集）。INTERSECT 看起来可行，但不被支持。此查询将每天运行，且不针对任何帖子。
谢谢！

3 个赞
2

I use something like this to create Alternative User Level for my members.
You can replace ib.badge_id with the badge id your members should have achieved.

SELECT user_id, CURRENT_TIMESTAMP granted_at, NULL post_id
FROM user_badges pb
WHERE badge_id = 1
AND EXISTS (
  SELECT 1 FROM user_badges ib
  WHERE pb.user_id = ib.user_id
  AND ib.badge_id = 11
)
AND EXISTS (
  SELECT 1 FROM user_badges ib
  WHERE pb.user_id = ib.user_id
  AND ib.badge_id = 124
)
AND EXISTS (
  SELECT 1 FROM user_badges ib
  WHERE pb.user_id = ib.user_id
  AND ib.badge_id = 123
)
AND EXISTS (
  SELECT 1 FROM user_badges ib
  WHERE pb.user_id = ib.user_id
  AND ib.badge_id = 127
)
AND EXISTS (
   SELECT 1 FROM user_badges ib
   WHERE pb.user_id = ib.user_id
  AND ib.badge_id = 5
)
7 个赞
3

Thanks for your help, that works perfectly!

1 个赞
4

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.