SELECT zh.depName,zh.msMaxNum,zh.gcsMaxNum,zh.sxsMaxNum,
COUNT(CASE WHEN zh.postName = '秘书' THEN 1 ELSE NULL END) AS '秘书人数',
COUNT(CASE WHEN zh.postName = '工程师' THEN 1 ELSE NULL END) AS '工程师人数',
COUNT(CASE WHEN zh.postName = '实习生' THEN 1 ELSE NULL END) AS '实习生人数' FROM
(SELECT e.empId,e.empName,
d.depId,d.depName,d.msMaxNum,d.gcsMaxNum,d.sxsMaxNum,
e.postId,p.postName
FROM emp e
RIGHT JOIN dep d
ON e.depId=d.depId
LEFT JOIN post p
ON e.postId=p.postId
ORDER BY e.empId) AS zh
GROUP BY zh.depName
查询结果如下:
总结:使用了group by分组,另外使用count()函数对各个岗位下的人数进行统计。
后续继续添加