本文介绍了ORA-00979:不是GROUP BY表达式问题的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我正在执行以下请求,但收到"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表达式问题的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-18 18:21