>我有一个看起来像这样的表格:
username manager
emp001 emp006
emp002 emp006
emp003 emp006
emp004 emp007
emp005 emp007
emp006 emp009
emp007 emp010
emp008 emp010
emp009 emp011
emp010 null
emp011 null
我想创建一个选择查询,它像这样返回第三列 - 新列基于其中获取经理项并引用第一列并获取该行的经理。
username manager director
emp001 emp006 emp009
emp002 emp006 emp009
emp003 emp006 emp009
emp004 emp007 emp010
emp005 emp007 emp010
emp006 emp009 emp011
emp007 emp010 null
emp008 emp010 null
emp009 emp011 null
emp010 null null
emp011 null null
我迷失在这一点上。
感谢您的任何建议。我已经尝试了选择和连接的各种组合,但似乎没有什么能满足我的需求。
谢谢!
这应该可以做你想要的:
Select E.UserName, E.Manager, M.Manager As Director
From YourTable E
Left Join YourTable M On E.Manager = M.UserName
select e1.id, e1.managerId, e2.managerId
from employees e1
left join employees e2 on (e1.manager = e2.employee)
使用另一个别名连接同一个表:
select a.username,a.manager,b.manager as director
from TableName a left join
TableName b on a.manager=b.username
结果:
username manager director
emp001 emp006 emp009
emp002 emp006 emp009
emp003 emp006 emp009
emp004 emp007 emp010
emp005 emp007 emp010
emp006 emp009 emp011
emp007 emp010 (null)
emp008 emp010 (null)
emp009 emp011 (null)
emp010 (null) (null)
emp011 (null) (null)
在SQL 小提琴中查看结果