SQL Lesson 11: 在查询中进行统计II (Pt. 2)

1.【统计】统计一下Artist角色的雇员数量 ✓

select count(name) as artist_number
from employees
where role = "Artist"

2.【分组】按角色统计一下每个角色的雇员数量

select role,count(name) as cont_number
from employees
group by role

3.【分组】算出Engineer角色的就职年份总计

select sum(years_employed) as sum_year
from employees
where role = "Engineer"

4.【难题】按角色分组算出每个角色按有办公室和没办公室的统计人数(列出角色，数量，有无办公室,注意一个角色如果部分有办公室，部分没有需分开统计）

select role,count(years_employed) as count_num,
       case when building is null then 0 else 1 end as c_b
from employees
group by role,c_b

case when ......  then .... else

像if ...else语法一样使用