ORA-00934:此处不允许使用组功能||选择最高薪水部门的MIN(薪水) [英] ORA-00934: Group function not allowed here || Selecting MIN(Salary) of highest paid dept

问题描述

O社区，您知道我如何选择部门编号和平均工资最高的部门的最低工资吗?还是如何消除'ORA-00934:此处不允许使用分组功能' 问题?我需要使用两个子查询吗?

O community, do you know how I could select the department_ID, and lowest salary of the department with the highest average salary? Or how to eliminate the'ORA-00934: group function not allowed here' issue? Would I need to use two subqueries?

到目前为止，这是我想出的，试图获取薪水最高的部门的部门编号:

So far, this is what I've come up with, trying to get the department_ID of the highest paid department:

SELECT department_ID, MIN(salary
FROM employees
WHERE department_ID = (SELECT department_ID
    FROM employees WHERE salary = MAX(salary));

非常感谢您的协助.

推荐答案

我无法对此进行测试，但它应该可以工作:

I can't test this, but it should work:

;WITH DepartmentsSalary AS
(
    SELECT department_ID, AVG(Salary) AvgSalary, MIN(Salary) MinSalary
    FROM employees
    GROUP BY department_ID
)
SELECT department_ID, MinSalary
FROM (  SELECT department_ID, AvgSalary, MAX(AvgSalary) OVER() MaxSalary, MinSalary
        FROM DepartmentsSalary) D
WHERE MaxSalary = AvgSalary

这篇关于ORA-00934:此处不允许使用组功能||选择最高薪水部门的MIN(薪水)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！