员工表 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;