已知一个员工数据库,其中包括:员工编号(ID_Number)、姓名(Name)、性别(Sex)、年龄(Age)、所属部门(Department)五个字段,要对每个部门的总人数以及各部门男女人数进行统计。下面为select语句:
1、
select
Department as 部门,
count(case when Sex = '男' then '男' end) as 男,
count(case when sex = '女' then '男' end) as 女,
count(1) AS 总人数
from
TBL_Persons
group by
Department
如果某个部门人数为0,这种方式就不能起到很好的查询。
2、
select
COUNT(1),a.Department,
(select COUNT(1) from TBL_Persons b where b.Sex='男' and b.Department=a.Department) 男,
(select COUNT(1) from TBL_Persons b where b.Department=a.Department and b.Sex='女') 女
from
TBL_Persons a
group by
a.Department
这种方式是对表进行动态查询,当一个部门人数为0时,仍可对表进行正常查询,故这种查询方式更加适用。