SQL表输出 [英] Sql table output

问题描述

我得到了两个表:Employee和Staff

I've been given two tables: Employee and Staff

|ID| Name  |Surname|Postion|           |EmpID|ManID|
----------------------------           ------------- 
|1 |Scrooge|McDuck |Manager|           |3    |1    |
|2 |Daisy  |Duck   |Manager|           |7    |1    |
|3 |Donald |Duck   |Support|           |6    |2    | 
|4 |Minny  |Mouse  |Support|           |4    |2    |
|5 |Mickey |Mouse  |Support|           |2    |1    |
|6 |Goofy  |       |Support|           |1    |2    |
|7 |Pluto  |       |Support|           |5    |2    |
|8 |Huey   |Duck   |Support|
|9 |Dewey  |Duck   |Support|
|10|Louie  |Duck   |Support|

要求我提供一条将产生以下输出的sql语句

I am asked for a sql statement that will produce following output

| Name  |Surname|Postion|Manager Name|Manager Positon|          
------------------------            
|Donald |Duck   |Support|Scrooge     |Manager         
|Pluto  |       |Support|Scrooge     |Manager         
|Goofy  |       |Support|Daisy       |Manager        
|Minny  |Mouse  |Support|Daisy       |Manager       
|Daisy  |Duck   |Support|Scrooge     |Manager        
|Scrooge|McDuck |Manager|Daisy       |Manager        
|Mickey |Mouse  |Manager|Daisy       |Manager      

到目前为止，我已经创建了一个视图，该视图显示所有内容，但不创建两个新列，并且不提供管理者的姓名.

So far I've created a view which displays everything but doesn't create the two new columns and doesn't give the names of the managers.

CREATE VIEW example
AS
    SELECT * FROM Employee
    JOIN StaffLink
    ON Employee.ID = StaffLink.EmpID

    SELECT 
GO

请指导我.

推荐答案

SQL:

SELECT Staff.EmpID, Manager.Name AS `Manager Name`, 
       Manager.Position AS `Manager Position`, 
       Employee.Name, Employee.Surname, Employee.Position
FROM (Staff, Employee AS Manager, Employee)
WHERE Manager.ID = Staff.ManID AND
      Employee.ID = Staff.EmpID;

我相信它现在已解决

这篇关于SQL表输出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！