SELECT DISTINCT A.WO_KEY, A.TYPE INPUT, A1.TYPE OUTPUT, SUM (A.QTY) AQTY,
SUM (A1.QTY) A1QTY
FROM SFCS_IO_STATISTICS A,
SFCS_IO_STATISTICS A1,
(SELECT I_WS_ID, O_WS_ID, PROCESS, PART_NO
FROM INV_PN_PROCESS) E
WHERE A.WO_KEY IN (16113, 16204, 16205)
AND A.TYPE = 'I '
AND A.ROUTE_CODE IN E.I_WS_ID
AND A1.WO_KEY = A.WO_KEY
AND A1.ROUTE_CODE IN E.O_WS_ID
AND A1.TYPE = 'O '
ORA-00937: 不是一個單一群體的群體函數
這段代碼會出現這個錯誤,
把SUM (A.QTY) AQTY,
SUM (A1.QTY) A1QTY 這兩個拿掉後就不會出錯
已經找過很多地方了。沒答案,請指教!謝謝
------解决方案--------------------
少了group by
------解决方案--------------------
把DISTINCT 去掉,在结尾加 group by A.WO_KEY, A.TYPE, A1.TYPE
因为要用聚合函数sum() 后面就要用到group by,相应的前面select的字段要不是group的字段,要不就是经过聚合处理的字段,如用sum(),min(),max()处理