mysql - 如何从两个 GROUP_CONCAT 列中发现缺失值？

Question

我有表 All Badges 和表 User Badge。

User Badge中的值都可以在All Badges上找到，每个user_id可以有多个User Badge所以我用GROUP_CONCAT来根据 user_id 对值进行分组。 User Badge 的目标是让用户收集所有可用的徽章，因此他们将需要有关其个人资料中丢失的徽章的信息。

这是所有徽章表:

|---------------|-----------|
|   Badge_ID    |   Label   |
|---------------|-----------|
|       1       |     a     |
|       2       |     b     |
|       3       |     c     |
|       4       |     d     |
|       5       |     e     |
|---------------|-----------|

用户徽章表:

|------------------|---------------|---------------|---------------|
|  User_Badge_ID   |  Badge_Label  |    user_ID    |  fk_badge_id  |
|------------------|---------------|---------------|---------------|
|        1         |       a       |       1       |       1       |
|        2         |       a       |       2       |       1       |
|        3         |       b       |       1       |       2       |
|        4         |       a       |       3       |       1       |
|        5         |       b       |       2       |       2       |
|        6         |       c       |       2       |       3       |
|        7         |       d       |       2       |       4       |
|------------------|---------------|---------------|---------------|

我有这个查询来根据 user_id 对徽章进行分组，但我不知道显示缺失值的查询。

SELECT
     user_badge.user_id AS User_ID
     GROUP_CONCAT(user_badge.Badge_Label) AS User_Badge
FROM
     User_Badge

想要的结果。丢失的徽章将添加到名为 Missing Badge 的新列中。

|---------------------|------------------|------------------|
|      User_ID        |    User_Badge    |   Missing Badge  |
|---------------------|------------------|------------------|
|          1          |       a, b       |     c, d, e      |
|---------------------|------------------|------------------|
|          2          |     a, b, c, d   |         e        |
|---------------------|------------------|------------------|
|          3          |         a        |    b, c, d, e    |
|---------------------|------------------|------------------|

有什么想法可以显示每个 user_id 的缺失值吗？非常感谢您愿意提供帮助。

Answer 1

select s.user_id,
         group_concat(case when fk_badge_id is not null then s.label end) 'user_badge',
         group_concat(case when fk_badge_id is null then s.label end) 'missing'
from
(
SELECT distinct USER_ID,badge_id,ab.label
FROM USER_BADGE ub
CROSS JOIN ALL_BADGES ab
) s
left join user_badge ub on ub.user_id = s.user_id and ub.fk_badge_id = s.badge_id
group by s.user_id;

子查询使用交叉连接获取用户可能拥有的所有徽章，而主查询检查他/她是否拥有这些徽章，如果没有则左连接返回空值。

+---------+------------+---------+
| user_id | user_badge | missing |
+---------+------------+---------+
|       1 | a,b        | c,d,e   |
|       2 | a,b,c,d    | e       |
|       3 | a          | c,d,e,b |
+---------+------------+---------+
3 rows in set (0.00 sec)