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