group by 扩展函数之rollup

最新推荐文章于 2024-12-27 10:07:52 发布

原创最新推荐文章于 2024-12-27 10:07:52 发布 · 1.5k 阅读

0 ·

CC 4.0 BY-SA版权

读书笔记（开发）专栏收录该内容

27 篇文章

订阅专栏

本文介绍了SQL中的ROLLUP扩展函数，用于计算标准分组、小计及合计。通过示例展示了ROLLUP如何按指定列的顺序递减进行分组，以及如何通过UNION实现类似功能。此外，还提到了部分ROLLUP分组的应用，以减少不必要的总计和小计。

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

/*功能：rollup后面指定的列以逗号隔开，计算结果与后面列的顺序有关，
因为rollup分组过程具有方向性，先计算标准分组，然后列从右到左递减计算更高一级的小计，一直到列全部被选完，最后计算合计*/

SELECT a.dname,b.job,SUM(b.sal) sum_sal
FROM dept a,emp b
WHERE a.deptno = b.deptno
GROUP BY ROLLUP(a.dname,b.job);

/*这个语句从结果可以看出，sum_sal那列，950是sales中clerk的总工资，2850是sales中manager的总工资，
5600是sales中salesman的总工资，9400是sales的总工资.从这样的结果可以看到，既有标准分组，又有多种小计、合计*/
--------------

SELECT a.dname,b.job,SUM(b.sal) sum_sal
FROM dept a,emp b
WHERE a.deptno = b.deptno
GROUP BY a.dname,b.job
UNION
SELECT a.dname,NULL,SUM(b.sal) sum_sal
FROM dept a,emp b
WHERE a.deptno = b.deptno
GROUP BY a.dname
UNION
SELECT NULL,NULL,SUM(b.sal) sum_sal
FROM dept a,emp b
WHERE a.deptno = b.deptno
GROUP BY NULL;

-------------
/*部分rollup分组，group by to_char(b.hiredate,'yyyy'),a.dname,ROLLUP(b.job),
这样的写法，比group by ROLLUP(to_char(b.hiredate,'yyyy'),a.dname,b.job)少去了标准分组的总合计，也少掉了按年份的小计,
即少去了group by to_char(b.hiredate,'yyyy')和group by null*/

SELECT to_char(b.hiredate,'yyyy') hire_year,a.dname,b.job,SUM(sal) sum_sal
FROM dept a,emp b
WHERE a.deptno = b.deptno
GROUP BY to_char(b.hiredate,'yyyy'),a.dname,ROLLUP(b.job);

---------------

SELECT to_char(b.hiredate,'yyyy') hire_year,a.dname,b.job,SUM(sal) sum_sal
FROM dept a,emp b
WHERE a.deptno = b.deptno
GROUP BY to_char(b.hiredate,'yyyy'),a.dname,b.job
UNION 
SELECT to_char(b.hiredate,'yyyy') hire_year,a.dname,Null,SUM(sal) sum_sal
FROM dept a,emp b
WHERE a.deptno = b.deptno
GROUP BY to_char(b.hiredate,'yyyy'),a.dname;