sql请求分发正确的徽章并模糊其余部分

我有一张table badges 它列出了所有可用的徽章（徽章id、名称、描述等），一个表格 users 它列出了所有用户（用户id、名称等）和一个表 users_badges 保存所有获得的徽章（id、徽章id、用户id）。
如果用户1获得徽章5，则输入一个条目（x，5，1） users_badges （用户只能获得一次每个徽章）。
为了向每个用户展示她迄今为止获得的哪些徽章以及哪些徽章仍然丢失，我想列出她所有的徽章，而已获得的徽章是彩色的，而未获得的徽章是灰色的。
为此，我需要一个适当的sql查询—这正是我正在努力解决的问题。可能的输出是一个独特的列表，旁边有所有的徽章和特定用户的用户id；如果她还没拿到徽章的话，那就无效了。
第一次尝试：

SELECT * FROM badges
LEFT JOIN users_badges ON badges.id = users_badges.badge_id
WHERE users_badges.user_id = ".$row['user_id']." OR users_badges.user_id IS NULL
ORDER BY badges.id

问题：这只适用于拥有其他用户或更多用户的所有徽章的用户，因为where踢出的徽章只属于其他用户。
第二次尝试：

SELECT * FROM badges
LEFT JOIN users_badges ON badges.id = users_badges.badge_id
ORDER BY badges.id

问题：如果徽章由多个用户拥有，则会多次列出。添加 DISTINCT 无法解决问题，因为行已经是不同的（不同的用户ID）。也许可以从这个方法出发，用 user_id 但前提是她已经获得徽章（以确保所有非自有徽章也被列出（灰色））？
谢谢！

在对数据结构进行一些假设后，我为本例创建了虚拟表：

create table #badges
(
    badge_id int,
    [name] varchar(50),
    [description] varchar(50)
)
create table #users
(
    [user_id] int,
    [name] varchar(50)
)
create table #users_badges
(
    id int,
    badge_id int,
    [user_id] int
)
insert into #badges
values
    (1,'Starter Badge','It''s your first time.'),
    (2,'Rookie Badge','You''re just getting started.'),
    (3,'Intermediate Badge','Now you''re starting to get the hang of this.'),
    (4,'Expert Badge','You are a master!')
insert into #users
values
    (1,'John Smith'),
    (2,'Alice Johnson'),
    (3,'Phillip Black'),
    (4,'Sarah Goodwell'),
    (5,'Ian Hunter')
insert into #users_badges
values
    (1,1,1),
    (2,1,2),
    (3,1,3),
    (4,1,4),
    (5,2,1),
    (6,2,2),
    (7,2,4),
    (8,3,5),
    (9,4,1),
    (10,4,2)

我认为这就是您要寻找的结果（这里，特别是使用 user_id 第1页-约翰·史密斯）：

select 
    b.*,  
    case when ub.id is not null then 'Earned' else null end as badgeStatus
from #badges b
left join #users_badges ub on b.badge_id = ub.badge_id and ub.[user_id] = 1

下面列出了所有的徽章，并显示了john获得的徽章：

/------------------------------------------------------------------------|--------\
| id | name               | description                                  | status |
|----|--------------------|----------------------------------------------|--------|
|  1 | Starter Badge      | It's your first time.                        | Earned |
|  2 | Rookie Badge       | You're just getting started.                 | Earned |
|  3 | Intermediate Badge | Now you're starting to get the hang of this. | NULL   |
|  4 | Expert Badge       | You are a master!                            | Earned |
\---------------------------------------------------------------------------------/

展开查看全部

sql请求分发正确的徽章并模糊其余部分

1条答案

相关问题

热门标签

最新问答