oracle ORA-00979: 不是 GROUP BY 表达式问题
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5028212/
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
ORA-00979: not a GROUP BY expression issue
提问by MartinMoizard
I am performing the following request and I get a "ORA-00979: not a GROUP BY expression" error.
我正在执行以下请求，但收到“ORA-00979：不是 GROUP BY 表达式”错误。
select distinct
field1,
field2,
field3,
count(*) as field4,
field5,
field6,
case
when smt1>0 then 'Deleted'
when smt2>0 then 'Impacted'
when smt3>0 then 'Unknown'
else 'Clean'
end as field7,
field8,
field9,
field10,
field11,
field12,
field13
from (<here a big sub query>) A
group by field1, field2
order by field1, field2
I know that I have to put all columns of the SELECT in the GROUP BY statement except the grouping functions ones (like MAX or SUM) so I'm trying the following query but I get the same error message:
我知道我必须将 SELECT 的所有列都放在 GROUP BY 语句中，但分组函数（如 MAX 或 SUM）除外，因此我正在尝试以下查询，但收到相同的错误消息：
select distinct
field1,
field2,
field3,
count(*) as field4,
field5,
field6,
case
when smt1>0 then 'Deleted'
when smt2>0 then 'Impacted'
when smt3>0 then 'Unknown'
else 'Clean'
end as field7,
field8,
field9,
field10,
field11,
field12,
field13
from (<here a big sub query>) A
group by field1, field2, field3, field5, field6, field8, field9, field10, field11, field12, field13
order by field1, field2
How can I solve that without changing the overall meaning of the query?
如何在不改变查询的整体含义的情况下解决这个问题？
Thank you very much, Martin
非常感谢，马丁
回答by Sachin Shanbhag
you are missing field7in your group byexpression.
你field7的group by表情不见了。
Also you cannot use alias in your group by expression of same query. You need to add complete CASEstatement in your group by expression to include field7.
此外，您不能通过相同查询的表达式在您的组中使用别名。您需要CASE通过表达式在您的组中添加完整的语句以包含 field7。
Just mentioning an alias is not possible in group by, because the SELECTstep is the last step to happen the execution of a query, grouping happens earlier, when alias names are not yet defined.
在 group by 中不可能只提到别名，因为该SELECT步骤是执行查询的最后一步，当别名尚未定义时，分组发生得更早。
回答by René Nyffenegger
You need to add the expression
您需要添加表达式
case
when smt1>0 then 'Deleted'
when smt2>0 then 'Impacted'
when smt3>0 then 'Unknown'
else 'Clean'
end
into your group byexpression.
进入你的group by表情。
