MYSQL union All with condition if condition matched choose first else 2nd 条件匹配

Question

在我的例子中，它是权限和角色，所以我将结果与 union all 结合起来我只想检查条件，如果用户权限值 = 0，然后选择其他我正在尝试的其他条件

SELECT username, orgId, uid, pid, perm FROM (
    SELECT users.username, users.orgId, user_roles.uid, role_perms.pid, role_perms.value AS perm
                    FROM user_roles INNER JOIN role_perms
                    ON (user_roles.rid = role_perms.rid) INNER JOIN user_users ON(user_roles.uid = users.uid) WHERE role_perms.pid = 9 AND users.orgId = 2 AND users.username IS NOT NULL AND users.username != ''
                    UNION ALL 
    SELECT users.username, users.orgId, user_perms.uid, user_perms.pid, user_perms.value AS perm
                    FROM user_perms INNER JOIN users ON(user_perms.uid = users.uid) WHERE user_perms.pid = 9 AND user_users.orgId = 2 AND user_users.username is not null and user_users.username != '') AS combPerm; 

它给出的结果好像 user_perm 表中的一个用户的权限被拒绝，但该用户也有包含特定权限的角色

username | orgId | uid | pid | perm
abc@a.com  2       11    9     0
abc@a.com  2       11    9     1
xyz@a.com  2       91    9     1
dvc@a.com  2       88    9     1

结果我只想要 abc@a.com 一次，如果它从 user_perms 表中获得 perm 0 并且所有其他记录相同，所需的结果如下

 username | orgId | uid | pid | perm
abc@a.com  2       11    9     0
xyz@a.com  2       91    9     1
dvc@a.com  2       88    9     1

Answer 1

你可以只使用 min(perm) 并在你的查询中添加一个 group by

SELECT username, orgId, uid, pid, min(perm) FROM (

 -----the rest of the query

) AS combPerm group by username, orgId, uid, pid;