SQL Lesson 10: 在查询中进行统计I (Pt. 1)

1.【统计】找出就职年份最高的雇员(列出雇员名字+年份）

select name,years_employed
from employees
order by years_employed desc
limit 1

select name,
       max(years_employed) as max_year
from employees

 

2.【分组】按角色(Role)统计一下每个角色的平均就职年份

select role,
       avg(years_employed) as avg_year
from employees
group by role

3.【分组】按办公室名字总计一下就职年份总和

select building,
       sum(years_employed) as sum_year
from employees
group by building

 

4.【难题】每栋办公室按人数排名,不要统计无办公室的雇员

select building,
       count(name) as count_people
from employees
where building is not null
group by building