mysql - 将 3 个独立的列合并为一列，以便在 IN 子句中使用

Question

这纯粹是一个效率问题。我有两张 table :

根据用户在实体中的位置存储用户 ID

id     | supervisor     | manager      | worker
-------+----------------+--------------+---------------
1      | 12             | 15           | 32
2      | 12             | 42           | 22

还有带有用户信息的:

id     | name           | email
-------+----------------+---------------------
12     | Bob            | bob@example.com
15     | Dave           | dave@example.com

是否可以仅使用一个 IN 子句(而不是 3 个)来完成此操作？

SELECT `name`, `email` 
FROM `tbl_users` 
WHERE `id` IN 
(SELECT `supervisor` FROM `tbl_warehouse` WHERE `id` = 'WAREHOUSEID') 
OR `id` IN 
(SELECT `manager` FROM `tbl_warehouse` WHERE `id` = 'WAREHOUSEID')
OR `id` IN 
(SELECT `worker` FROM `tbl_warehouse` WHERE `id` = 'WAREHOUSEID')

我尝试了一些方法，尝试将 supervisor、manager、worker 列合并为一列，以便 IN 子句有效，但没有成功

Answer 1

您可以在子查询中使用EXISTS并使用IN:

select `name`,
    `email`
from `tbl_users` u
where exists (
        select 1
        from `tbl_warehouse` w
        where w.id = 'WAREHOUSEID'
            and u.id in (w.supervisor, w.manager, w.worker)
        )

您也可以使用 JOIN 来实现此目的:

select u.name, u.email
from tbl_users u
join tbl_warehouse w on u.id in (w.supervisor, w.manager, w.worker)
where w.id = 'WAREHOUSEID'