SUBSTR组

Question

有人能解释一下为什么下面的代码没有错误吗？

SELECT t1.column1,
       t1.column2,
       t1.column3,
       ctot as Cost,
       (ctot * t1.qty) as FinalCost
FROM Table1 t1

LEFT JOIN (SELECT column1,
                  column2,
                  column3,
                  column4,
             MAX(DateCol),
             AVG(CostCol) KEEP(DENSE_RANK LAST ORDER BY t2.DateCol) as ctot
FROM Table2 t2
GROUP BY column1, column2, SUBSTR(column3, 1, 5), column4) t2
     ON t2.column1 = t1.column1
     AND t2.column2 = t1.column2
     AND t2.column4 = t1.column4
     AND SUBSTR(t1.column3, 1, 5) = SUBSTR(t2.column3, 1, 5)
     WHERE t1.ID1 = '500'
     AND t1.ID2 != '50'

而这段代码将产生错误ORA-00979: not a GROUP BY expression

SELECT t1.column1,
       t1.column2,
       t1.column3,
       ctot as Cost,
       (ctot * t1.qty) as FinalCost
FROM Table1 t1

LEFT JOIN Table2 t2
    ON t1.column1 = t2.column1
   AND t1.column2 = t2.column2

LEFT JOIN (SELECT IdCol as points, column3 FROM Table3) t3
    ON t2.column3 = t3.column3

LEFT JOIN (SELECT column1,
                  column3,
             MAX(DateCol),
             AVG(CostCol) KEEP(DENSE_RANK LAST ORDER BY t4.DateCol) as ctot
FROM Table4 t4
GROUP BY column1, SUBSTR(column3, 1, 5)) t4
    ON t4.column1 = points
   AND SUBSTR(t4.column3, 1, 5) = SUBSTR(t1.column3, 1, 5)
 WHERE t1.ID1 = '500'
   AND t1.ID2 != '50'

我的编辑器在最后一个join语句中突出显示column3是导致错误的原因，并将SUBSTR(column3, 1, 5)替换为GROUP BY语句中的column3修复了此错误。但是为什么在第二个示例中抛出错误，而不是在第一个示例中抛出错误？它与多个join语句有关吗？

Answer 1

您在主选择中使用LEFT JOIN，这意味着甲骨文正在使用SQL92。因此，您必须在GROUP BY clausule exat的SELECT投影部分使用。

埃伊岛在子选择中使用此方法：

SELECT column1,
    column2,
    SUBSTR(column3, 1, 5) as column3, -- column3 after substr()
    column4,
    MAX(DateCol),
    AVG(CostCol) KEEP(DENSE_RANK LAST ORDER BY t2.DateCol) AS ctot
FROM Table2 t2
GROUP BY column1, column2, SUBSTR(column3, 1, 5), column4

Answer 2

在声明结束时额外的) ..。

应该只是

SELECT
  t1.column1,
  t1.column2,
  t1.column3,
  ctot AS Cost,
  (ctot * t1.qty) AS FinalCost
FROM Table1 t1

LEFT JOIN (SELECT
  column1,
  column2,
  column3,
  column4,
  MAX(DateCol),
  AVG(CostCol) KEEP (DENSE_RANK LAST ORDER BY t2.DateCol) AS ctot
FROM Table2 t2
GROUP BY column1,
         column2,
         SUBSTR(column3, 1, 5),
         column4) t2

  ON t2.column1 = t1.column1
  AND t2.column2 = t1.column2
  AND t2.column4 = t1.column4
  AND SUBSTR(t1.column3, 1, 5) = SUBSTR(t2.column3, 1, 5)
WHERE t1.ID1 = '500'
AND t1.ID2 != '50'