sql - 查询显示部门名称经理部门名称

Question

我必须编写一个查询以从表中显示 Worker Department 及其 Manager Department。

一个部门不能管理自己，所有部门都应该与它的经理部门一起显示一次。

这是 Employee 和 Department 表的架构：

Employees:

empno char[6]

firstname varchar[12]

lastname varchar[15]

workdept char[3]

job char[9]

Department:

deptno char[3]

deptname varchar[36]

mgrno char[6]

admrdept char [3]

location char[16]

我是否错过了什么，因为我似乎做不到。

这是我期望的输出（工人部门和经理部门是别名）：

Worker Dept.                            Manager Dept.
Administration Systems                  Development Center
Development Center                      Spiffy Computer Service
Information Center                      Spiffy Computer Service
Manufacturing Systems                   Development Center
Planning                                Spiffy Computer Service Div
Support Services                        Spiffy Computer Service Div

我试过这个，但我找不到经理部门：

SELECT distinct d.deptname,  d.location , d.admrdept  
FROM Department d 
JOIN Employee e on d.deptno = workdept 

PS：我将第三列作为部门。根据上面查询的代码，我如何建立与部门名称的连接。

标签： sqltsqlsql-server-2008

Answer 1

根据给出的信息很难说，但我认为你想要：

select d.deptname as WorkerDept, md.deptname as ManagerDept
from Employee e
inner join Department d
   on (e.workdept = d.deptno)
inner join Department md
   on (d.admrdept = md.deptno)
where d.deptno != md.deptno