SQL 此处不允许使用群组功能
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28077555/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Group function is not allowed here
提问by user3093583
When I run the following query, I get
当我运行以下查询时，我得到
ORA-00934: group function is not allowed here
ORA-00934: 此处不允许使用组功能
what is the problem ?
问题是什么 ？
select c.Numcom,c.Nompr,c.salaire_fix
from commercialv c,comercialv c1
where c.salaire_fix=(max(c1.salaire_fix) );
回答by Sylvain Leroux
You cannot use an aggregate function in a WHEREclause.
不能在WHERE子句中使用聚合函数。
Given your use case, you probably want a subquery:
鉴于您的用例，您可能需要一个子查询：
select c.Numcom,c.Nompr,c.salaire_fix
from commercialv c
where c.salaire_fix=(select max(salaire_fix) from comercialv);
The rational is that aggregate functions works on a set. The WHEREclause on the other hand, has only access to the data of one row.
理由是聚合函数适用于集合。在WHERE另一方面条款，只对数据访问的一行。
回答by Gordon Linoff
You can do what you want with analytic functions:
你可以用分析函数做你想做的事：
select Numcom, Nompr, salair_fix
from (select c.Numcom, c.Nompr, c.salaire_fix,
max(c.salaire_fix) over () as maxs
from commercialv c
) c
where c.salaire_fix = c.maxs;
As for your query, aggregation functions are not permitted in the whereclause.
至于您的查询，where子句中不允许使用聚合函数。
回答by David Faber
You could also do this query using MAX()as a window function (or analytic function if you prefer the Oracle lingo):
您还可以使用MAX()窗口函数（或分析函数，如果您喜欢 Oracle 术语）来执行此查询：
SELECT numcom, nompr, salaire_fix FROM (
SELECT numcom, nompr, salaire_fix, MAX(salaire_fix) OVER ( ) AS max_salaire_fix
FROM commercialv
) WHERE salaire_fix = max_salaire_fix;
You could also use RANK():
您还可以使用RANK()：
SELECT numcom, nompr, salaire_fix FROM (
SELECT numcom, nompr, salaire_fix, RANK() OVER ( ORDER BY salaire_fix DESC ) AS salaire_fix_rank
FROM commercialv
) WHERE salaire_fix_rank = 1;
Or even ROWNUM:
甚至ROWNUM：
SELECT * FROM (
SELECT numcom, nompr, salaire_fix
FROM commercialv
ORDER BY salaire_fix DESC
) WHERE rownum = 1;
The only difficulty with the last is that it will get only one row even if there are additional rows with the maximum value of salaire_fix. The first two queries will get more than one row in that case.
最后一个的唯一困难是，即使存在最大值为 的附加行，它也只会得到一行salaire_fix。在这种情况下，前两个查询将获得不止一行。
