mysql - 如何检查一对多映射表中是否存在特定映射

Question

我有一个表，该表维护一个 EMPLOYEE_ID 到可以分配给员工的一个或多个 ROLE_ID 的映射。 ROLE_ID 是 ROLE 表的主键。

现在，我正在尝试查找特定员工是否是团队负责人 (ROLE_ID = 2)。也就是说，本质上是试图查找映射表中是否存在 (EMPLOYEE_ID, 2) 的特定映射组合。

目前，我正在使用以下查询来实现此目的:

SELECT E.NAME AS `EMPLOYEE_NAME`, 
 EXISTS( SELECT 1 FROM `EMPLOYEE_ROLE` WHERE 
   (`EMPLOYEE_ROLE`.`EMPLOYEE_ID` = `E`.`EMPLOYEE_ID`)
   AND (`EMPLOYEE_ROLE`.`ROLE_ID` = 2)) AS `IS_TEAM_LEADER`
 -- Assume some other column shall be selected from ER table, 
 -- hence necessitating the JOIN on ER
FROM EMPLOYEE E
JOIN EMPLOYEE_ROLE ER ON (ER.EMPLOYEE_ID = E.EMPLOYEE_ID)
GROUP BY E.EMPLOYEE_ID;

虽然这似乎可以完成工作，但我正在寻找一种更有效的方法，因为当前形式的子查询似乎是多余的。不确定它是否相关，但可以使用 FIND_IN_SET 或某些此类函数吗？

谁能提出解决方案，因为我对性能最佳的方法感兴趣？

编辑 1:我有意使用了 JOIN EMPLOYEE_ROLE，目的是也可以从 ER 表中选取其他列。所以，我正在寻找优化子查询，同时保持连接不变。因此，声明“当前形式的当前子查询似乎是多余的”。

SQLFiddle:http://sqlfiddle.com/#!9/2aad3/5

Answer 1

使用 exists 子查询或使用 join，但不应在一个查询中同时使用两者。

我会使用连接方法，因为在必要时很容易获得角色相关数据:

SELECT E.NAME AS `EMPLOYEE_NAME`, 
FROM EMPLOYEE E
INNER JOIN EMPLOYEE_ROLE ER ON (ER.EMPLOYEE_ID = E.EMPLOYEE_ID)
WHERE ER.ROLE_ID=2;

如果您需要一个包含所有员工的列表，其中有一个字段指示该员工是否是 IS 领导者，请使用左连接而不是内部连接:

SELECT DISTINCT E.NAME AS `EMPLOYEE_NAME`, IF(ER.ROLE_ID IS NULL, 'NOT IS Leader','IS Leader') AS IsISLeader
FROM EMPLOYEE E
LEFT JOIN EMPLOYEE_ROLE ER ON ER.EMPLOYEE_ID = E.EMPLOYEE_ID AND ER.ROLE_ID=2;