SQL SERVER 分组组合GROUPING SETS

1、分组汇总--概述

SQL SERVER增强了GROUP BY的功能，GROUPING SETS 子句允许你指定多个GROUP BY选项，可以通过一条SELECT语句实现复杂繁琐的多条SELECT语句的查询，并且更加的高效。

GROUPING SETS 的 GROUP BY 子句可以生成一个等效于由多个简单 GROUP BY 子句的 UNION ALL 生成的结果集。

GROUPING SETS 可以生成等效于由简单 GROUP BY、ROLLUP 或 CUBE 操作生成的结果。

GROUPING SETS、ROLLUP 或CUBE 的不同组合可以生成等效的结果集。

 

2、分组汇总--方法实例

SQL SERVER增强了WITH ROLLUP 能很方便的对同一个结果及进行汇总

下面通过实例说明GROUP BY、GROUPING SETS、WITH ROLLUP的用法和区别：

SQL 使用 ROLLUP 汇总数据

（1）、 准备基础数据

CREATE TABLE #TBLPOPULATION
    (
      COUN NVARCHAR (100) ,--国家
      PROV NVARCHAR (100) ,--省份
      CITY NVARCHAR (100) ,--城市
      POPU INT --人口数量（百万）
    );
DELETE  FROM #TBLPOPULATION;
INSERT  INTO #TBLPOPULATION VALUES  ( '中国', '河南', '郑州', 9 );
INSERT  INTO #TBLPOPULATION VALUES  ( '中国', '河南', '许昌', 2 );
INSERT  INTO #TBLPOPULATION VALUES  ( '中国', '河北', '石家庄', 6 );
INSERT  INTO #TBLPOPULATION VALUES  ( '中国', '河北', '沧州', 2 );
GO
SELECT * FROM  #TBLPOPULATION

（2）、GROUP BY分组

--2.1常用分组方法（单分组）
SELECT COUN ,PROV ,SUM(POPU) AS POPU 
FROM #TBLPOPULATION
GROUP BY COUN, PROV
ORDER BY COUN, PROV

（3）GROUPING SETS分组

--3.1分别多组合并后UNION
SELECT COUN ,PROV ,SUM(POPU) AS POPU  
FROM #TBLPOPULATION
GROUP BY GROUPING SETS(COUN ,PROV)
ORDER BY COUN, PROV

--3.2分别多组多级合并后UNION--等价于ROLLUP,但比ROLLUP少一行合计
SELECT COUN ,ISNULL(PROV,'合计') AS PROV,SUM( POPU ) AS POPU 
FROM #TBLPOPULATION
GROUP BY GROUPING SETS(
(COUN),
(COUN, PROV)
)
ORDER BY COUN, PROV

--3.3分别多组多级合并后UNION--等价于ROLLUP,但比ROLLUP少一行合计
SELECT ISNULL (COUN, '合计') AS COUN,ISNULL( PROV,'合计' ) AS PROV,ISNULL( CITY,'合计' ) AS CITY,SUM( POPU ) AS POPU  
FROM #TBLPOPULATION
GROUP BY GROUPING SETS(
(COUN),
(COUN, PROV),
(COUN, PROV,CITY )
)
ORDER BY COUN, PROV ,CITY

 

 （4）WITH ROLLUP汇总

--ROLLUP 汇总数据（比GROUPING SETS 多一合计）
SELECT  ISNULL (COUN, '合计') AS COUN,ISNULL( PROV,'合计' ) AS PROV,ISNULL( CITY,'合计' ) AS CITY,SUM( POPU ) AS POPU
FROM    #TBLPOPULATION
GROUP BY COUN, PROV, CITY
WITH ROLLUP;

--另外写法，结果于上相同
SELECT   CASE WHEN ( GROUPING( COUN) = 1 ) THEN N'合计' ELSE ISNULL (COUN, 'UNKNOWN') END AS COUN ,
       CASE WHEN ( GROUPING (PROV) = 1 ) THEN N'合计' ELSE ISNULL (PROV, 'UNKNOWN') END AS PROV ,
       CASE WHEN ( GROUPING (CITY) = 1 ) THEN N'合计' ELSE ISNULL (CITY, 'UNKNOWN') END AS CITY ,
       SUM(POPU ) AS POPU
FROM    #TBLPOPULATION
GROUP BY COUN, PROV, CITY
WITH ROLLUP;

 

 
DROP TABLE #TBLPOPULATION;