我的表结构如下所示：
tbl.users tbl.issues
+--------+-----------+ +---------+------------+-----------+
| userid | real_name | | issueid | assignedid | creatorid |
+--------+-----------+ +---------+------------+-----------+
| 1 | test_1 | | 1 | 1 | 1 |
| 2 | test_2 | | 2 | 1 | 2 |
+--------+-----------+ +---------+------------+-----------+
基本上我想编写一个查询，该查询将以如下所示的结果表结束：
(results table)
+---------+------------+---------------+-----------+--------------+
| issueid | assignedid | assigned_name | creatorid | creator_name |
+---------+------------+---------------+-----------+--------------+
| 1 | 1 | test_1 | 1 | test_1 |
| 2 | 1 | test_1 | 2 | test_2 |
+---------+------------+---------------+-----------+--------------+
Mysql 目前看起来像这样：
SELECT
`issues`.`issueid`,
`issues`.`creatorid`,
`issues`.`assignedid`,
`users`.`real_name`
FROM `issues`
JOIN `users`
ON ( `users`.`userid` = `issues`.`creatorid` )
OR (`users`.`userid` = `issues`.`assignedid`)
ORDER BY `issueid` ASC
LIMIT 0 , 30
这会返回类似这样的内容：
(results table)
+---------+------------+-----------+-----------+
| issueid | assignedid | creatorid | real_name |
+---------+------------+-----------+-----------+
| 1 | 1 | 1 | test_1 |
| 2 | 1 | 2 | test_1 |
| 2 | 1 | 2 | test_2 |
+---------+------------+-----------+-----------+
谁能帮助我获得所需的结果表？
SELECT
IssueID,
AssignedID,
CreatorID,
AssignedUser.real_name AS AssignedName,
CreatorUser.real_name AS CreatorName
FROM Issues
LEFT JOIN Users AS AssignedUser
ON Issues.AssignedID = AssignedUser.UserID
LEFT JOIN Users AS CreatorUser
ON Issues.CreatorID = CreatorUser.UserID
ORDER BY `issueid` ASC
LIMIT 0, 30
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)
