我有一张桌子叫Staff
它有以下字段:idStaff, Name, Phone, Email, SupervisorId
.
The SuervisorId
is the idStaff
该工作人员的主管。
我想显示所有员工的列表及其基本信息(姓名、电子邮件等)以及主管的姓名。
所以像这样:
select idStaff
, Name
, Email
, Phone
, (select Name from Staff where idStaff = SupervisorId) as SupervisorName
from Staff
order
by Name ASC
查询不起作用。我尝试连接这两个表,但我对如何从连接中的子查询获取名称感到困惑。
select idStaff
, Name
, Phone
, Email
from Staff a
inner
join Staff b
on a.idStaff = b.SupervisorId
order
by Name ASC
也许是这样的......
select s1.idStaff
, s1.Name
, s1.Email
, s1.Phone
, s2.Name as SupervisorName
from Staff s1
LEFT JOIN Staff s2 ON s1.SupervisorId = s2.idStaff
order
by s1.Name ASC
或者你可以做类似的事情......
select s.idStaff
, s.Name
, s.Email
, s.Phone
, (select top 1 m.Name from Staff m
where s.SupervisorId = m.idStaff) as SupervisorName
from Staff s
order by s.Name ASC
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)