sql - 平均值的最大值

Question

我应该得到每个部门的平均工资，只显示平均工资最高的部门。我想出了这个查询，但它不起作用。有人有一些想法吗？

SELECT department, max(avg(wage))
FROM employees
GROUP BY department;

我收到此错误:
第 1 行的错误:
ORA-00937: 不是单组组函数

Answer 1

没有 CTE，您可以执行以下操作:

Select Z.Department, Z.AvgWage
From  (
        Select Department, Avg(Wage) AvgWage
        From Employees
        Group By Department
        ) As Z
Where AvgWage = (
                Select Max(Z1.AvgWage)
                From    (
                        Select Department, Avg(Wage) AvgWage
                        From Employees
                        Group By Department
                        )  Z1
                )

使用 CTE，您可以:

With AvgWages As
    (
    Select Department
        , Avg(Wage) AvgWage
        , Rank() Over( Order By Avg(Wage) Desc ) WageRank
    From Employees
    Group By Department
    )
Select Department, AvgWage, WageRank
From AvgWages
Where WageRank = 1