我需要编写一个查询,列出所有员工的姓名(在1列中)和他们管理的个人数量。
我试过把计数和concat结合起来使用,但它不起作用,我只是不知道如何继续。
select
concat(Boss.firstName, ' ', Boss.lastName) as 'Boss',
concat(supervised.firstName, ' ',supervised.lastName) as supervised,
count(supervised.firstName, ' ',supervised.lastName)
from Employees as Boss
left join Employees as supervised on Boss.employeeNumber=supervised.reportsTo;
我需要第二列有一个数值。我很感谢你的帮助!
最佳答案
您需要在查询中添加分组并计算每个老板的行数:
select
concat(Boss.firstName, ' ', Boss.lastName) as Boss,
count(supervised.lastName) counter
from Employees as Boss left join Employees as supervised
on Boss.employeeNumber = supervised.reportsTo
group by concat(Boss.firstName, ' ', Boss.lastName)