SQL SERVER的分类汇总

SQL SERVER的分类汇总

SQL SERVER中使用GROUP BY对数据进行分类汇总，我们也可以使用WITH ROLLUP和WITH CUBE配合GROUP BY进行“增强”了的分类汇总，那么他们两个是如何增强GROUP BY的汇总能力的呢？
一.功能增强
1.使用WITH ROLLUP
用下面的例子说明,GROUP使用了3个分组字段：GROUP BY A, B, C WITH ROLLUP就相当于

GROUP BY A, B, C UNION ALL
GROUP BY A, B    UNION ALL
GROUP BY A       UNION ALL
GROUP BY NULL

2.使用WITH CUBE
用下面的例子说明，GROUP使用了3个分组字段：GROUP BY A, B, C WITH CUBE就相当于

GROUP BY A, B, C UNION ALL
GROUP BY A, B    UNION ALL
GROUP BY A, C    UNION ALL
GROUP BY B, C    UNION ALL
GROUP BY A       UNION ALL
GROUP BY B       UNION ALL
GROUP BY C       UNION ALL
GROUP BY NULL  

二.说明：
1.WITH ROLLUP和CUBE可以使用HAVING和GROUPING对产生的汇总行进行再次过滤。
2.GROUP BY A, B, C WITH ROLLUP/CUBE会将生成的汇总排序号，而GROUP BY/UNION ALL 却没有，只有手工ORDER BY排序了。
3.GROUP BY的字段数若不是上例的3个，其规律与上面一致。

三.举例：
DECLARE @T TABLE (大类 CHAR, 小类 CHAR, VAL INT)

INSERT INTO @T
SELECT 'A', 'B', 1 UNION ALL
SELECT 'A', 'B', 2 UNION ALL
SELECT 'A', 'C', 3 UNION ALL
SELECT 'A', 'C', 4 UNION ALL
SELECT 'D', 'B', 5 UNION ALL
SELECT 'D', 'B', 6 UNION ALL
SELECT 'D', 'C', 7 UNION ALL
SELECT 'D', 'C', 8

--使用WITH ROLLUP汇总
SELECT 大类, 小类, SUM(VAL)
FROM @T
GROUP BY 大类, 小类 WITH ROLLUP
/*
大类   小类              
---- ---- -----------
A    B    3
A    C    7
A    NULL 10
D    B    11
D    C    15
D    NULL 26
NULL NULL 36
*/

--WITH ROLLUP 相当于下面的UNION ALL
SELECT 大类, 小类, SUM(VAL)
FROM @T
GROUP BY 大类, 小类
UNION ALL
SELECT 大类, NULL, SUM(VAL)
FROM @T
GROUP BY 大类
UNION ALL
SELECT NULL, NULL, SUM(VAL)
FROM @T
/*
大类   小类              
---- ---- -----------
A    B    3
D    B    11
A    C    7
D    C    15
A    NULL 10
D    NULL 26
NULL NULL 36
*/

--使用WITH CUBE汇总
SELECT 大类, 小类, SUM(VAL)
FROM @T
GROUP BY 大类, 小类 WITH CUBE
/*

大类   小类              
---- ---- -----------
A    B    3
A    C    7
A    NULL 10
D    B    11
D    C    15
D    NULL 26
NULL NULL 36
NULL B    14
NULL C    22
*/

--WITH CUBE 相当于下面的UNION ALL
SELECT 大类, 小类, SUM(VAL)
FROM @T
GROUP BY 大类, 小类
UNION ALL
SELECT 大类, NULL, SUM(VAL)
FROM @T
GROUP BY 大类
UNION ALL
SELECT NULL, 小类, SUM(VAL)
FROM @T
GROUP BY 小类
UNION ALL
SELECT NULL, NULL, SUM(VAL)
FROM @T
/*
大类   小类              
---- ---- -----------
A    B    3
D    B    11
A    C    7
D    C    15
A    NULL 10
D    NULL 26
NULL B    14
NULL C    22
NULL NULL 36
*/