ORA-00979 不是按表达式分组
我通过以下查询获得 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?
相关文章