SQLSERVER分组组合GROUPINGSETS

SQLSERVER分组组合GROUPINGSETS
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;。