在不同的列上连接两次相同的表

I've got a user table and a complaint table.

我有一张用户表和投诉表。

The complaint table has the following structure:

投诉表具有以下结构：

[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 complainers and complaint-resolvers are located in table 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 _by columns show usernames rather than IDs.

但我不知道怎么写它所以_by列显示用户名而不是ID。

4 个解决方案

#1

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

#2

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.

如果您有任何性能问题，则查询优化器通常会重写子查询。

#3

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的东西）

#4

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

#1

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

#2