在MySQL中对同一表使用子查询 [英] Using subquery for the same table in 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中对同一表使用子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！