我需要MySQL查询的帮助

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了我需要MySQL查询的帮助，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含967字，纯文字阅读大概需要2分钟。

内容图文

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.

解决方法:

回答第一个问题：

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)

内容总结

以上是互联网集市为您收集整理的我需要MySQL查询的帮助全部内容，希望文章能够帮你解决我需要MySQL查询的帮助所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端