ErrorMessage: ORA-00979: 不是 GROUP BY 表达式

记录一次oracle报错：ErrorMessage: ORA-00979: 不是 GROUP BY 表达式

oracle与其他数据库差别

原因：select 列表项中不存在的列可以出现在group by的列表项中，但反过来就不行了，在select列表项中出现的列必须全部出现在group by后面(聚合函数除外)

解决：在select中出现的列必须全部出现在group by的后面

 
 SELECT SUM(C.total_in) as total_in, C.VEN_NO as VEN_NO, C.VENDOR_NAM as VENDOR_NAM, SUM(C.total_out) as total_out from (
(
 SELECT count(*) AS total_in,A.VEN_NO, A.VENDOR_NAM, 0 total_out FROM 
 (SELECT DISTINCT EMP_NAM, VEN_NO,VENDOR_NAM,CHECK_TYP  FROM LIEMS99.VIEW_ENTRY WHERE TO_CHAR(CHECK_TIM,'yyyy-mm-dd') = '2019-04-24'  AND CHECK_TYP='I') A
 GROUP BY A.VEN_NO,A.VENDOR_NAM
)
 union all
(
 SELECT 0 total_in,B.VEN_NO, B.VENDOR_NAM, count(*) AS total_out FROM 
 (SELECT DISTINCT EMP_NAM, VEN_NO,VENDOR_NAM,CHECK_TYP  FROM LIEMS99.VIEW_ENTRY WHERE TO_CHAR(CHECK_TIM,'yyyy-mm-dd') = '2019-04-24'   AND CHECK_TYP='O') B
 GROUP BY B.VEN_NO,B.VENDOR_NAM
)
) C  group by C.VEN_NO,C.VENDOR_NAM ORDER BY total_in desc

 

转载于:https://www.cnblogs.com/zhou-pan/p/10932573.html