MySQL 部门 员工数量查询

员工表 user

部门表 department

1.查询每个部门的员工数量；

SELECT department.`name`,COUNT(*) from `user` inner JOIN department ON department.id=`user`.depart_id GROUP BY depart_id;

2.查询每个部门的员工数量并 按数量从高到低排序；

SELECT department.`name`,COUNT(*) from `user` inner JOIN department ON department.id=`user`.depart_id GROUP BY depart_id ORDER BY COUNT(*) desc;

3.查询员工数大于1的部门名以及部门员工数；

SELECT t2.name,t1.count from (SELECT depart_id as did ,count(*) as count from user GROUP BY depart_id HAVING count(*)>1)t1,department t2 WHERE t1.did=t2.id;