如何在一个列中连接一个不同的列，在另一个中连接多个列？
如何在一个列中连接一个不同的列，在另一个中连接多个列？
提问于 2015-05-29 09:39:41
下面是两个示例表(Issues和Users)：
╔═════════════╦══════════╗
║ reporter_id ║ agent_id ║
╠═════════════╬══════════╣
║ asdf ║ jfid ║
║ asde ║ ║
║ djij ║ dije ║
╚═════════════╩══════════╝
╔══════╦════════════╦═══════════╗
║ id ║ first_name ║ last_name ║
╠══════╬════════════╬═══════════╣
║ asdf ║ Magnus ║ Nilsson ║
║ sdfe ║ Thomas ║ Keller ║
║ dije ║ Daniel ║ Humm ║
╚══════╩════════════╩═══════════╝期望的结果：
╔═════════════╦══════════╦══════════════════╦═════════════════╦═════════════════════╦════════════════════╗
║ reporter_id ║ agent_id ║ agent_first_name ║ agent_last_name ║ reporter_first_name ║ reporter_last_name ║
╠═════════════╬══════════╬══════════════════╬═════════════════╬═════════════════════╬════════════════════╣
║ asdf ║ idfj ║ john ║ lee ║ david ║ lee ║
║ aiej ║ ║ mike ║ dee ║ ║ ║
║ isao ║ idje ║ sarah ║ lee ║ sarah ║ dee ║
╚═════════════╩══════════╩══════════════════╩═════════════════╩═════════════════════╩════════════════════╝什么样的join可以处理这个问题？对于上下文，这将在Redshift (postgres)上。我在想这个。
select
???
from
issues join users
on issues.reporter_id = users.id AND issues.agent_id = users.id我不清楚select子句，我只略微确信我的from子句是正确的。
回答 2
Stack Overflow用户
发布于 2015-05-29 09:48:04
您需要连接User表两次。另外，您希望的结果数据与表数据不匹配。
SELECT I.reporter_id,
I.agent_id,
U1.first_name AS agent_first_name,
U1.last_name AS agent_last_name,
U2.first_name AS reporter_first_name,
U2.last_name AS reporter_last_name
FROM issues I
INNER JOIN users U2
ON U2.id = I.reporter_id
LEFT JOIN users U1
ON U1.id = I.agent_idStack Overflow用户
发布于 2015-05-29 09:46:43
您可以将同一个表联接两次。只需使用不同的别名，如下所示：
select
issues.reporter_id
,issues.agent_id
,a.first_name as agent_first_name
,a.last_name as agent_last_name
,r.first_name as reporter_first_name
,r.last_name as reporter_last_name
from
issues left join users r
on issues.reporter_id = r.id
left join users a
on issues.agent_id = a.id此外，由于agent_id (可能还有reporter_id)的值为null，因此需要使用左连接
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/30519791
复制相关文章
相似问题
腾讯云开发者
