group by扩展函数之cube

最新推荐文章于 2025-06-08 08:02:30 发布

cindy-berry

最新推荐文章于 2025-06-08 08:02:30 发布

阅读量529

点赞数

CC 4.0 BY-SA版权

分类专栏：读书笔记（开发）

本文链接：https://blog.youkuaiyun.com/u012366626/article/details/38962989

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

27 篇文章

订阅专栏

本文探讨了SQL中的CUBE扩展函数，对比了CUBE与ROLLUP的区别。通过实例展示了如何使用CUBE进行多维度数据分析，包括完整的CUBE分组以及部分CUBE分组的用法，帮助读者掌握这一高级统计技巧。

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

/*cube比rollup比较，ROLLUP是按列的顺序从右到左递减分组统计，而cube是各种可能性的分组*/

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

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

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,b.job,SUM(b.sal) sum_sal
FROM dept a,emp b
WHERE a.deptno = b.deptno
GROUP BY b.job
UNION
SELECT NULL,NULL,SUM(b.sal) sum_sal
FROM dept a,emp b
WHERE a.deptno = b.deptno
GROUP BY NULL

-----------------------
/*部分cube分组，group by a.dname,CUBE(b.job),
这样的写法，比group by CUBE(a.dname,b.job)少去了group by b.job和group by null的分组*/

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

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

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