分组汇总之ROLLUP与CUBE

最新推荐文章于 2024-07-29 15:01:12 发布

jsklnice

最新推荐文章于 2024-07-29 15:01:12 发布

阅读量91

点赞数

分类专栏： SQL 文章标签： sql

本文链接：https://blog.csdn.net/jsklnice/article/details/105219643

版权

SQL 专栏收录该内容

11 篇文章 0 订阅

订阅专栏

1.ROLLUP()

SELECT Category, Year, SUM(Amount) FROM expenses 
GROUP BY ROLLUP(Category, Year) ORDER BY 1,2, GROUPING_ID();  

 Category    | Year |  SUM
-------------+------+--------
 Books       | 2005 |  39.98 
 Books       | 2007 |  29.99
 Books       | 2008 |  29.99
 Books       |      |  99.96
 Electricity | 2005 | 109.99
 Electricity | 2006 | 109.99
 Electricity | 2007 | 229.98
 Electricity |      | 449.96
             |      | 549.92

SELECT Category, Year, SUM(Amount) FROM expenses
GROUP BY ROLLUP(Category,Year) HAVING GROUPING_ID(Category,Year)<3
ORDER BY 1, 2, GROUPING_ID();

 Category    | Year |  SUM
-------------+------+--------
 Books       | 2005 |  39.98
 Books       | 2007 |  29.99
 Books       | 2008 |  29.99
 Books       |      |  99.96
 Electricity | 2005 | 109.99
 Electricity | 2006 | 109.99
 Electricity | 2007 | 229.98
 Electricity |      | 449.96

2.CUBE()

 SELECT Category, Year, SUM(Amount) FROM expenses 
 GROUP BY CUBE(Category, Year) ORDER BY 1, 2, GROUPING_ID();  
 
 Category    | Year |  SUM
-------------+------+-------- 
 Books       | 2005 |  39.98
 Books       | 2007 |  29.99
 Books       | 2008 |  29.99
 Books       |      |  99.96
 Electricity | 2005 | 109.99
 Electricity | 2006 | 109.99
 Electricity | 2007 | 229.98
 Electricity |      | 449.96
             | 2005 | 149.97
             | 2006 | 109.99
             | 2007 | 259.97
             | 2008 |  29.99
             |      | 549.92

3.GROUPING_ID说明

SELECT Category, Year, SUM(Amount), 
GROUPING(Category), GROUPING(Year) FROM expenses 
GROUP BY ROLLUP(Category, Year) ORDER BY Category, Year, GROUPING_ID();

  Category   | Year |  SUM   | GROUPING | GROUPING
-------------+------+--------+----------+----------
 Books       | 2005 |  39.98 |        0 |        0
 Books       | 2007 |  29.99 |        0 |        0
 Books       | 2008 |  29.99 |        0 |        0
 Books       |      |  99.96 |        0 |        1
 Electricity | 2005 | 109.99 |        0 |        0
 Electricity | 2006 | 109.99 |        0 |        0
 Electricity | 2007 | 229.98 |        0 |        0
 Electricity |      | 449.96 |        0 |        1
             |      | 549.92 |        1 |        1