SQL SERVER的分类汇总

zgdy

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
*/