最大平均值
我应该得到每个部门的平均工资，只显示平均工资最高的部门。我想出了这个查询，但它不起作用。任何人有一些想法？最大平均值
SELECT department, max(avg(wage))
FROM employees
GROUP BY department;
我得到这个错误： ORA-00937：第1行 错误不是一个单一群组功能
谷歌搜索...
原因：SELECT列表不能包括除非单个列表达式包含在GROUP BY子句中，否则都是一个组函数，例如AVG，COUNT，MAX，MIN，SUM，STDDEV或VARIANCE以及单个列表达式。
操作：从SELECT列表中删除组函数或单个列表达式，或者添加包含列出的所有单个列表达式的GROUP BY子句。
做这项工作：
select *
from
(
SELECT
department
, avg(wage) as ave_wage
FROM employees
GROUP BY department
)x
order by ave_wage desc
where rownum < 2;
（免责声明：没有经过充分测试，所以我可能已经把ROWNUM位在错误的地方）
这样做的技巧，谢谢！ – 2010-03-13 19:22:19
如果平均值最高的平均值是多少？这将随机挑选其中一个。那是你要的吗？ – 2010-03-14 04:05:05
这让我有一天，很棒！我仍然无法将它复制到MSSQL，因为它不喜欢rownum。基于这个概念，你也可以使用'select Top（1）* from（...）'，它像MSSQL中的一个魅力一样工作（如果在表格上播放，填充AVG查询） – Takedasama 2013-11-27 14:09:18
没有热膨胀系数，你可以这样做：
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
Althogh下面的查询显示了相同的结果其他的答案，这是很好的用户展示它如何可以作为替代来实现：
--Method 1 (Davek's select of 1st row over Order by) Brilliant!
--Method 2 (Thomas' where = sub-query result)
--Method 3 (Thomas' based on ranking)
--Method 4 (Inner join sub-queries)
select distinct a.department, a.wage from
(select distinct department, AVG(wage) as wage from employees group by department) as a
inner join
(select Max(wage) as wage from
(select distinct department, AVG(wage) as wage from employees group by department) as x) as b
on a.wage = b.wage
where a.wage = b.wage
--Method 5 (AVG wage in (sub-query))
select distinct a.department, a.wage
from (select distinct department, AVG(wage) as wage from employees group by department) as a
Where a.wage in
(select Max(wage) as wage from
(select distinct department, AVG(wage) as wage from employees group by department) as x)
期待看到一个自定义函数为这个选择也:)
这不是很有帮助...代码示例是一个更好的方法。 – 2010-03-13 19:10:34