时间戳

首页 > 解决方案 > 查询以显示员工和经理

sql - 查询以显示员工和经理

问题描述

我必须编写一个 SQL Server 查询来显示员工的姓氏和他各自经理的姓名，如下所示。

John works for Robert
Jane works for David

这些是两个表的架构。

Employee Table

empno char (6)
firstname varchar(12)
midinit char(1)
lastname varchar(15)
workdept char(3)



Department Table 

deptno char(3)
deptname varchar(36)
mgrno char(6)
admrdept char (3)
location char(16)

预期输出：

Adamson works for Stern 
Brown works for Stern 
Jefferson works for Pulaski 
Johnson works for Pulaski 
Jones works for Stern 
Lutz works for Stern 
Marino works for Pulaski 
Monteverde works for Pulaski 
Natz works for Kwan

我试过这段代码，但它不正确

SELECT Concat(e.firstnme ," works for ", (select firstnme from Employee where mgrno = empno)) as Hierarchy
from Employee e
join Department d on e.workdept = d.deptno
order by lastname

详细架构：

架构

标签： sqlsql-servertsql

解决方案

我想像这样简单的事情应该这样做

SELECT e.LastName + ' works for ' + e2.LastName
from  Employee e 
  join Department d on e.workdept = d.deptno
  join Employee e2 on d.mgrno = e2.empno

这个怎么运作：

  • 员工 e 与部门 d 的加入将使您在部门中获得准确的 1 行
  • 然后从这个部门返回到员工 e2，这将从员工 e2 返回确切的 1 名员工（老板）
  • 现在你在 e 中有员工，在 e2 中有老板

我用这个测试过

declare @Employee table (empno char(6), lastname varchar(15), workdept char(3))
declare @Department table (deptno char(3), mgrno char(6))

insert into @Employee (empno, lastname, workdept) values 
  ('123456', 'Adamson', 'dp1'), 
  ('123457', 'Brown', 'dp1'), 
  ('123458', 'Jefferson', 'dp2'), 
  ('123459', 'Johnson', 'dp2'),
  ('123460', 'Jones', 'dp1'),
  ('123461', 'Lutz', 'dp1'),
  ('123462', 'Marino', 'dp2'),
  ('123463', 'Monteverde', 'dp2'),
  ('123464', 'Natz', 'dp3'),
  ('123465', 'Stern', 'dp1'),
  ('123466', 'Pulaski', 'dp2'),
  ('123467', 'Kwan', 'dp3')

insert into @Department (deptno, mgrno) values ('dp1', '123465'), ('dp2', '123466'), ('dp3', '123467')

SELECT e.LastName + ' works for ' + e2.LastName
from  @Employee e 
  join @Department d on e.workdept = d.deptno
  join @Employee e2 on d.mgrno = e2.empno

结果

Adamson works for Stern
Brown works for Stern
Jefferson works for Pulaski
Johnson works for Pulaski
Jones works for Stern
Lutz works for Stern
Marino works for Pulaski
Monteverde works for Pulaski
Natz works for Kwan
Stern works for Stern
Pulaski works for Pulaski
Kwan works for Kwan

编辑

如果您想省略老板，请将此 where 子句添加到查询中

where  e.empno not in (select mgrno from @Department)

或替代此 where 子句

where  e.empno <> e2.empno

推荐阅读