mysql - GROUP_CONCAT 根据副表排序

Question

有没有一种方法可以根据辅助表对 GROUP_CONCAT 进行排序？

我有 3 个表设置类似于:

users          usersPrivilegeGroups     privilegeGroupsPriority
-----------    ---------------------    -----------------------
userid PK      groupName FK             groupName PK
username       userid FK                priority int
               active

使用以下内容，我检索了匹配用户的数据，并将相关的 usersPrivilegeGroups.groupName 连接到一个字段中:

SELECT
    users.*,
    GROUP_CONCAT(privList.groupName separator ',') AS privilegeGroups
FROM users
    LEFT JOIN usersPrivilegeGroups privList
    ON (privList.userid = users.userid AND privList.active = 1)
WHERE users.userid = ...

有没有一种方法可以根据 privilegeGroupsPriority.priority 按升序对匹配的组(privGroups)进行排序？

Answer 1

您需要JOIN 优先级表并执行ORDER BY GROUP_CONCAT 中的优先级值:

SELECT
    users.*,
    GROUP_CONCAT(privList.groupName separator ',' 
                 ORDER BY priority.priority ) AS privilegeGroups
FROM users

    LEFT JOIN usersPrivilegeGroups privList
    ON (privList.userid = users.userid AND privList.active = 1)

    LEFT JOIN privilegeGroupsPriority priority
    ON privList.groupName = priority.groupName

WHERE users.userid = ...