上面的方法仔细看一下存在有问题:
1.效率不高,要查询出男和女的个数,需要两次走遍表中所有记录
2.加深一点难度:
员工表中有一个字段——部门(dept),要求查询出每个部门中男员工和女员工的个数:
按照上面的方法可以写为:
select maleCount.dept, maleCount, femalCount from (select dept, count(*) as maleCount from table_employee where sex = 'male' group by dept) as MaleCount , (select dept, count(*) as femaleCount from table_employee where sex = 'femle' group by dept) as FemalCount where MaleCount.dept = FemalCount.dept
存在一中问题,如果一个部门只有男员工,没有女员工,对于查询
select dept, count(*) as femaleCoun from table_employee where sex = 'female' group by dept. 这个部门将没有数据显示,因此上面的查询中将没有这个部门的数据(即使存在男员工)
我已经实现了:
select * from (select count(*) as maleCount from table_employee where sex = 'male') as MaleCount , (select count(*) as femaleCount from table_employee where sex = 'femle') as FemalCount
大家可以参考!