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_agefrom employeeswhere reports_to is not nullgroup by reports_to) a 
left join (select employee_id, namefrom employeesgroup 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