Oracle grouping和grouping sets

最新推荐文章于 2024-11-07 14:30:44 发布

blockheading

最新推荐文章于 2024-11-07 14:30:44 发布

阅读量591

点赞数

CC 4.0 BY-SA版权

分类专栏： Oracle PLSQL/SQL 文章标签： sql

本文链接：https://blog.youkuaiyun.com/u013169075/article/details/56897987

Oracle PLSQL/SQL 专栏收录该内容

28 篇文章

订阅专栏

本文详细介绍了Oracle数据库中的两种分组查询方法：grouping和grouping sets。通过具体实例展示了如何使用这两种方法来获取不同层级的数据汇总信息。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

--Oracle grouping和grouping sets

1.grouping
SCOTT@PROD1> select deptno,job,sum(sal),grouping(deptno) grp_dept,grouping(job) grp_job
  2  from emp
  3  group by rollup(deptno,job);

    DEPTNO JOB	       SUM(SAL)   GRP_DEPT    GRP_JOB
---------- --------- ---------- ---------- ----------
	10 CLERK	   1301 	 0	    0
	10 MANAGER	   2451 	 0	    0
	10 PRESIDENT	   5001 	 0	    0
	10		   8753 	 0	    1
	20 CLERK	   1902 	 0	    0
	20 ANALYST	   6002 	 0	    0
	20 MANAGER	   2976 	 0	    0
	20		  10880 	 0	    1
	30 CLERK	    951 	 0	    0
	30 MANAGER	   2851 	 0	    0
	30 SALESMAN	   5604 	 0	    0
	30		   9406 	 0	    1
			  29039 	 1	    1

13 rows selected.

可以看出grouping值为0时说明这个值是数据库中本来的值，为1说明是统计的结果(也可以说该列为空时是1，不为空时是0)。

2.grouping sets
HR@PROD1> SELECT	 department_id, job_id, null manager_id,avg(salary)
  2  FROM	 employees
  3  GROUP BY (department_id,job_id)
  4  UNION ALL
  5  SELECT	 null department_id, job_id, manager_id,avg(salary)
  6  FROM	 employees
  7  GROUP BY (job_id,manager_id);

DEPARTMENT_ID JOB_ID	 MANAGER_ID AVG(SALARY)
------------- ---------- ---------- -----------
	  110 AC_ACCOUNT		   8300
	   90 AD_VP			  17000
	   50 ST_CLERK			   2785
	   80 SA_REP		     8396.55172
	  110 AC_MGR			  12008
	   50 ST_MAN			   7280
	   80 SA_MAN			  12200
	   50 SH_CLERK			   3215
	   20 MK_MAN			  13000
	   90 AD_PRES			  24000
	   60 IT_PROG			   5760
	  100 FI_MGR			  12008
	   30 PU_CLERK			   2780
	  100 FI_ACCOUNT		   7920
	   70 PR_REP			  10000
	      SA_REP			   7000
	   10 AD_ASST			   4400
	   20 MK_REP			   6000
	   40 HR_REP			   6500
	   30 PU_MAN			  11000
	      AC_MGR		101	  12008
	      SH_CLERK		122	   3200
	      SH_CLERK		124	   2825
	      MK_MAN		100	  13000
	      ST_MAN		100	   7280
	      ST_CLERK		121	   2675
	      SA_REP		148	   8650
	      SH_CLERK		120	   2900
	      AD_ASST		101	   4400
	      AD_PRES			  24000
	      FI_MGR		101	  12008
	      SA_REP		146	   8500
	      SH_CLERK		123	   3475
	      IT_PROG		102	   9000
	      IT_PROG		103	   4950
	      FI_ACCOUNT	108	   7920
	      PU_MAN		100	  11000
	      AC_ACCOUNT	205	   8300
	      ST_CLERK		122	   2700
	      SA_REP		145	   8500
	      HR_REP		101	   6500
	      PR_REP		101	  10000
	      AD_VP		100	  17000
	      ST_CLERK		120	   2625
	      ST_CLERK		124	   2925
	      SA_REP		147  7766.66667
	      SA_REP		149  8333.33333
	      ST_CLERK		123	   3000
	      SH_CLERK		121	   3675
	      MK_REP		201	   6000
	      PU_CLERK		114	   2780
	      SA_MAN		100	  12200

52 rows selected.

HR@PROD1> SELECT	 department_id, job_id,manager_id,avg(salary)
  2  FROM	 employees
  3  GROUP BY GROUPING SETS ((department_id,job_id), (job_id,manager_id));

DEPARTMENT_ID JOB_ID	 MANAGER_ID AVG(SALARY)
------------- ---------- ---------- -----------
	      AC_MGR		101	  12008
	      SH_CLERK		122	   3200
	      SH_CLERK		124	   2825
	      MK_MAN		100	  13000
	      ST_MAN		100	   7280
	      ST_CLERK		121	   2675
	      SA_REP		148	   8650
	      SH_CLERK		120	   2900
	      AD_ASST		101	   4400
	      AD_PRES			  24000
	      FI_MGR		101	  12008
	      SA_REP		146	   8500
	      SH_CLERK		123	   3475
	      IT_PROG		102	   9000
	      IT_PROG		103	   4950
	      FI_ACCOUNT	108	   7920
	      PU_MAN		100	  11000
	      AC_ACCOUNT	205	   8300
	      ST_CLERK		122	   2700
	      SA_REP		145	   8500
	      HR_REP		101	   6500
	      PR_REP		101	  10000
	      AD_VP		100	  17000
	      ST_CLERK		120	   2625
	      ST_CLERK		124	   2925
	      SA_REP		147  7766.66667
	      SA_REP		149  8333.33333
	      ST_CLERK		123	   3000
	      SH_CLERK		121	   3675
	      MK_REP		201	   6000
	      PU_CLERK		114	   2780
	      SA_MAN		100	  12200
	  110 AC_ACCOUNT		   8300
	   90 AD_VP			  17000
	   50 ST_CLERK			   2785
	   80 SA_REP		     8396.55172
	  110 AC_MGR			  12008
	   50 ST_MAN			   7280
	   80 SA_MAN			  12200
	   50 SH_CLERK			   3215
	   20 MK_MAN			  13000
	   90 AD_PRES			  24000
	   60 IT_PROG			   5760
	  100 FI_MGR			  12008
	   30 PU_CLERK			   2780
	  100 FI_ACCOUNT		   7920
	   70 PR_REP			  10000
	      SA_REP			   7000
	   10 AD_ASST			   4400
	   20 MK_REP			   6000
	   40 HR_REP			   6500
	   30 PU_MAN			  11000

52 rows selected.

GROUPING SETS就是将多个分组合并，写union all也可以同样完成。