为什么会出现错误ORA-00937 [英] Why getting Error ORA-00937

问题描述

对于获得三架以上飞机认证的每位飞行员，请找到 和他(或她)所在的飞机的最大巡航距离 认证.

For each pilot who is certified for more than three aircraft, find the aid and the maximum cruisingrange of the aircraft that he (or she) is certified for.

我有四个桌子

航班( flno :varchar，ffrom:varchar，fto:varchar，distance:integer，departs:date，Arrives:date);

FLIGHTS(flno:varchar, ffrom:varchar, fto:varchar, distance:integer, departs:date, Arrives:date);

AIRCRAFT( aid :varchar，aname:varchar，Cruisingrange:number);

AIRCRAFT(aid:varchar, aname:varchar, Cruisingrange:number);

EMPLOYEES( eid :varchar，Ename:varchar，Salary:number);

EMPLOYEES(eid:varchar, Ename:varchar, Salary:number);

CERTIFIED(eid:varchar，aid:varchar);

CERTIFIED (eid:varchar, aid:varchar);

我的查询:

SELECT aname, MAX(cruisingrange)
FROM   Aircraft 
WHERE aid IN(SELECT aid 
         FROM Certified
         WHERE eid = (SELECT eid
                      FROM Certified
                      GROUP BY eid
                      HAVING COUNT(eid) > 3) ) 
;

但这会导致此错误:

ORA 00937. 00000-不是单组分组功能"

ORA 00937. 00000 - "not a single-group group function"

推荐答案

在外部选择中缺少分组依据. select语句中的所有非集合都必须在分组依据中列出.

Missing group by in outer select. All non-aggregates in a select statement must be listed in the group by.

SELECT aname, MAX(cruisingrange)
FROM   Aircraft 
WHERE aid IN(SELECT aid 
         FROM Certified
         WHERE eid in (SELECT eid
                      FROM Certified
                      GROUP BY eid
                      HAVING COUNT(eid) > 3) )
GROUP BY aname 
;

请注意，您在最里面的选择上有一个分组依据；但你似乎想念它 在最外面.

Note you have a group by on the inner most select; but you seem to be missing it on the outermost.

我也同意您的意思是，如果您可能需要in与=

I also agree do you mean in or equal will more than one EID be in Certified if so you likely need in vs =

这篇关于为什么会出现错误ORA-00937的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！