oracle 分组依据：ORA-00937：不是单组分组功能
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16206740/
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
Group by: ORA-00937: not a single-group group function
提问by Sohel Mansuri
Why is this query not working properly?
为什么这个查询不能正常工作？
SELECT e.*
FROM enrolled e
FULL OUTER JOIN student s ON e.studentid = s.sid
WHERE ((e.quarter = 'Fall') OR (e.quarter = 'Spring'))
GROUP BY e.studentid
HAVING count(e.studentid) == 1;
Error that happens:
发生的错误：
ORA-00937: not a single-group group function 00937. 00000 - "not a single-group group function" *Cause: *Action: Error at Line: 1 Column: 8
ORA-00937: 不是单组组函数 00937. 00000 - “不是单组组函数” *原因：*操作：行错误：1 列：8
Thanks,
谢谢，
回答by A.B.Cade
As Rene said, you can't select columns which aren't in the group by clause.
正如 Rene 所说，您不能选择不在 group by 子句中的列。
If you want to select these columns but only for studentids that have count(*) = 2for some critria you can do it like this:
如果您想选择这些列，但仅适用于studentid具有count(*) = 2某些标准的s，您可以这样做：
select *
from
(SELECT e.*, count(*) over (partition by e.studentid) cnt
FROM enrolled e
FULL OUTER JOIN student s ON e.studentid = s.sid
WHERE ((e.quarter = 'Fall') OR (e.quarter = 'Spring'))
)
where cnt = 2
Or
或者
SELECT e.*, count(*) over (partition by e.studentid) cnt
FROM enrolled e
FULL OUTER JOIN student s ON e.studentid = s.sid
WHERE ((e.quarter = 'Fall') OR (e.quarter = 'Spring'))
AND (select count(*) from enrolled i where i.studentid = e.studentid) = 2
Anyway, you can't do ==only =
无论如何，你不能==只做=
回答by Rene
When you use "group by" you can only select the columns that are used in the "group by".
当您使用“group by”时，您只能选择“group by”中使用的列。
You are selecting e.*. change that to e.studentid.
您正在选择 e.*. 将其更改为 e.studentid。
And its having count(*) = 1. Not having count(*) ==1.
而其having count(*) = 1. 不是having count(*) ==1。
