u010050475 于 2013.06.24 15:19 提问

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;

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

1个回答

shendixiong   2013.09.18 17:47
``````SELECT CASE 1 WHEN 1=1 THEN 'a' else 'b' END;
SELECT CASE 2 WHEN 1=1 THEN 'a' else 'b' END;
``````

``````SELECT CASE 1=1 WHEN 1=1 THEN 'a' else 'b' END;
SELECT CASE 2=2 WHEN 1=1 THEN 'a' else 'b' END;
``````