MySQL：用户，用户组和组表

Question

我有三个表格（这里只涉及问题列）：

USERS              GROUPS                 USERS_GROUPS
id                 id                     user_id
                   importance             group_id
                   color                   

如您所见，一个用户可以在多个组中，每个组都有不同的颜色，一些组比其他组更重要。例如，用户A在“编辑器”（蓝色）和“管理员”（绿色）组中。他应该显示为哪种颜色？绿色，因为管理员比编辑器更重要。

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

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

我希望只有最重要的组 - 管理员作为组管理员的成员，普通用户作为组主持人的成员。

- Andy

Answer 1

这是MySQL中一个相当常见的问题，有几种不同的解决方案，通常最快的方法是连接到选择最高重要性以过滤结果的子查询。

SELECT users.*, groups.*
FROM `users` 
INNER JOIN users_groups 
ON users_groups.user_id = users.id 
INNER JOIN groups 
ON groups.id=users_groups.group_id
INNER JOIN (SELECT user_groups.user_id, MAX(importance) AS importance FROM groups GROUP BY user_id) mxGrp
ON (groups.user_id = mxGrp.user_id AND groups.importance = mxGrp.importance);

阅读这篇博客文章，了解其他可能的解决方案。

- Bad Wolf

Answer 2

选项1：此选项获取每个用户的最重要组，并返回到“groups”表中查找该组的颜色。需要额外的JOIN，因为初始GROUP BY不一定会抓取与MAX（importance）结果相对应的正确颜色。

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语句。

- Andrew G