我有一个包含以下数据的表：
+-----------------+--------------+----------------------+------------+--------------------+-----------+------+---------+---------+-----+------------------------+---------------------+
| notification_id | from_user_id | from_user_auth_level | to_user_id | to_user_auth_level | status_id | type | subject | message | url | timestamp_inserted_utc | timestamp_read_utc |
+-----------------+--------------+----------------------+------------+--------------------+-----------+------+---------+---------+-----+------------------------+---------------------+
| 1 | NULL | NULL | 1 | 1 | 0 | 2 | test | test | url | 2010-10-10 00:00:00 | 2011-10-10 00:00:00 |
| 2 | 2 | 5 | 1 | 1 | 0 | 2 | test | test | url | 2010-10-10 00:00:00 | 2011-10-10 00:00:00 |
| 3 | 3 | 5 | 1 | 1 | 0 | 2 | test | test | url | 2010-10-10 00:00:00 | 2011-10-10 00:00:00 |
| 4 | 2295 | 4 | 1 | 1 | 0 | 2 | test | test | url | 2010-10-10 00:00:00 | 2011-10-10 00:00:00 |
| 5 | 10 | 1 | 1 | 1 | 0 | 2 | test | test | url | 2010-10-10 00:00:00 | 2011-10-10 00:00:00 |
+-----------------+--------------+----------------------+------------+--------------------+-----------+------+---------+---------+-----+------------------------+---------------------+
然后我还有其他一些表，例如“用户”，“公司”，“组织”等。
我需要能够获取每个通知的用户名，性别和图像（基于from_user_id和from_user_auth_level）。
但是问题出在以下事实上：该信息位于不同的位置，具体取决于user_auth_level是什么。
例如：如果我的用户是“常规”用户，则他的auth_level将为1。该图像将驻留在我的“用户”表中，并且性别适用。
但是，如果用户的auth_level == 5，则表示他是一个组织。在这种情况下，性别不适用，并且图像位于“组织”表中，需要通过用户将其链接到user_roles，然后再链接到组织。
对于每种用户类型，这种情况都是如此，他们都需要不同的联接。
我已经创建了一个有效的查询，但这在所有地方都使用了UNION，并且我读到它并不是出于性能原因的最佳选择，所以我希望有人可以指导我在性能上改进此查询：
SELECT n.*, NULL as username, NULL as gender, NULL as picture
FROM notification as n
WHERE n.from_user_auth_level IS NULL
AND n.to_user_id = $userid
UNION
SELECT n.*, u.username, u.gender as gender, u.profile_picture as picture
FROM notification as n
LEFT JOIN users AS u ON n.from_user_id = u.user_id
WHERE n.from_user_auth_level = 1
AND n.to_user_id = $userid
UNION
SELECT n.*, u.username, NULL as gender, c.logo as picture
FROM notification as n
LEFT JOIN users AS u ON n.from_user_id = u.user_id
LEFT JOIN user_companies AS uc on u.user_id = uc.user_id
LEFT JOIN company as c on uc.company_id = c.company_id
WHERE n.from_user_auth_level = 4
AND n.to_user_id = $userid
UNION
SELECT n.*, u.username, NULL as gender, o.logo as picture
FROM notification as n
LEFT JOIN users AS u ON n.from_user_id = u.user_id
LEFT JOIN user_roles as ur on u.user_id = ur.user_id
LEFT JOIN organization as o on ur.org_id = o.org_id
WHERE n.from_user_auth_level = 5
AND n.to_user_id = $userid
UNION
SELECT n.*, u.username, NULL as gender, o.logo as picture
FROM notification as n
LEFT JOIN users AS u ON n.from_user_id = u.user_id
LEFT JOIN user_roles as ur on u.user_id = ur.user_id
LEFT JOIN organization as o on ur.org_id = o.org_id
WHERE n.from_user_auth_level = 7
AND n.to_user_id = $userid
UNION
SELECT n.*, u.username, NULL as gender, NULL as picture
FROM notification as n
LEFT JOIN users AS u ON n.from_user_id = u.user_id
WHERE n.from_user_auth_level = 9
AND n.to_user_id = $userid"
得到此结果后，我将使用PHP根据timestamp_inserted_utc对结果进行排序，因为用UNION无法获得正确的结果。
最佳答案
我将使用通知表作为基础，并使用条件外部联接作为：
select
n.*,t1.gender, t2.orgNo
from
notifications n
left outer join table1 t1 on (n.auth=1 and more join)
left outer join table2 t2 on (n.auth=2 and more..)
您将有更多的列，但是它们的名称会有意义，并且您可以在应用程序级别合并。
关于mysql - 改进查询以根据值联接其他表而无需合并，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/59789496/