我的数据库由两个表组成
用户表
+---------+----------+---------------+-----------+
| 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\u id和tickets.assignee\u id都是users.user\u 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语句中,我想用users表中相应的用户名替换受让人的id号。
例如,第一张票证(票证id=1)的受让人id为1,我希望assigned to列显示用户名“admin”而不是1
如何修改select查询来实现这一点?
2条答案
按热度按时间anauzrmj1#
只需再次加入users表
assignee_id
. 注意它必须是一个LEFT JOIN
以防没有人被分配到票上。您也可以使用COALESCE
在u2.username
输出空白而不是(null)
(即。COALESCE(u2.username, '')
). 一把小提琴。示例数据的输出:
qojgxg4l2#