主页 / 问题 / 958224
Accepted
Alexander
Asked:2020-03-19 01:48:49 +0800 CST2020-03-19 01:48:49 +0800 CST

甲骨文。请求未完成

  • 772

查询：列出所有收入超过所在部门平均工资的员工

我按部门得出平均工资：

SELECT AVG(salary) 
FROM listworker, listdep
WHERE listworker.department_code = listdep.code_department
GROUP BY (listdep.name_department)

结论：

在此处输入图像描述

接下来我使用子查询：

SELECT worker_surname, name_department, salary
FROM listworker, listdep
WHERE listworker.salary > 
      (SELECT AVG(salary) 
       FROM listworker, listdep
       WHERE listworker.department_code = listdep.code_department
       GROUP BY (listdep.name_department));

但是它返回的值不止一个，如何使它只与部门的平均工资进行比较。

我知道您可以指定一个附加条件，但它不起作用。这里的工作只与农学有关，而与其他部门无关

SELECT worker_surname, name_department, salary
FROM listworker, listdep
WHERE listworker.salary > 
     (SELECT AVG(salary) 
      FROM listworker, listdep
      WHERE listworker.department_code = listdep.code_department 
        AND listdep.name_department = 'Агрономия'
      GROUP BY (listdep.name_department));
база-данных

1 个回答

  1. Best Answer

    最好使用允许您与原始数据同时获取分组数据的窗口函数：

    SELECT *
  FROM (
    SELECT listworker.*,
           avg(salary) over(parition by listdep.name_department) avg_salary
      FROM listworker, listdep
     WHERE listworker.department_code = listdep.code_department
  ) X
 WHERE salary > avg_salary
    • 4

相关问题