sql - Oracle --> NOT A GROUP BY EXPRESSION 错误

Question

下面是我的查询。我不确定是什么原因导致 Not A group by expression 错误。任何帮助深表感谢。 =)

SELECT 
    DTA_PRODUCAO AS AF002_DTA_PRODUCAO
,   COD_MATERIAL AS AD001_COD_MATERIAL 
,   COD_FILIAL AS AD004_COD_FILIAL
,   NVL(SUM(DQTD_CONSUMOT),0)  AS AF002_DQTD_CONSUMOT
,  (SELECT NVL(SUM(DQTD_CONSUMOT),0) FROM MYDB.DSO_TERMICO M WHERE M.COD_FILIAL = D.COD_FILIAL AND M.CODPRO = D.COD_MATERIAL   AND M.ZTL_DATA >= SUBSTR(D.DTA_PRODUCAO,0,6)||'01' AND M.ZTL_DATA <= D.DTA_PRODUCAO) AS AF002_MQTD_CONSUMOT
,  (SELECT NVL(SUM(DQTD_CONSUMOT),0) FROM MYDB.DSO_TERMICO M WHERE M.COD_FILIAL = D.COD_FILIAL AND M.CODPRO = D.COD_MATERIAL   AND M.ZTL_DATA >= SUBSTR(D.DTA_PRODUCAO,0,4)||'0101' AND M.ZTL_DATA <= D.DTA_PRODUCAO) AS AF002_AQTD_CONSUMOT
FROM
    (SELECT 
       DTA_PRODUCAO
       ,CODPRO AS COD_MATERIAL 
       ,COD_FILIAL AS COD_FILIAL 
      , CASE WHEN DAUX.ZTL_DATA = TMP.DTA_PRODUCAO THEN DAUX.DIACONSUMO ELSE 0 END AS DQTD_CONSUMOT
      FROM MYDB.DSO_TERMICO DAUX
      INNER JOIN (SELECT DISTINCT MYDB.DSO_TERMICO.ZTL_DATA AS DTA_PRODUCAO FROM MYDB.DSO_TERMICO) TMP ON SUBSTR(TMP.DTA_PRODUCAO,1,4)=SUBSTR(DAUX.ZTL_DATA,1,4))
  D
  GROUP BY 
  DTA_PRODUCAO,
  COD_MATERIAL,
  COD_FILIAL
  ORDER BY 2,1; 

Answer 1

我想我明白发生了什么。这是您的子查询:

 (SELECT NVL(SUM(DQTD_CONSUMOT),0)
  FROM MYDB.DSO_TERMICO M
  WHERE M.COD_FILIAL = D.COD_FILIAL AND
        M.CODPRO = D.COD_MATERIAL   AND
        M.ZTL_DATA >= SUBSTR(D.DTA_PRODUCAO,0,6)||'01' AND 
        M.ZTL_DATA <= D.DTA_PRODUCAO
) AS AF002_MQTD_CONSUMOT

一切看起来都很干净。 where 中提到的三个字段位于group by 子句中。表达式 DQTD_CONSUMOT(重要的是 D.DQTD_CONSUMOT)不是，但它在 sum() 中。可能是什么问题？

问题是这是一个标量子查询。所以编译器只在子查询中解释 sum()。它说“这个子查询想要对 D.DQTD_CONSUMOT 求和，但这不是 group by 表达式。错误!”。

你可以通过分解出“常量”来解决这个问题:

 (SELECT count(*)
  FROM MYDB.DSO_TERMICO M
  WHERE M.COD_FILIAL = D.COD_FILIAL AND
        M.CODPRO = D.COD_MATERIAL   AND
        M.ZTL_DATA >= SUBSTR(D.DTA_PRODUCAO,0,6)||'01' AND 
        M.ZTL_DATA <= D.DTA_PRODUCAO
 ) *  NVL(SUM(DQTD_CONSUMOT),0) AS AF002_MQTD_CONSUMOT

我怀疑您也可以通过使用分析函数来解决这个问题。像这样的东西:

sum(DQTD_CONSUMOT) over (partition by D.COD_FILIAL, D.COD_MATERIAL, SUBSTR(D.DTA_PRODUCAO,0,6))

或类似的东西。没有样本数据和结果，很难弄清楚这一切到底在做什么。