ORA-00979:不是GROUP BY表达式问题 [英] ORA-00979: not a GROUP BY expression issue

问题描述

我正在执行以下请求，但收到"ORA-00979:不是GROUP BY表达式"错误.

I am performing the following request and I get a "ORA-00979: not a GROUP BY expression" error.

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

我知道我必须将SELECT的所有列放入GROUP BY语句中，但分组功能除外(例如MAX或SUM)，因此我正在尝试以下查询，但得到的错误消息相同:

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 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?

非常感谢你， 马丁

推荐答案

您在group by表达式中缺少field7.

此外，您不能通过同一查询的表达式在组中使用别名.您需要按表达式在组中添加完整的CASE语句，以包含field7.

Also you cannot use alias in your group by expression of same query. You need to add complete CASE statement in your group by expression to include field7.

在分组依据中仅提及别名是不可能的，因为SELECT步骤是执行查询的最后一步，当别名名称尚未定义时，分组会更早地进行.

Just mentioning an alias is not possible in group by, because the SELECT step is the last step to happen the execution of a query, grouping happens earlier, when alias names are not yet defined.

这篇关于ORA-00979:不是GROUP BY表达式问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！