在MySQL中对同一表使用子查询

问题描述

我有一个名为Staff的表，该表具有以下字段:idStaff, Name, Phone, Email, SupervisorId.

I have a table called Staff which has the following fields: idStaff, Name, Phone, Email, SupervisorId.

SuervisorId是该工作人员主管的idStaff.

The SuervisorId is the idStaff of that staff member's supervisor.

我想显示所有职员的名单及其基本信息(姓名，电子邮件等)以及主管的姓名.

I want to show the list of all staff members with their basic info (Name, Email etc) as well as the name of their supervisor.

是这样的:

select idStaff
     , Name
     , Email
     , Phone
     , (select Name from Staff where idStaff = SupervisorId) as SupervisorName
  from Staff
 order
    by Name ASC

查询不起作用.我尝试联接两个表，但对如何从联接中的子查询中获取名称感到困惑.

The query does not work. I tried joining the two tables but I am confused on how to get the Name from the subquery in the join.

 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

或者您可以做类似....

or you could have done something like....

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

这篇关于在MySQL中对同一表使用子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！