子查询连接同一个表

vwkv1x7d 于 2021-06-24 发布在 Mysql

关注(0)|答案(2)|浏览(273)

所以我有一个雇员表：

我试图创建一个查询来显示这个雇员的所有信息，但是我有点被这个雇员的报告难住了。我要显示的是存储在此列中的员工id的名和姓（例如joe bloggs id 1，向员工joanne blog id 50报告）
这需要一个连接还是一个简单的选择。我尝试时，数据库的链接出现问题。。

SELECT employee.*, departments.Department_Name, 
            jobTitle.Job_Title,
            (SELECT manager.First_Name, manager.Last_Name
                FROM Employee manager
                INNER JOIN employee AS employeeReportsTo
          ON manager.Employee_Reports_To = manager.Employee_ID   
            ) AS Reports_To
            FROM Employee employee
            LEFT JOIN Departments AS departments
                ON departments.Departments_ID = employee.Departments_ID
            LEFT JOIN Job_Title AS jobTitle
                ON jobTitle.Job_Title_ID = employee.Job_Title_ID
             ORDER BY `employee`.`Record_Active` DESC,
                     `employee`.`First_Name` ASC

如有任何帮助，我们将不胜感激！

mysql Join subquery

来源：https://stackoverflow.com/questions/49998128/subquery-joining-same-table

2条答案

按热度按时间

btxsgosb1#

查看您的代码，您可以使用两个时间雇员（别名e1和e2），一个用于雇员，另一个用于相关经理

SELECT e1.*
  , departments.Department_Name
  , jobTitle.Job_Title
  , e2.First_Name
  ,e2.Last_Name
  FROM Employee e1
  INNER JOIN Employee e2 ON e1.Employee_Reports_To = e2.Employee_ID
  LEFT JOIN Departments AS departments
      ON departments.Departments_ID = e1.Departments_ID
  LEFT JOIN Job_Title AS jobTitle
      ON jobTitle.Job_Title_ID = e1.Job_Title_ID
   ORDER BY e1.`Record_Active` DESC,
           e1.`First_Name` ASC

赞(0）回复(0）举报 2021-06-24

5vf7fwbs2#

尝试此解决方案：

SELECT emp.*,
       manager.First_Name, 
       manager.Last_Name,
       departments.Department_Name, 
       jobTitle.Job_Title
FROM Employee emp
INNER JOIN Employee manager
ON emp.Employee_Reports_To = manager.Employee_ID
LEFT JOIN Departments AS departments
ON departments.Departments_ID = emp.Departments_ID
LEFT JOIN Job_Title AS jobTitle
ON jobTitle.Job_Title_ID = emp.Job_Title_ID
ORDER BY emp.Record_Active DESC,
             emp.First_Name ASC

用它总是更好的 Join 而不是使用 Sub Query .
编辑时间：
自列 Employee_Reports_To 可以是 null 所以最好用 LEFT JOIN 而不是 INNER JOIN . 这样地：

SELECT emp.*,
           manager.First_Name, 
           manager.Last_Name,
           departments.Department_Name, 
           jobTitle.Job_Title
FROM Employee emp
LEFT JOIN Employee manager                     --Changed Join here
ON emp.Employee_Reports_To = manager.Employee_ID
LEFT JOIN Departments AS departments
ON departments.Departments_ID = emp.Departments_ID
LEFT JOIN Job_Title AS jobTitle
ON jobTitle.Job_Title_ID = emp.Job_Title_ID
ORDER BY emp.Record_Active DESC,
         emp.First_Name ASC

赞(0）回复(0）举报 2021-06-24

我来回答

子查询连接同一个表

2条答案

相关问题

热门标签

最新问答