SQL -使用子查询计算最大员工数和排除字符串
SQL -使用子查询计算最大员工数和排除字符串
提问于 2016-01-12 09:50:59
这是我的两张桌子：
雇员
employee_id employee_name job manager_id hire_date salary commission department_id
----------------------------------------------------------------------- -------------------------------------
7839 KING PRESIDENT 20-NOV- 01 5000 50
7596 JOST VICE PRESIDENT 7839 04-MAY- 01 4500 50
7603 CLARK VICE PRESIDENT 7839 12-JUN- 01 4000 50
7566 JONES PUBLIC ACCOUNTANT 7596 05-APR- 01 3000 10
7886 STEEL PUBLIC ACCOUNTANT 7566 08-MAR- 03 2500 10
7610 WILSON ANALYST 7596 03-DEC- 01 3000 20
7999 WOLFE ANALYST 7610 15-FEB- 02 2500 20
7944 LEE ANALYST 7610 04-SEP- 06 2400 20
7900 FISHER SALESMAN 7603 06-DEC- 01 3000 500 30
7921 JACKSON SALESMAN 7900 25-FEB- 05 2500 400 30
7952 LANCASTER SALESMAN 7900 06-DEC- 06 2000 150 30
7910 SMITH DATABASE ADMINISTRATOR 7596 20-DEC- 01 2900 40
7788 SCOTT PROGRAMMER 7910 15-JAN- 03 2500 40
7876 ADAMS PROGRAMMER 7910 15-JAN- 03 2000 40
7934 MILLER PROGRAMMER 7876 25-JAN- 02 1000 40
8000 BREWSTER TBA 22-AUG- 13 2500 部门
department_id department_name address
------------- -------------------- --------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 IT DALLAS
50 EXECUTIVE NEW YORK
60 MARKETING CHICAGO 我需要使用子查询(而不是联接)来编写一个语句，以显示所有部门(不包括达拉斯的部门)的名称和地址，这些部门的员工人数最多。除了字符串“达拉斯”，我不能有任何硬编码。
结果应该如下所示：
DEPARTMENT_NAME ADDRESS
-------------------- --------------------
EXECUTIVE NEW YORK
SALES CHICAGO我对在没有加入的情况下做这件事感到茫然。以下是我的三次尝试：
SELECT department_name, address
FROM department
WHERE department_id NOT IN
(SELECT department_id
FROM department
WHERE UPPER(address) + 'DALLAS')
ORDER BY department_name;
SELECT department_name, address
FROM department
WHERE department_id NOT IN
(SELECT department_id
FROM department
WHERE UPPER(address) = 'DALLAS')
AND department_id > ALL
(select COUNT(department_id) FROM employee GROUP BY department_id)
ORDER BY department_id;
select *
from department
where department_id in (select department_id
from employee
group by department_id
having count(*) = (select max(num)
from (select department_id,
count(*) as num
from employee_tbl
where address != 'DALLAS'
group by department_id)));有人能告诉我，如果我甚至接近并引导我朝着正确的方向吗？谢谢
回答 2
Stack Overflow用户
发布于 2016-01-12 10:56:44
雇员人数最多的部门(使用单表扫描)：
SELECT department_id
FROM (
SELECT department_id,
RANK() OVER ( ORDER BY num_employees DESC ) AS rnk
FROM (
SELECT department_id,
COUNT(1) AS num_employees
FROM employees
GROUP BY department_id
)
)
WHERE rnk = 1;查找不在DALLAS中的部门
SELECT department_id
FROM departments
WHERE address <> 'DALLAS'因此，将两者结合起来：
SELECT department_id
FROM (
SELECT department_id,
RANK() OVER ( ORDER BY num_employees DESC ) AS rnk
FROM (
SELECT department_id,
COUNT(1) AS num_employees
FROM employees
WHERE department_id IN (
SELECT department_id
FROM departments
WHERE address <> 'DALLAS'
)
GROUP BY department_id
)
)
WHERE rnk = 1;Stack Overflow用户
发布于 2016-01-12 10:48:57
也许是这样的？
select department_name, address
from department
where department_id in
(SELECT e.department_id
FROM employee e
group by e.department_id
having count(*) = (
select max(count(e2.employee_id))
FROM employee e2
WHERE department_id NOT IN (SELECT department_id
FROM department
WHERE UPPER(address) = 'DALLAS')
group by e2.department_id
)
)
and UPPER(address) != 'DALLAS'但这并不是平面上最好的查询:)非常奇怪
在你的问询中，艾伦，我不知道表在哪里
employee_tbl
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/34740292
复制相关文章
相似问题
腾讯云开发者
