我想返回部门id-s,其中该部门的最低工资高于其他部门的最高工资。这是我的问题:
select *
from hr.employees
where (select min(salary) from hr.employees group by department_id) < any (select max(salary) from hr.employees group by department_id)
我得到的错误是:ORA-01427:单行子查询返回多行
即使我使用了任何运算符。。。
有人能帮忙吗?
这里有一种方法。首先按部门汇总工资,收集每个部门的最低工资和最高工资。然后在问题中的条件下自联接结果(将结果联接到自身(。我使用了一个with
子句,它允许Oracle只执行一次聚合。我包括";高薪;部门和最高工资;低工资;部门来确认查询是否按预期工作。输出相当大(27行(,但并没有大到不适合这个答案。
with d as (
select department_id, min(salary) as min_sal, max(salary) as max_sal
from hr.employees
group by department_id
)
select d1.department_id as dept_highsal, d1.min_sal as dept_higsal_min_sal,
d2.department_id as dept_lowsal , d2.max_sal as dept_lowsal_max_sal
from d d1 inner join d d2 on d1.min_sal > d2.max_sal
;
输出:
DEPT_HIGHSAL DEPT_HIGSAL_MIN_SAL DEPT_LOWSAL DEPT_LOWSAL_MAX_SAL
------------ ------------------- ----------- -------------------
90 17000 80 14000
90 17000 20 13000
90 17000 100 12008
90 17000 110 12008
90 17000 30 11000
90 17000 70 10000
90 17000 60 9000
90 17000 50 8200
90 17000 7000
90 17000 40 6500
90 17000 10 4400
70 10000 60 9000
70 10000 50 8200
70 10000 7000
70 10000 40 6500
70 10000 10 4400
110 8300 50 8200
110 8300 7000
110 8300 40 6500
110 8300 10 4400
7000 40 6500
7000 10 4400
100 6900 40 6500
100 6900 10 4400
40 6500 10 4400
80 6100 10 4400
20 6000 10 4400
试试这个:
select d_id1, d_id2
(select department_id d_id1, min(salary) min_salary
from hr.employees
group by department_id) q1
join
(select department_id d_id2, max(salary) max_salary
from hr.employees
group by department_id) q2
on q1.min_salary > q2.max_salary
谢谢大家。
这是一个解决方案:
从(选择main_temp.deapartment_id,min(工资(min_salary,(从hr.eemployees temp中选择max(salary(,其中temp.deapartment_id<gt;main_temp.dedepartment_id(其他_最大来自hr.employes main_temp,其中department_id不是null按department_id分组(,其中min_salary>其他_最大