mysql聚合函数count用法_mysql 组函数count的具体用法

SELECT d.department,

COUNT(CASE e.sex WHEN e.sex='f' AND p.position !='主管' THEN p.id END) fq,

COUNT(CASE e.sex WHEN e.sex='f' AND p.position ='主管' THEN p.id END) fz,

COUNT(CASE e.sex WHEN e.sex='m' AND p.position !='主管' THEN p.id END) mq,

COUNT(CASE e.sex WHEN e.sex='m' AND p.position ='主管' THEN p.id END) mz,

COUNT(e.id) xj

FROM department d LEFT JOIN employ e ON d.id=e.deptid

LEFT JOIN position p ON e.positionid=p.id

GROUP BY d.id;

出现后面两个count统计不出来是什么原因？

为什么下面个可以查出来？

SELECT d.department,

COUNT(CASE WHEN e.sex='f' AND p.position !='主管' THEN p.id END) fq,

COUNT(CASE WHEN e.sex='f' AND p.position ='主管' THEN p.id END) fz,

COUNT(CASE WHEN e.sex='m' THEN CASE WHEN p.position !='主管' THEN p.id END END) mq

COUNT(CASE WHEN e.sex='m' THEN CASE WHEN p.position ='主管' THEN p.id END END) mz,

COUNT(e.id) xj

FROM department d LEFT JOIN employ e ON d.id=e.deptid

LEFT JOIN position p ON e.positionid=p.id

GROUP BY d.id