mysql - 如何显示某个worker MYSQL的supervisor的名字

Question

显示 McLester 主管的姓名。使用标准语法

select sup.lastname "Supervisor's Name"
from staff e, staff sup
where e.supervisorno = sup.staffno and lastname = 'McLester';

这是表格我想我需要连接员工号和主管号。但这似乎不起作用。有什么想法吗？

这是表格 https://prnt.sc/on3er7 的屏幕截图

ERROR 1052 (23000): Column 'lastname' in where clause is ambiguous

要重现问题:

Drop Table Staff;

Create Table Staff
 (StaffNo  varchar(6) Primary Key,
  Lastname  varchar(30),
  Firstname varchar(25),
  Hire_date  date,
  Location  varchar(30),
  SupervisorNo  varchar(6),
  Salary  numeric(8,2),
  Commission  numeric(4,2));

-- Populate Staff Table ------------------------------------------

Insert Into Staff
Values('000001','Zambini','Rick',Date('1980-2-15'),'LOS ANGELES','000000',6000.00,5.00);
Insert Into Staff
Values('000003','Vidoni','Cheryl',Date('1980-3-6'),'NEW YORK','000000',5780.00,5.00);
Insert Into Staff
Values('000004','Coudray','Sandy',Date('1980-6-6'),'LOS ANGELES','000001',6237.00,5.00);
Insert Into Staff
Values('000006','Thomas','Pat',Date('1991-1-8'),'NEW YORK','000003',5875.00,5.00);
Insert Into Staff
Values('000008','McLester','Debbie',Date('1981-4-12'),'LOS ANGELES','000001',4792.00,5.00);
Insert Into Staff
Values('000011','Michaels','Delores',Date('1982-5-5'),'CHICAGO','000012',4927.00,7.00);
Insert Into Staff
Values('000012','Charles','Ted',Date('1983-2-2'),'CHICAGO','000000',5945.00,5.00);
Insert Into Staff
Values('000013','Marin','Mark',Date('1983-6-5'),'LOS ANGELES','000001',4802.00,11.00);
Insert Into Staff
Values('000015','Roddick','Mary',Date('1984-2-13'),'NEW YORK','000003',5493.00,8.00);
Insert Into Staff
Values('000016','Long','Nicole',Date('1984-8-18'),'NEW YORK','000003',5190.00,7.00);
Insert Into Staff
Values('000019','Rolfes','Chuck',Date('1984-9-9'),'LOS ANGELES','000001',4586.00,6.00);
Insert Into Staff
Values('000020','Sanders','Kathy',Date('1985-3-23'),'CHICAGO','000012',3783.00,5.00);


COMMIT;

Answer 1

只需使用正确的联接和列别名:

select sup.lastname "Supervisor's Name" 
from Staff e inner join Staff sup 
on e.supervisorno = sup.staffno 
where e.lastname = 'McLester';

请参阅demo .
结果:

| Supervisor's Name |
| ----------------- |
| Zambini           |