时间戳

首页 > 解决方案 > 由于缺少 GROUP BY 子句，为什么 Oracle 不抛出错误消息？

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

问题描述

我在 Oracle 11g 上，很困惑第三个查询甚至可以在下面编译。这不应该引发 ORA-00937 错误吗？其他任何人都可以重现这一点或解释 Oracle 如何或为什么忽略内联视图中缺少 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!
     );

标签： sqloraclegroup-bysubquery

解决方案

这是 Oracle 错误 5520732，已在 11.2 版中修复。如果您有 Oracle Support 帐户，则可以阅读所有相关信息。

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

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!
   );

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

推荐阅读