SQL 查询并返回两行合二为一

Question

我有三个表：

• POST： 有关职位的信息

POST_ID | REPORTS_TO
--------+-----------
1       | 4
2       | 1
3       | 1
4       | NULL

• EMPLOYEE：记录哪个人在哪个岗位

EMPLOYEE_ID | POST_ID | PERSON_ID
------------+---------+----------
1           | 1       | 1
2           | 2       | 2
3           | 3       | 3
4           | 4       | 4

• PERSON： 有关此人的信息

PERSON_ID | NAME
----------+-----
1         | BOB
2         | JIM
3         | FRANK
7         | PHIL

我想将结果返回为：

WorkerName | SupervisorName
-----------+---------------
BOB        | PHIL
JIM        | BOB
FRANK      | BOB
PHIL       | (NULL)

我已经尝试过这个，但它只返回 7 行。我需要将下面的结果按postID分组，但随后我丢失了一半的值：

SELECT First_Name
FROM post
JOIN employee
ON employee.PostID = post.Supervisor_PostID
OR employee.PostID = post.PostID
JOIN person
ON person.PersonID = employee.PersonID

Answer 1

您需要使用posts 加入person表两次：一次用于工作线程(通过桥表employee访问(，另一个用于主管：

select pe1.name WorkerName, pe2.name SupervisorName
from post po
inner join employee em on em.post_id = po.post_id
inner join person pe1 on pe1.person_id = em.person_id
inner join person pe2 on pe2.person_id = po.reports_to