sql - 为什么 Oracle 不因缺少 GROUP BY 子句而抛出错误消息？

Question

我使用的是 Oracle 11g，我很困惑第三个查询如何在下面编译。这不应该引发 ORA-00937 错误吗？其他人能否重现此内容或解释 Oracle 如何或为何忽略内联 View 中缺少 GROUP BY 子句？ (不过它确实会抛出错误，只是自己运行子查询)

DROP TABLE ZZZ_DELETE_ME;
CREATE TABLE ZZZ_DELETE_ME
(
contract NUMBER(6),
lives INTEGER
 );

 INSERT INTO ZZZ_DELETE_ME (contract,lives) VALUES (123456,100);
 INSERT INTO ZZZ_DELETE_ME (contract,lives) VALUES (123456,50);
 INSERT INTO ZZZ_DELETE_ME (contract,lives) VALUES (123457,100);
 INSERT INTO ZZZ_DELETE_ME (contract,lives) VALUES (123457,50);
 INSERT INTO ZZZ_DELETE_ME (contract,lives) VALUES (123458,100);
 INSERT INTO ZZZ_DELETE_ME (contract,lives) VALUES (123458,50);
 INSERT INTO ZZZ_DELETE_ME (contract,lives) VALUES (123459,100);
 INSERT INTO ZZZ_DELETE_ME (contract,lives) VALUES (123459,50);

 -- query 1 returns 100 for each record (which makes sense)
 SELECT contract, SUM(MAX_LIVES) TOTAL_LIVES
 FROM 
     (
     SELECT contract, MAX(lives) MAX_LIVES
     FROM ZZZ_DELETE_ME 
     GROUP BY contract
     )
 GROUP BY contract;

 -- query 2 returns 400 (which makes sense)
 SELECT SUM(MAX_LIVES) TOTAL_LIVES
 FROM 
     (
     SELECT contract, MAX(lives) MAX_LIVES
     FROM ZZZ_DELETE_ME 
     GROUP BY contract
     );

 -- query 3 returns 100 (but why?  Shouldn't this throw an error?)
 SELECT SUM(MAX_LIVES) TOTAL_LIVES
 FROM 
     (
     SELECT contract, MAX(lives) MAX_LIVES
     FROM ZZZ_DELETE_ME 
     -- THERE'S NO GROUP BY HERE!
     );       

Answer 1

这是 Oracle 错误 5520732，已在 11.2 版中修复。如果您有 Oracle 支持帐户，您可以阅读所有相关内容。

要确认，您可以将查询更改为

SELECT SUM(MAX_LIVES) TOTAL_LIVES
FROM 
   (
   SELECT /*+ NO_MERGE */ contract, MAX(lives) MAX_LIVES
   FROM ZZZ_DELETE_ME 
   -- THERE'S NO GROUP BY HERE!
   );   

...您应该得到预期的错误。