我需要编写一个查询,列出所有员工的姓名(在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)

10-04 12:46