ORACLE中sql语句中同时使用decode与聚合函数时的注意事项

最新推荐文章于 2024-05-01 16:44:51 发布

沉迷过后的天空

最新推荐文章于 2024-05-01 16:44:51 发布

阅读量1k

点赞数

分类专栏：数据库 Oracle 文章标签： oracle sql 数据库

本文链接：https://blog.csdn.net/Make_LCMTK/article/details/122822359

版权

数据库同时被 2 个专栏收录

2 篇文章 0 订阅

订阅专栏

Oracle

2 篇文章 0 订阅

订阅专栏

Oracle数据库中同时使用decode与聚合函数的注意事项

1. sql语句必须使用group by语句，并且将select中的查询列表中时项都必须有(聚合函数除外)，否则报 > ORA-00979: 不是 GROUP BY 表达式的错误！！！同时注意逗号，多逗号会出现 > ORA-00936: 缺失表达式的错误！！！少逗号会出现 > ORA-00933: SQL 命令未正确结束的错误

示例1:未列全查询列表

	SELECT
		DECODE(U.ID, NULL, Z.ZTNAME, D.DEPARTMENTNAME) ZTNAME,
		Z.YEARV YEARV,
		Z.KMCODE KMCODE,
		Z.KMNAME KMNAME,
		DECODE(U.ID, NULL, Z.ZTCODE, D.EXT4) ZTCODE,
		DECODE(Z.ZTTYPE, '人员档案','部门档案', Z.ZTTYPE) ZTTYPE,
		SUM(Z.YSAMOUNT) YSAMOUNT,
		SUM(Z.ZFWC) ZFWC,
		SUM(Z.BDGCOMP) BDGCOMP,
		SUM(Z.NEWZFWC) NEWZFWC,
		SUM(Z.NEWBDGCOMP) NEWBDGCOMP,
		SUM(Z.TOT) TOT,
		SUM(Z.MEABDGCOMP) MEABDGCOMP,
		SUM(Z.BALANCE) BALANCE,
		SUM(Z.ZFZT) ZFZT,
		SUM(Z.NEWZFZT) NEWZFZT,
		MAX(Z.DEPARTMENTNAME) DEPARTMENTNAME,
		MAX(Z.DEPTMGR) DEPTMGR,
		MAX(Z.DEPTMGRID) DEPTMGRID
	FROM
		VIEW_ACT_CSTBDGT_MONITOR_T Z
		LEFT JOIN ORGUSER U ON Z.ZTNAME = U.USERNAME
		LEFT JOIN ORGDEPARTMENT D ON U.DEPARTMENTID = D.ID
	GROUP BY 		
		Z.YEARV,
		Z.KMCODE,
		Z.KMNAME

报错：

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

> 时间: 0.02s

示例二：列全查询列表了，但是使用的是DECODE的简称！！这时也会报错 > ORA-00979: 不是 GROUP BY 表达式

	SELECT
		DECODE(U.ID, NULL, Z.ZTNAME, D.DEPARTMENTNAME) ZTNAME,
		Z.YEARV YEARV,
		Z.KMCODE KMCODE,
		Z.KMNAME KMNAME,
		DECODE(U.ID, NULL, Z.ZTCODE, D.EXT4) ZTCODE,
		DECODE(Z.ZTTYPE, '人员档案','部门档案', Z.ZTTYPE) ZTTYPE,
		SUM(Z.YSAMOUNT) YSAMOUNT,
		SUM(Z.ZFWC) ZFWC,
		SUM(Z.BDGCOMP) BDGCOMP,
		SUM(Z.NEWZFWC) NEWZFWC,
		SUM(Z.NEWBDGCOMP) NEWBDGCOMP,
		SUM(Z.TOT) TOT,
		SUM(Z.MEABDGCOMP) MEABDGCOMP,
		SUM(Z.BALANCE) BALANCE,
		SUM(Z.ZFZT) ZFZT,
		SUM(Z.NEWZFZT) NEWZFZT,
		MAX(Z.DEPARTMENTNAME) DEPARTMENTNAME,
		MAX(Z.DEPTMGR) DEPTMGR,
		MAX(Z.DEPTMGRID) DEPTMGRID
	FROM
		VIEW_ACT_CSTBDGT_MONITOR_T Z
		LEFT JOIN ORGUSER U ON Z.ZTNAME = U.USERNAME
		LEFT JOIN ORGDEPARTMENT D ON U.DEPARTMENTID = D.ID
	GROUP BY 
		ZTNAME,
		Z.YEARV,
		Z.KMCODE,
		Z.KMNAME,
		ZTCODE,
		ZTTYPE

报错：

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

> 时间: 0.02s

这是因为Oracle中不认decode函数的简称，必须写全，故正确语句为：

	SELECT
		DECODE(U.ID, NULL, Z.ZTNAME, D.DEPARTMENTNAME) ZTNAME,
		Z.YEARV YEARV,
		Z.KMCODE KMCODE,
		Z.KMNAME KMNAME,
		DECODE(U.ID, NULL, Z.ZTCODE, D.EXT4) ZTCODE,
		DECODE(Z.ZTTYPE, '人员档案','部门档案', Z.ZTTYPE) ZTTYPE,
		SUM(Z.YSAMOUNT) YSAMOUNT,
		SUM(Z.ZFWC) ZFWC,
		SUM(Z.BDGCOMP) BDGCOMP,
		SUM(Z.NEWZFWC) NEWZFWC,
		SUM(Z.NEWBDGCOMP) NEWBDGCOMP,
		SUM(Z.TOT) TOT,
		SUM(Z.MEABDGCOMP) MEABDGCOMP,
		SUM(Z.BALANCE) BALANCE,
		SUM(Z.ZFZT) ZFZT,
		SUM(Z.NEWZFZT) NEWZFZT,
		MAX(Z.DEPARTMENTNAME) DEPARTMENTNAME,
		MAX(Z.DEPTMGR) DEPTMGR,
		MAX(Z.DEPTMGRID) DEPTMGRID
	FROM
		VIEW_ACT_CSTBDGT_MONITOR_T Z
		LEFT JOIN ORGUSER U ON Z.ZTNAME = U.USERNAME
		LEFT JOIN ORGDEPARTMENT D ON U.DEPARTMENTID = D.ID
	GROUP BY 
		DECODE(U.ID, NULL, Z.ZTNAME, D.DEPARTMENTNAME),
		Z.YEARV,
		Z.KMCODE,
		Z.KMNAME,
		DECODE(U.ID, NULL, Z.ZTCODE, D.EXT4)
		DECODE(Z.ZTTYPE, '人员档案','部门档案', Z.ZTTYPE)