查询各部门人数,男员工数,女员工数(要求显示部门名称,总数,男员工数,女员工数)
select d.Name ‘部门名’,COUNT(*) as ‘总人数’,
sum(case e.sex when ‘男’ then 1 else 0 end) ‘男’,
sum(case e.sex when ‘女’ then 1 else 0 end) ‘女’
from Employee e,Department d
where e.DeparID=d.DeparID group by e.DeparID,d.Name
原表格:
转换后:
select Sex 性别,count(*) 总人数,
sum(case DeparID when 1 then 1 else 0 end) 系统管理部,
sum(case DeparID when 2 then 1 else 0 end) 人力资源部,
sum(case DeparID when 3 then 1 else 0 end) 物资部
from Employee group by Sex