每当我添加MAX()函数或分组语句时，查询就会中断

Question

我有一些表格的布局如下：

Airplane  
(airplaneID number(2) primary key, airplaneName char(20),  cruisingRange number(5));
Flights   
(airplaneID number (2), flightNo number(4) primary key, 
fromAirport char(20), toAirport char(20), distance number(4), depart timestamp, 
arrives timestamp, foreign key (airplaneID) references Airplane);
Employees 
(employeeID number(10) primary key, employeeName char(18), salary number(7));
Certified 
(employeeID number(10), airplaneID number(2),
foreign key (airplaneID) references Airplane, 
foreign key (employeeID) references Employees );

我需要写一个查询来获得以下信息：

对于每名获得至少4架飞机认证的飞行员，请查找employeeName和最大巡航范围那个飞行员是合格的。

我写的查询是这样的：

SELECT Employees.employeeName, MAX(Airplane.cruisingRange)
FROM Employees
JOIN Certified ON Employees.employeeID = Certified.employeeID
JOIN Airplane ON Airplane.airplaneID = Certified.airplaneID
GROUP BY Employees.employeeName
HAVING COUNT(*) > 3

最后，这是执行并读取查询信息的函数：

void  prepareAndExecuteIt() {
//  Prepare the query
//sqlQueryToRun.len = strlen((char *) sqlQueryToRun.arr);
exec sql PREPARE dbVariableToHoldQuery FROM :sqlQueryToRun;
/* The declare statement, below, associates a cursor with a
* PREPAREd statement.  
* The cursor name, like the statement
* name, does not appear in the Declare Section.
* A single cursor name can not be declared more than once.
*/
exec sql DECLARE cursorToHoldResultTuples cursor FOR dbVariableToHoldQuery;
exec sql OPEN cursorToHoldResultTuples;
int i = 0;
exec sql WHENEVER NOT FOUND DO break;
while(1){
exec sql FETCH cursorToHoldResultTuples INTO empName, cruiseRange;
printf("%st", empName);
printf("%sn", cruiseRange);
i++;
// This is temporary while I debug so it doesn't just loop on forever when the query breaks.
if (i > 500){
printf("Entered break statementn");
break;
}
}
exec sql CLOSE cursorToHoldResultTuples;
}

在添加MAX((、GROUP BY和HAVING语句之前，查询一直有效。然后它只是无限地读不到任何东西。我不知道这是我编写查询的方式有问题，还是执行查询的C++代码有问题。我正在使用ProC接口访问Oracle数据库。有什么问题吗？

Answer 1

不能混合隐式和显式联接。我建议

SELECT Employees.employeeName, MAX(Airplane.cruisingRange)
FROM Employees
JOIN Certified ON Employees.employeeID = Certified.employeeID
JOIN Airplane ON Airplane.airplaneID = Certified.airplaneID
GROUP BY Employees.employeeName
HAVING COUNT(*) > 3

这很好用。

db<gt；在这里摆弄