【SQL】1731. 每位经理的下属员工数量 （ 新思想：确定左表，依次添加后续字段）

leetcode题目链接

注意点

1. 确定左表（即，确定result表中的主键)，依次添加后续字段。注意：主键可能是一个字段，也可能是多个字段
2. COUNT(DISTINCT())，一般为了防止重复，使用COUNT计数时，一般带着DISTINCT()，不然就跟COUNT(1)、COUNT(常数)一样了。
3. 一般，非聚合字段都要group by

写法一：思想：（ 确定左表，依次添加后续字段）

select a.employee_id, b.name, reports_count, average_age
from (
  select reports_to as employee_id, 
        count(distinct(employee_id)) as reports_count, round(AVG(age)) as average_age
  from employees
  where reports_to is not null
  group by reports_to
  ) a 
left join (
  select employee_id, name
  from employees
  group by employee_id, name
  ) b
on a.employee_id = b.employee_id
order by employee_id asc

写法二：内连接

# 写法二
select a.employee_id, a.name, count(distinct(b.employee_id)) as reports_count, 
       round(avg(b.age)) as average_age 
from employees a
inner join employees b
on a.employee_id = b.reports_to
group by a.employee_id, a.name
# 注意：非聚合字段都要group by