ORA-00979不是按表达式分组 [英] ORA-00979 not a group by expression
问题描述
我通过以下查询获取ORA-00979:
I am getting ORA-00979 with the following query:
SELECT cr.review_sk, cr.cs_sk, cr.full_name,
tolist(to_char(cf.fact_date, 'mm/dd/yyyy')) "appt",
cs.cs_id, cr.tracking_number
from review cr, cs, fact cf
where cr.cs_sk = cs.cs_sk
and UPPER(cs.cs_id) like '%' || UPPER(i_cs_id) || '%'
and row_delete_date_time is null
and cr.review_sk = cf.review_wk (+)
and cr.fact_type_code (+) = 183050
GROUP BY cr.review_sk, cr.cs_sk, cf.fact_date, cr.tracking_number
ORDER BY cs.cs_id, cr.full_name;
我找不到在同一查询中同时具有GROUP BY和ORDER BY子句的任何示例.我尝试一次从组中删除每个字段,但仍然遇到相同的错误.
I couldn't find any examples that had both GROUP BY and ORDER BY clauses in the same query. I tried removing each field from the group by one at a time, but am still getting the same error.
推荐答案
必须将SELECT
的所有列放在GROUP BY
中,或在其上使用将结果压缩为单个值的函数(例如MIN
,MAX
或SUM
).
You must put all columns of the SELECT
in the GROUP BY
or use functions on them which compress the results to a single value (like MIN
, MAX
or SUM
).
一个简单的例子来理解为什么会发生这种情况:假设您有一个这样的数据库:
A simple example to understand why this happens: Imagine you have a database like this:
FOO BAR
0 A
0 B
,然后运行SELECT * FROM table GROUP BY foo
.这意味着数据库必须返回一行,并且第一列0
满足GROUP BY
的要求,但是现在有两个bar
值可供选择.您期望哪个结果-A
或B
?还是数据库应该返回多个行,从而违反GROUP BY
的约定?
and you run SELECT * FROM table GROUP BY foo
. This means the database must return a single row as result with the first column 0
to fulfill the GROUP BY
but there are now two values of bar
to chose from. Which result would you expect - A
or B
? Or should the database return more than one row, violating the contract of GROUP BY
?
这篇关于ORA-00979不是按表达式分组的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!