MySQL:用户、用户组和组表

Question

我有 3 个表(这里只对问题列很重要):

USERS              GROUPS                 USERS_GROUPS
id                 id                     user_id
                   importance             group_id
                   color                   

如您所见，一个用户可以在多个组中，每个组都有不同的颜色，有些组比其他组更重要。例如，用户 A 在组 editor(蓝色)和 moderator(绿色)中。他应该是什么颜色？绿色，因为版主比编辑更重要。

但我不知道(也许在一个查询中不可能？)如何从表组中获取用户的颜色。我创建了这样的查询:

SELECT * FROM `users` INNER JOIN users_groups ON users_groups.user_id = users.id INNER JOIN groups ON groups.id=users_groups.group_id 

但它会返回与用户所在的组一样多的用户行，就像这样:

 USERNAME   (...)    GROUP_ID       NAME (group)         COLOR       IMPORTANCE
 user                2              moderator            green       50
 user                3              editor               blue        25
 administrator       1              admin                orange      100
 administrator       3              editor               blue        25

我只想获得最重要的组 - 管理员作为组管理员的成员，用户作为组版主的成员。

Answer 1

选项 1:这一个为每个用户获取最重要的组，然后返回到 groups 表中以找到该组的颜色。需要额外的 JOIN，因为初始 GROUP BY 不一定会获取与 MAX(重要性)结果对应的正确颜色。

SELECT T1.*, groups.color FROM
(
    SELECT users.id, MAX(groups.importance) AS most_important_group FROM `users` 
    INNER JOIN 
    users_groups 
    ON users_groups.user_id = users.id 
    INNER JOIN groups 
    ON groups.id=users_groups.group_id
    GROUP BY users.id
) AS T1
INNER JOIN
groups
ON T1.most_important_group=groups.importance
GROUP BY T1.id

选项 2:此选项以一种方式对初始结果集进行排序，该方式应将每个用户的最高重要性组以及该行中的相应颜色置于顶部。在外部 GROUP BY 之后，您应该只看到包含您想要的信息的每个用户的一行。您可以自定义从子查询返回哪些字段。我包含了尽可能多的内容，但如果您不需要它们，您应该减少它们(更少的字段应该意味着更快的结果)。

SELECT * FROM
(
    SELECT users.*, users_groups.*, groups.importance, groups.color FROM `users` 
    INNER JOIN users_groups 
    ON users_groups.user_id = users.id 
    INNER JOIN groups 
    ON groups.id=users_groups.group_id
    ORDER BY users.id, groups.importance DESC
) AS T1
GROUP BY T1.id

这些性能将根据您的表大小和索引结构而有所不同。两者都试一下，然后回答哪个更快。如果您需要更快的运行时间，请随时使用这些查询中的一些 EXPLAIN 语句和有关表的 DESCRIBE 语句来响应。