Oracle奇怪的SUM行为

Oracle奇怪的SUM行为

我有两个查询,据我所知应该提供相同的结果,但不能。显然,我在这里缺少一些要点,希望您能为我提供帮助。

  • 查询(我认为这是错误的):
      SELECT SUM(a.amount) AS A_SUM ---10 727.470
      FROM billdetail a
      INNER JOIN bill c
        ON (a.bill_id = c.bill_id)
      INNER JOIN dates d
        ON (c.date_id = d.date_id)
      WHERE d.year                = '2014'
        AND c.status        <> 'D'
        AND a.status        <> 'D';
    
  • 查询:
    SELECT SUM(C_SUM) ---10 754.279
    FROM
      (
      SELECT SUM(a.amount) AS C_SUM
      FROM billdetail a
      INNER JOIN bill c
        ON (a.bill_id = c.bill_id)
      INNER JOIN dates d
        ON (c.date_id = d.date_id)
      WHERE d.year                = '2014'
        AND c.status        <> 'D'
        AND a.status        <> 'D'
      GROUP BY c.bill_id
      );
    

  • 如您所见,查询1给我10 727.470,而查询2给我10 754.279,因此少了27。

    您能解释一下为什么吗?我想我在这两者上都做同样的事情:只选择2014年以来的账单,然后从那里抓取它们的所有账单明细并汇总金额。但是很明显,我在这里有一些理解上的问题,希望您能为我提供帮助。

    最佳答案

    您可能会遇到“错误4604970,启用了'hash group by'聚合的错误结果”。您可以通过运行alter session set "_gby_hash_aggregation_enabled"=false;避免该错误。这是一个简单的bug测试用例,可悲的是这些年来仍然存在。

    SQL> select stddev(test), count(distinct test) from
      2  (
      3      select 7/9 test from dual
      4      union all
      5      select 7/9 test from dual
      6  );
    select stddev(test), count(distinct test) from
    *
    ERROR at line 1:
    ORA-01428: argument '-.00000000000000000000000000000000000001' is out of range
    
    
    SQL> alter session set "_gby_hash_aggregation_enabled"=false;
    
    Session altered.
    
    SQL> select stddev(test), count(distinct test) from
      2  (
      3      select 7/9 test from dual
      4      union all
      5      select 7/9 test from dual
      6  );
    
    STDDEV(TEST) COUNT(DISTINCTTEST)
    ------------ -------------------
               0                   1
    

    关于sql - Oracle奇怪的SUM行为,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/27960794/

    10-11 02:57