我需要 MySQL 查询方面的帮助

Question

I have two tables - `employee` and `department`. 

1. `employee` table contains column id,employee name and dept_id
2. `department` table contains column id, department name.

I need exact department name which contains 

1. maximum employee and 
2. no employee

编辑：

Apologizing for bad grammar, here is the example for above two questions what i need.
1. for eg: if two department contains same number of employees, i need to show both department not single by limit.
2. for eg: if more than one department contains 0 employees, i must show those departments particularly.

Answer 1

select department_name as `department name`, 
       count(*) as `number of employees`
from employee 
        inner join department 
            on employee.dept_id = department.id
group by department_name
order by count(*) desc
limit 1

我认为应该这样做。我有一段时间没有用 mysql 做任何事情了。

编辑：错过了第二个问题

select department_name as `department name`, 
       count(*) as `number of employees`
from employee 
        left join department 
            on employee.dept_id = department.id
group by department_name
  HAVING count(*) = 0

Answer 2

第一个问题的回答：

WITH epcount(dept_id, ep_count) AS
(
    SELECT dept_id, COUNT(*) AS ep_count
        FROM employee
        GROUP BY dept_id
)
SELECT d.name FROM epcount AS ec1 JOIN department AS d ON ec1.dept_id=d.id
    WHERE NOT EXISTS
        (SELECT * FROM epcount AS ec2 WHERE ec1.ep_count < ec2.ep_count)

第二个问题的回答：

SELECT name FROM department AS d
    WHERE NOT EXISTS
        (SELECT * FROM  employee AS e WHERE d.id=e.dept_id)

Answer 3

如果我没看错问题，你需要：

select department_name,
       count(employee.dept_id) as num_employees
from department
left join employee on employee.dept_id = department.id
group by department_name
having count(employee.dept_id) = 0 or
       count(employee.dept_id) = (select count(dept_id)
                   from employee
                   group by employee.id
                   order by count(dept_id) desc
                   limit 1)

Answer 4

这将为您提供按员工数量排序的部门排序列表。

SELECT `dept`.`id`, `dept`.`name`, COUNT(`employee`.`id`) as `employee_count`
    FROM `dept` LEFT JOIN `employee`
        ON `employee`.`dept_id` = `dept`.`id`
    GROUP BY `dept`.`id`
    ORDER BY `employee_count`

要获取没有员工的部门，

AND `employee_count` = 0

请在 GROUP BY 之前添加：...。

要获取员工最多的部门，请在末尾添加 DESC LIMIT 1。

Answer 5

显示包含最多员工的部门名称和员工数量的查询：

SELECT department.name, COUNT(employee.name) from department
 INNER JOIN employee
 ON employee.dept_id = department.id
 GROUP BY department.name
 ORDER BY COUNT(employee.name) DESC limit 1

显示没有员工的部门的查询：

SELECT department.name from department
 LEFT JOIN employee
 ON employee.dept_id = department.id
 HAVING COUNT(employee.name) = 0
 GROUP BY department.name

如果需要在一个查询中显示它，请粘贴第一个查询，添加 UNION ALL，然后粘贴第二个查询。