MySQL 在不同的列上两次加入同一个表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3201359/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
joining the same table twice on different columns
提问by Drew
I've got a usertable and a complainttable.
我有一张user桌子和一张complaint桌子。
The complainttable has the following structure:
该complaint表具有以下结构：
[opened_by] [complaint_text] [closed_by]
(user_id) (text) (user_id)
(user_id) (text) (user_id)
(user_id) (text) (user_id)
All users, both the complainersand complaint-resolversare located in table user.
所有用户，包括投诉者和投诉解决者都位于表中user。
How do I write a query to show the username for both columns?
如何编写查询以显示两列的用户名？
This gives me one:
这给了我一个：
SELECT user.username, complaint.complaint_text
FROM complaint
LEFT JOIN user ON user.user_id=complaint.opened_by
but I don't know how to write it so both _bycolumns show usernames rather than IDs.
但我不知道怎么写，所以两_by列都显示用户名而不是 ID。
回答by potatopeelings
SELECT
complaint.complaint_text,
A.username,
B.username
FROM
complaint
LEFT JOIN user A ON A.user_id=complaint.opened_by
LEFT JOIN user B ON B.user_id=complaint.closed_by
回答by Brian Hooper
I prefer sub-queries as I find them easier to understand...
我更喜欢子查询，因为我发现它们更容易理解......
SELECT (SELECT name
FROM user
WHERE user_id = opened_by) AS opener,
(SELECT name
FROM user
WHERE user_id = closed_by) AS closer,
complaint_text
FROM complaint;
Sub-queries are usually rewritten by the query optimiser, if you have any performance concerns.
如果您有任何性能问题，子查询通常由查询优化器重写。
回答by Dal Hundal
SELECT user1.username AS opened_by_username, complaint.complaint_text, user2.username AS closed_by_username
FROM user AS user1, complaint, user as user2
WHERE user1.user_id = complaint.opened_by
AND user2.user_id = complaint.closed_by
Join it again using an alias (thats what the user as user2 stuff is about)
使用别名再次加入它（这就是用户作为 user2 的内容）
回答by Kangkan
Use this query:
使用此查询：
SELECT opener.username as opened_by, complaint.complaint_text, closer.username as closed_by
FROM complaint
LEFT JOIN user as opener ON opener.user_id=complaint.opened_by
LEFT JOIN user as closer ON closer.user_id=complaint.closed_by
