时间戳

首页 > 解决方案 > 如何从连接中最右边的列中获取排名最高的值

mysql - 如何从连接中最右边的列中获取排名最高的值

问题描述

我正在尝试执行按用户角色排序的查询。用户有公司。用户在该公司担任角色。

表：

USER
id

USER_HAS_COMPANY_ROLES
user_id
company_role_id

COMPANY_ROLES
id
name

存在的公司角色是“管理员”或“成员”。用户可以具有“成员”角色或同时具有“成员”和“管理员”角色。

我想获取公司的所有用户，但按最重要的角色对用户进行排序。在这种情况下，最重要的角色是“管理员”。但是由于用户既可以是“成员”也可以是“管理员”，我需要先按重要性顺序对角色进行排序，然后选择最重要的，这样我才能正确执行我的 ORDER BY 子句。

我不能使用 company_role_id 进行排序，因为将来会添加更多角色，并且我们不能依赖 id 的最重要角色的顺序。

我尝试了几种过滤结果的方法，但似乎都没有

我想本质上加入 USER_HAS_COMPANY_ROLES 并按 COMPANY_ROLES 名称对这些角色进行排序，然后获取最高角色并将该最高角色作为 JOIN 语句的记录返回

SELECT users.email, company_roles.name FROM `users`
JOIN `user_has_company_roles`
ON `user_has_company_roles`.`user_id` = `users`.`id`
JOIN`company_roles`
ON `company_roles`.`id` = `user_has_company_roles`.`company_role_id`
WHERE `users`.`deleted_at` is null 
ORDER BY CASE
              WHEN company_roles.name = 'member' THEN 1
              WHEN company_roles.name = 'admin' THEN 0
         END

此语句有效，但我的测试数据中有 1 个管理员用户。此管理员用户同时具有“成员”和“管理员”角色，因此该用户将在结果中列出两次

我从上述查询中得到的结果是：

EMAIL            NAME
admin@aol.com    admin
user@aol.com     member
admin@aol.com    member

我正在寻找的结果是

EMAIL            NAME
admin@aol.com    admin
user@aol.com     member

标签： mysqlsqllaravel

解决方案

GROUP BY似乎是一个简单的方法

SELECT u.email,
       (CASE WHEN SUM(cr.name = 'admin') > 0 THEN 'admin'
             WHEN SUM(cr.name = 'user') > 0 THEN 'user'
        END) as best_role
FROM users u JOIN
     user_has_company_roles ucr
     ON ucr.user_id = u.id JOIN
     company_roles cr
     ON ucr.company_role_id = cr.id
GROUP BY u.id, u.email;

推荐阅读