单行子查询返回多个行 [英] Single-row subquery returns more than one row

问题描述

我需要有关oracle sql的帮助.问题:我有2个表的员工和部门.我从一个查询中获得了部门的平均薪水，我想用它来查看有多少雇员比部门的平均薪水更高.到目前为止，我有这个.

I need some help with oracle sql. The problem: I have 2 tables employee and department. I got the average department salary from one query and i want to use it to see how many employees make more money than the average of their department. I have this so far.

此查询返回部门的平均值:

This query returns the avg of the department:

select ROUND(AVG(Salary), 2) Dept_avg_sal
 from employee, department
 where department.department_id = employee.department_id
 group by department_name

我想做的是:

select employee_name,
       salary,
       d.department_name
from   employee e,
       department d
where  salary > (select ROUND(AVG(Salary), 2) Dept_avg_sal
                 from   employee,
                        department
                 where  department.department_id = employee.department_id
                 group  by department_name)  

即时通讯收到的错误是:01427. 00000-单行子查询返回多个行"

The error that im getting is :01427. 00000 - "single-row subquery returns more than one row"

我知道同一部门的2名员工赚的钱比平均水平还多，我认为这就是造成此问题的原因.

I know that 2 employees in the same department make more money than the average and i think this is what is causing the issue.

EMPLOYEE_NAME       - SALARY -                -DEPARTMENT_NAME-      DEPT_AVG_SAL           
-------------------- ---------------------- -------------------- ------------ 
FISHER              - 3000.00  -              SALES      -          2500.00                   
JONES          -      3000.00               - ACCOUNTING         -  2750.00                   
KING             -    5000.00           -     EXECUTIVE     -       4500.00                   
**SCOTT           -     2500.00           -     IT             -      2100.00                   
SMITH           -     2900.00          -      IT     -              2100.00**                   
WILSON           -    3000.00          -      RESEARCH      -       2633.33 

任何帮助将不胜感激.

推荐答案

您的初始查询缺少外部查询上的任何连接条件以及内部查询中的任何关联条件，这些条件会将其限制为感兴趣部门的行.同样，通常也不想group by name，因为id可能是主键.

Your initial query is missing any join condition on the outer query and any correlation condition in the inner query that would limit that to just the row for the department of interest. Also generally you do not want to group by name as presumably id is the primary key.

解决这些问题以解决您的相关子查询所提供的问题

Resolving these issues to fix your correlated subquery gives

SELECT e.employee_name,
       e.salary,
       d.department_name
FROM   employee e
       JOIN department d
         ON d.department_id = e.department_id
WHERE  e.salary > (SELECT ROUND(AVG(Salary), 2) Dept_avg_sal
                   FROM   employee e2
                   WHERE  e2.department_id = e.department_id)  

但是您可能会发现放弃标量相关子查询并用派生表替换会更好.

But you may find ditching the scalar correlated sub-query and replacing with a derived table works better.

SELECT e.employee_name,
       e.salary,
       d.department_name
FROM   employee e
       JOIN department d
         ON d.department_id = e.department_id
       JOIN (SELECT ROUND(AVG(Salary), 2) Dept_avg_sal,
                    department_id
             FROM   employee
             GROUP  BY department_id) e2
         ON e2.department_id = e.department_id
            AND e.salary > e2.Dept_avg_sal  

对于Oracle，我认为以下内容也应该工作

For Oracle the following should also work I believe

SELECT employee_name,
       salary,
       d.department_name
FROM   (SELECT employee_name,
               salary,
               d.department_name,
               AVG(Salary) OVER (PARTITION BY e.department_id) AS AvgSalary
        FROM   employee e
               JOIN department d
                 ON d.department_id = e.department_id)
WHERE  salary > AvgSalary  

这篇关于单行子查询返回多个行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！