oracle 遇到 ORA-00979：在 sql 中使用 CASE - IN 语句时不是 GROUP BY 表达式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3819710/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Encountering ORA-00979: not a GROUP BY expression when using CASE - IN statements in sql
提问by jonasespelita
This works:
这有效：
SELECT (CASE
WHEN x = 'value' THEN
a.col1
ELSE
nvl(a.col1, a.col2)
END)
FROM table1 a
WHERE a.this = 'that'
GROUP BY (CASE
WHEN x = 'value' THEN
a.col1
ELSE
nvl(a.col1, a.col2)
END)
But trying to have the case statement do an IN statement(trying for a more dynamic sql here), the following code results to an ORA-00979error.
但是试图让 case 语句做一个 IN 语句（在这里尝试一个更动态的 sql），下面的代码导致ORA-00979错误。
SELECT (CASE
WHEN x IN (SELECT me FROM here WHERE this = 'example') THEN
a.col1
ELSE
nvl(a.col1, a.col2)
END)
FROM table1 a
WHERE a.this = 'that'
GROUP BY (CASE
WHEN x IN (SELECT me FROM here WHERE this = 'example') THEN
a.col1
ELSE
nvl(a.col1, a.col2)
END)
Is it possible to make this work or have an alternative? Thanks. --Jonas
是否有可能使这项工作或有其他选择？谢谢。——乔纳斯
Benoit: Here's a modified sql based on your sql that recreates the error:
Benoit：这是一个基于您的 sql 的修改后的 sql，它重新创建了错误：
select (case when a.y IN (select 'A'||ROWNUM from dual where rownum=1) then 1 else 0 end)
from (SELECT 'A'||ROWNUM y, 'B' x FROM DUAL CONNECT BY ROWNUM <= 3) a where x = 'B'
group by (case when a.y IN (select 'A'||ROWNUM from dual where rownum=1) then 1 else 0 end)
;
Basically what was missing was that the FROM table should have more than one values and that a column was referenced in the CASE statement.
基本上缺少的是 FROM 表应该有多个值，并且在 CASE 语句中引用了一列。
采纳答案by Benoit
I cannot reproduce this error with following request (working):
我无法通过以下请求（工作）重现此错误：
select (case when 'X' IN (select dummy from dual where rownum=1) then 1 else 0 end)
from dual
where dummy = 'X'
group by (case when 'X' IN (select dummy from dual where rownum=1) then 1 else 0 end)
;
Try:
尝试：
WITH table1_extended AS (
SELECT a.*, CASE WHEN x IN .... END "condition"
FROM table1 a
)
SELECT b."condition"
FROM table1_extended b
WHERE b.this = 'that'
GROUP BY b."condition"
回答by Alex Poole
Is there a reason you can't move the subselect into a join? From your adjusted version on @Benoit's test case you could do:
是否有理由不能将子选择移动到连接中？从@Benoit 测试用例的调整版本中，您可以执行以下操作：
select case when a.y = b.z then 1 else 0 end, count(*)
from (select 'A'||rownum y, 'B' x from dual connect by rownum <= 3) a,
(select 'A'||rownum z from dual where rownum=1) b
where a.x = 'B'
group by case when a.y = b.z then 1 else 0 end;
Which gives (in 10g):
其中给出（以 10 克计）：
CASEWHENA.Y=B.ZTHEN1ELSE0END COUNT(*)
---------------------------- ----------------------
1 1
0 2
Not entirely convinced that will give the answer you want, but hard to tell as it's so simplified, and might be a starting point.
不完全相信这会给出您想要的答案，但很难说，因为它是如此简化，并且可能是一个起点。
Edit编辑似乎这确实太简单了。另一种可能的解决方案看起来也太简单了，但可能会奏效，那就是从另一端处理这个问题，然后将
casecase放入selectselect聚合函数中：SELECT MIN(CASE
WHEN x IN (SELECT me FROM here WHERE this = 'example') THEN
a.col1
ELSE
nvl(a.col1, a.col2)
END)
FROM table1 a
WHERE a.this = 'that'
GROUP BY (CASE
WHEN x IN (SELECT me FROM here WHERE this = 'example') THEN
a.col1
ELSE
nvl(a.col1, a.col2)
END)
