17网站一起做网店增城,成都网站建设qghl,上海最近热点事件,如何建设网络营销网站leetcode题目链接
注意点
确定左表#xff08;即#xff0c;确定result表中的主键)#xff0c;依次添加后续字段。注意#xff1a;主键可能是一个字段#xff0c;也可能是多个字段COUNT(DISTINCT())#xff0c;一般为了防止重复#xff0c;使用COUNT计数时#xff0c… leetcode题目链接
注意点
确定左表即确定result表中的主键)依次添加后续字段。注意主键可能是一个字段也可能是多个字段COUNT(DISTINCT())一般为了防止重复使用COUNT计数时一般带着DISTINCT()不然就跟COUNT(1)、COUNT(常数)一样了。一般非聚合字段都要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
