mysql - 如何在同一选择查询中将一个表的值交换到另一个表的值？

Question

我的数据库由两个表组成

用户表

+---------+----------+---------------+-----------+
| user_id | username | password_hash | user_type |
+---------+----------+---------------+-----------+
| 1       | Admin    | hash          | 0         |
| 2       | Student  | hash          | 1         |
| 3       | Teacher  | hash          | 2         |
+---------+----------+---------------+-----------+

门票表

+-----------+---------+-----------------+----------------+--------------------+---------------+-------------+
| ticket_id | user_id | ticket_category | ticket_summary | ticket_description | ticket_status | assignee_id |
+-----------+---------+-----------------+----------------+--------------------+---------------+-------------+
| 1         | 2       | Network         | Test Network   | Lorem Ipsum        | 1             | 1           |
| 2         | 3       | Printer         | Test Printer   | Lorem Ipsum        | 1             |             |
+-----------+---------+-----------------+----------------+--------------------+---------------+-------------+

tickets.user_id和tickets.assignee_id都是users.user_id的外键

我使用以下 select 语句查询这些表

SELECT username AS `Opened By`, assignee_id AS `Assigned To`, ticket_category AS `Category`, ticket_summary AS `Summary`, ticket_description AS `Description`, IF(ticket_status = 1,'Open','Closed') AS `Status` FROM tickets INNER JOIN users ON tickets.user_id = users.user_id;

这会构建一个像这样的数据集

+-----------+-------------+----------+--------------+-------------+--------+
| Opened By | Assigned To | Category | Summary      | Description | Status |
+-----------+-------------+----------+--------------+-------------+--------+
| Student   | 1           | Network  | Test Network | Lorem Ipsum | Open   |
| Teacher   |             | Printer  | Test Printer | Lorem Ipsum | Open   |
+-----------+-------------+----------+--------------+-------------+--------+

在我的 select 语句中，我想用用户表中对应的用户名替换 allocateee_id 数字。

例如，第一个工单 (ticket_id = 1) 的 allocateee_id 为 1，我希望“分配给”列显示用户名“Admin”而不是 1

如何修改我的选择查询来实现此目的？

Answer 1

只需再次加入 assignee_id 的用户表即可。请注意，它需要是LEFT JOIN，以防没有人分配给该工单。您可能还想在 u2.username 上使用 COALESCE 来输出空白而不是 (null) (即 COALESCE(u2.用户名，''))。 SQLFiddle .

SELECT u1.username AS `Opened By`, 
       u2.username AS `Assigned To`, 
       ticket_category AS `Category`, 
       ticket_summary AS `Summary`, 
       ticket_description AS `Description`, 
       IF(ticket_status = 1,'Open','Closed') AS `Status` 
FROM tickets 
JOIN users u1 ON tickets.user_id = u1.user_id
LEFT JOIN users u2 ON tickets.assignee_id = u2.user_id

示例数据的输出:

Opened By   Assigned To   Category   Summary       Description   Status
Student     Admin         Network    Test Network  Lorem Ipsum   Open 
Teacher     (null)        Printer    Test Printer  Lorem Ipsum   Open