oracle SELECT Sql Query 连接同一个表两次
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9802347/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SELECT Sql Query joins the same table twice
提问by user1283041
I have two tables like below table 1:emp
我有两个表，如下表 1：emp
Emp ID SupervisorID
001 005
002 001
003 004
004 005
table 2:empdetails
表 2：emp 详细信息
ID Name
001 John
002 Krish
003 Ramesh
004 Smith
005 Ranga
I need a sql query to display ID name from table 1 in two columns Employee and its Supervisor as shows result below
我需要一个 sql 查询来在两列员工及其主管中显示表 1 中的 ID 名称，如下所示
Result:
结果：
Emp Supervisor
John ranga
Krish John
Ramesh Smith
Smith prasad
回答by
Try with following query:
尝试使用以下查询：
SELECT ed1.Name AS 'ID',ed2.Name AS 'Name' FROM emp e
JOIN empdetails ed1 ON e.EmpID=ed1.ID
JOIN empdetails ed2 ON e.SupervisorID=ed2.ID
It will take the result from emptable and try to get join with empdetails....
它将从emp表中获取结果并尝试加入empdetails....
回答by APC
It is perfectly simple to join the same table twice: just use different table aliases to distinguish the instances. In the following example I use an outer join to get the supervisor name, as every employee may not have a supervisor (there ought to be one person in the hierarchy, the ultimate boss, who doesn't have one).
将同一个表连接两次非常简单：只需使用不同的表别名来区分实例。在下面的示例中，我使用外部联接来获取主管名称，因为每个员工可能没有主管（层次结构中应该有一个人，即最终的老板，他没有）。
select ed.name as emp_name
, sup.name as supervisor
from emp e
inner join empdetails ed on ( e.id = ed.id )
left join empdetails sup on ( e.supervisorid = sup.id )
