转：http://www.cnblogs.com/kkun/archive/2011/08/10/2133613.html
看到某段代码,不懂:
SELECT
GROUPING(GRP_A),
GRP_A,GRP_B,COUNT(0)
FROM T
GROUP BY
GROUPING SETS(GRP_A,GRP_B)
于是百度,GOOGLE,MSDN,F1大概有点明白了,是个高级货,GROUP BY 的高级功能,
| Inside Microsoft® SQL Server™ 2005 T-SQL Querying中这么解释的,也蛮准确 |
CUBE | ROLLUP: Supergroups (groups of groups)
MSSQL2K5新增的关键字,汗颜,6年过去了,今天才知道天地间有这么个东西
心急的朋友不必看完,点此跳转即可,虽然是基于ORACLE的,语法基本一样
除了上述的GROUPING SETS外,还有一些相关的关键字,如
CUBE,ROLLUP,GROUPING SETS
整明白它,搞点基础数据是必须的,嫌麻烦也不行,对我来说算得上是个大活儿了
表及基础数据
CREATE TABLE T(GRP_A VARCHAR(20),GRP_B VARCHAR(20),VAL INT)
INSERT INTO T(GRP_A,GRP_B,VAL)
SELECT 'a1','b1',10 union all
SELECT 'a1','b1',20 union all
SELECT 'a1','b2',30 union all
SELECT 'a1','b2',40 union all
SELECT 'a1','b2',50 union all
SELECT 'a2','b3',12 union all
SELECT 'a2','b3',22 union all
SELECT 'a2','b3',32
SELECT * FROM T
F5执行后输出
GRP_A GRP_B VAL
-------------------- -------------------- -----------
a1 b1 10
a1 b1 20
a1 b2 30
a1 b2 40
a1 b2 50
a2 b3 12
a2 b3 22
a2 b3 32
嗯,就这么一张表,目前为止还不算复杂
GROUPING SETS
来对比两段代码,一个使用GROUP,另一个使用GROUPING SETS
SELECT GRP_A,NULL AS GRP_B,COUNT(0) FROM T GROUP BY GRP_A
UNION ALL
SELECT NULL AS GRP_A,GRP_B,COUNT(0) FROM T GROUP BY GRP_B
----------------------------
SELECT
GRP_A,GRP_B,COUNT(0)
FROM T
GROUP BY
GROUPING SETS(GRP_B,GRP_A)
执行结果一致:
GRP_A GRP_B
-------------------- -------------------- -----------
a1 NULL 5
a2 NULL 3
NULL b1 2
NULL b2 3
NULL b3 3
(5 行受影响)
GRP_A GRP_B
-------------------- -------------------- -----------
a1 NULL 5
a2 NULL 3
NULL b1 2
NULL b2 3
NULL b3 3
(5 行受影响)
第一段代码就是第二段代码的解释,没错,按照GROUPING SETS里的元素分别进行COUNT统计,最后再UNION起来
理解这个GROUPING SETS非常重要,下边的推导就基于它,再看段代码来学习下ROLLUP关键字
ROLLUP
先看两段等效的SQL语句,同样的,前者即是后者的解释:
SELECT
GRP_A,GRP_B,GRP_C,COUNT(1)
FROM T
GROUP BY
GROUPING SETS(
(GRP_A,GRP_B,GRP_C),
(GRP_A,GRP_B),
GRP_A,
()
)
ORDER BY 1,2,3,4
-------------------
SELECT
GRP_A,GRP_B,GRP_C,COUNT(1)
FROM T
GROUP BY
ROLLUP(GRP_A,GRP_B,GRP_C)
ORDER BY 1,2,3,4
输出相同的结果:
GRP_A GRP_B GRP_C
-------------------- -------------------- -------------------- -----------
NULL NULL NULL 8
a1 NULL NULL 5
a1 b1 NULL 2
a1 b1 c1 1
a1 b1 c2 1
a1 b2 NULL 3
a1 b2 c2 1
a1 b2 c3 2
a2 NULL NULL 3
a2 b3 NULL 3
a2 b3 c3 3
(11 行受影响)
GRP_A GRP_B GRP_C
-------------------- -------------------- -------------------- -----------
NULL NULL NULL 8
a1 NULL NULL 5
a1 b1 NULL 2
a1 b1 c1 1
a1 b1 c2 1
a1 b2 NULL 3
a1 b2 c2 1
a1 b2 c3 2
a2 NULL NULL 3
a2 b3 NULL 3
a2 b3 c3 3
(11 行受影响)
多了一列啊,表结构有修改,新的建表脚本如下:
--DROP TABLE T
CREATE TABLE T(GRP_A VARCHAR(20),GRP_B VARCHAR(20),GRP_C VARCHAR(20),VAL INT)
INSERT INTO T(GRP_A,GRP_B,GRP_C,VAL)
SELECT 'a1','b1','c1',10 union all
SELECT 'a1','b1','c2',20 union all
SELECT 'a1','b2','c2',30 union all
SELECT 'a1','b2','c3',40 union all
SELECT 'a1','b2','c3',50 union all
SELECT 'a2','b3','c3',12 union all
SELECT 'a2','b3','c3',22 union all
SELECT 'a2','b3','c3',32
SELECT * FROM T
回到刚才那个关键字:ROLLUP
ROLLUP(GRP_A,GRP_B,GRP_C)这一行就等同于
GROUPING SETS(
(GRP_A,GRP_B,GRP_C),
(GRP_A,GRP_B),
GRP_A,
()
)
它又等同什么妮?等同于四个GROUP BY 最后UNION起来!
看上去很像一个"阶乘"效果,递减,就它了,ROLLUP就这么个作用
CUBE
最后看一个关键字:CUBE,它的推导也基于UNION,它与ROLLUP不同的是,会UNION所有组合!
如CUBE(GRP_A,GRP_B,GRP_C)这句与下边这句等效:
GROUPING SETS(
(GRP_A,GRP_B,GRP_C),
(GRP_A,GRP_B),
(GRP_A,GRP_C),
(GRP_B,GRP_C),
GRP_A,
GRP_B,
GRP_C,
()
)
国际惯例,看两段等效的SQL语句:
SELECT
GRP_A,GRP_B,GRP_C,COUNT(1)
FROM T
GROUP BY
GROUPING SETS(
(GRP_A,GRP_B,GRP_C),
(GRP_A,GRP_B),
(GRP_A,GRP_C),
(GRP_B,GRP_C),
GRP_A,
GRP_B,
GRP_C,
()
)
ORDER BY 1,2,3,4
-------------------
SELECT
GRP_A,GRP_B,GRP_C,COUNT(1)
FROM T
GROUP BY
CUBE(GRP_A,GRP_B,GRP_C)
ORDER BY 1,2,3,4
两者的输出一致滴,如下:
GRP_A GRP_B GRP_C
-------------------- -------------------- -------------------- -----------
NULL NULL NULL 8
NULL NULL c1 1
NULL NULL c2 2
NULL NULL c3 5
NULL b1 NULL 2
NULL b1 c1 1
NULL b1 c2 1
NULL b2 NULL 3
NULL b2 c2 1
NULL b2 c3 2
NULL b3 NULL 3
NULL b3 c3 3
a1 NULL NULL 5
a1 NULL c1 1
a1 NULL c2 2
a1 NULL c3 2
a1 b1 NULL 2
a1 b1 c1 1
a1 b1 c2 1
a1 b2 NULL 3
a1 b2 c2 1
a1 b2 c3 2
a2 NULL NULL 3
a2 NULL c3 3
a2 b3 NULL 3
a2 b3 c3 3
(26 行受影响)
GRP_A GRP_B GRP_C
-------------------- -------------------- -------------------- -----------
NULL NULL NULL 8
NULL NULL c1 1
NULL NULL c2 2
NULL NULL c3 5
NULL b1 NULL 2
NULL b1 c1 1
NULL b1 c2 1
NULL b2 NULL 3
NULL b2 c2 1
NULL b2 c3 2
NULL b3 NULL 3
NULL b3 c3 3
a1 NULL NULL 5
a1 NULL c1 1
a1 NULL c2 2
a1 NULL c3 2
a1 b1 NULL 2
a1 b1 c1 1
a1 b1 c2 1
a1 b2 NULL 3
a1 b2 c2 1
a1 b2 c3 2
a2 NULL NULL 3
a2 NULL c3 3
a2 b3 NULL 3
a2 b3 c3 3
(26 行受影响)
GROUPING SETS(
(1,2,3),
(1,2),
1,
()
)
3,GROUPING sets(1,2)等效于
SELECT 1,COUNT FROM T GROUP BY 1 UNION ALL
SELECT 2,COUNT FROM T GROUP BY 2(伪代码)
有不对的,错误的地方还请多多包涵,我相信不写出来就很难发现自己理解错误或偏差的地方,多提宝贵意见,回复有时候比文章好看!
参考资料
http://www.sqlsnippets.com/en/topic-13128.html
http://download.oracle.com/docs/cd/B28359_01/server.111/b28313/aggreg.htm#i1007021
看到某段代码,不懂:
SELECT
GROUPING(GRP_A),
GRP_A,GRP_B,COUNT(0)
FROM T
GROUP BY
GROUPING SETS(GRP_A,GRP_B)
于是百度,GOOGLE,MSDN,F1大概有点明白了,是个高级货,GROUP BY 的高级功能,
| Inside Microsoft® SQL Server™ 2005 T-SQL Querying中这么解释的,也蛮准确 |
CUBE | ROLLUP: Supergroups (groups of groups)
MSSQL2K5新增的关键字,汗颜,6年过去了,今天才知道天地间有这么个东西
心急的朋友不必看完,点此跳转即可,虽然是基于ORACLE的,语法基本一样
除了上述的GROUPING SETS外,还有一些相关的关键字,如
CUBE,ROLLUP,GROUPING SETS
整明白它,搞点基础数据是必须的,嫌麻烦也不行,对我来说算得上是个大活儿了
表及基础数据
CREATE TABLE T(GRP_A VARCHAR(20),GRP_B VARCHAR(20),VAL INT)
INSERT INTO T(GRP_A,GRP_B,VAL)
SELECT 'a1','b1',10 union all
SELECT 'a1','b1',20 union all
SELECT 'a1','b2',30 union all
SELECT 'a1','b2',40 union all
SELECT 'a1','b2',50 union all
SELECT 'a2','b3',12 union all
SELECT 'a2','b3',22 union all
SELECT 'a2','b3',32
SELECT * FROM T
F5执行后输出
GRP_A GRP_B VAL
-------------------- -------------------- -----------
a1 b1 10
a1 b1 20
a1 b2 30
a1 b2 40
a1 b2 50
a2 b3 12
a2 b3 22
a2 b3 32
嗯,就这么一张表,目前为止还不算复杂
GROUPING SETS
来对比两段代码,一个使用GROUP,另一个使用GROUPING SETS
SELECT GRP_A,NULL AS GRP_B,COUNT(0) FROM T GROUP BY GRP_A
UNION ALL
SELECT NULL AS GRP_A,GRP_B,COUNT(0) FROM T GROUP BY GRP_B
----------------------------
SELECT
GRP_A,GRP_B,COUNT(0)
FROM T
GROUP BY
GROUPING SETS(GRP_B,GRP_A)
执行结果一致:
GRP_A GRP_B
-------------------- -------------------- -----------
a1 NULL 5
a2 NULL 3
NULL b1 2
NULL b2 3
NULL b3 3
(5 行受影响)
GRP_A GRP_B
-------------------- -------------------- -----------
a1 NULL 5
a2 NULL 3
NULL b1 2
NULL b2 3
NULL b3 3
(5 行受影响)
第一段代码就是第二段代码的解释,没错,按照GROUPING SETS里的元素分别进行COUNT统计,最后再UNION起来
理解这个GROUPING SETS非常重要,下边的推导就基于它,再看段代码来学习下ROLLUP关键字
ROLLUP
先看两段等效的SQL语句,同样的,前者即是后者的解释:
SELECT
GRP_A,GRP_B,GRP_C,COUNT(1)
FROM T
GROUP BY
GROUPING SETS(
(GRP_A,GRP_B,GRP_C),
(GRP_A,GRP_B),
GRP_A,
()
)
ORDER BY 1,2,3,4
-------------------
SELECT
GRP_A,GRP_B,GRP_C,COUNT(1)
FROM T
GROUP BY
ROLLUP(GRP_A,GRP_B,GRP_C)
ORDER BY 1,2,3,4
输出相同的结果:
GRP_A GRP_B GRP_C
-------------------- -------------------- -------------------- -----------
NULL NULL NULL 8
a1 NULL NULL 5
a1 b1 NULL 2
a1 b1 c1 1
a1 b1 c2 1
a1 b2 NULL 3
a1 b2 c2 1
a1 b2 c3 2
a2 NULL NULL 3
a2 b3 NULL 3
a2 b3 c3 3
(11 行受影响)
GRP_A GRP_B GRP_C
-------------------- -------------------- -------------------- -----------
NULL NULL NULL 8
a1 NULL NULL 5
a1 b1 NULL 2
a1 b1 c1 1
a1 b1 c2 1
a1 b2 NULL 3
a1 b2 c2 1
a1 b2 c3 2
a2 NULL NULL 3
a2 b3 NULL 3
a2 b3 c3 3
(11 行受影响)
多了一列啊,表结构有修改,新的建表脚本如下:
--DROP TABLE T
CREATE TABLE T(GRP_A VARCHAR(20),GRP_B VARCHAR(20),GRP_C VARCHAR(20),VAL INT)
INSERT INTO T(GRP_A,GRP_B,GRP_C,VAL)
SELECT 'a1','b1','c1',10 union all
SELECT 'a1','b1','c2',20 union all
SELECT 'a1','b2','c2',30 union all
SELECT 'a1','b2','c3',40 union all
SELECT 'a1','b2','c3',50 union all
SELECT 'a2','b3','c3',12 union all
SELECT 'a2','b3','c3',22 union all
SELECT 'a2','b3','c3',32
SELECT * FROM T
回到刚才那个关键字:ROLLUP
ROLLUP(GRP_A,GRP_B,GRP_C)这一行就等同于
GROUPING SETS(
(GRP_A,GRP_B,GRP_C),
(GRP_A,GRP_B),
GRP_A,
()
)
它又等同什么妮?等同于四个GROUP BY 最后UNION起来!
看上去很像一个"阶乘"效果,递减,就它了,ROLLUP就这么个作用
CUBE
最后看一个关键字:CUBE,它的推导也基于UNION,它与ROLLUP不同的是,会UNION所有组合!
如CUBE(GRP_A,GRP_B,GRP_C)这句与下边这句等效:
GROUPING SETS(
(GRP_A,GRP_B,GRP_C),
(GRP_A,GRP_B),
(GRP_A,GRP_C),
(GRP_B,GRP_C),
GRP_A,
GRP_B,
GRP_C,
()
)
国际惯例,看两段等效的SQL语句:
SELECT
GRP_A,GRP_B,GRP_C,COUNT(1)
FROM T
GROUP BY
GROUPING SETS(
(GRP_A,GRP_B,GRP_C),
(GRP_A,GRP_B),
(GRP_A,GRP_C),
(GRP_B,GRP_C),
GRP_A,
GRP_B,
GRP_C,
()
)
ORDER BY 1,2,3,4
-------------------
SELECT
GRP_A,GRP_B,GRP_C,COUNT(1)
FROM T
GROUP BY
CUBE(GRP_A,GRP_B,GRP_C)
ORDER BY 1,2,3,4
两者的输出一致滴,如下:
GRP_A GRP_B GRP_C
-------------------- -------------------- -------------------- -----------
NULL NULL NULL 8
NULL NULL c1 1
NULL NULL c2 2
NULL NULL c3 5
NULL b1 NULL 2
NULL b1 c1 1
NULL b1 c2 1
NULL b2 NULL 3
NULL b2 c2 1
NULL b2 c3 2
NULL b3 NULL 3
NULL b3 c3 3
a1 NULL NULL 5
a1 NULL c1 1
a1 NULL c2 2
a1 NULL c3 2
a1 b1 NULL 2
a1 b1 c1 1
a1 b1 c2 1
a1 b2 NULL 3
a1 b2 c2 1
a1 b2 c3 2
a2 NULL NULL 3
a2 NULL c3 3
a2 b3 NULL 3
a2 b3 c3 3
(26 行受影响)
GRP_A GRP_B GRP_C
-------------------- -------------------- -------------------- -----------
NULL NULL NULL 8
NULL NULL c1 1
NULL NULL c2 2
NULL NULL c3 5
NULL b1 NULL 2
NULL b1 c1 1
NULL b1 c2 1
NULL b2 NULL 3
NULL b2 c2 1
NULL b2 c3 2
NULL b3 NULL 3
NULL b3 c3 3
a1 NULL NULL 5
a1 NULL c1 1
a1 NULL c2 2
a1 NULL c3 2
a1 b1 NULL 2
a1 b1 c1 1
a1 b1 c2 1
a1 b2 NULL 3
a1 b2 c2 1
a1 b2 c3 2
a2 NULL NULL 3
a2 NULL c3 3
a2 b3 NULL 3
a2 b3 c3 3
(26 行受影响)
GROUPING SETS(
(1,2,3),
(1,2),
1,
()
)
3,GROUPING sets(1,2)等效于
SELECT 1,COUNT FROM T GROUP BY 1 UNION ALL
SELECT 2,COUNT FROM T GROUP BY 2(伪代码)
有不对的,错误的地方还请多多包涵,我相信不写出来就很难发现自己理解错误或偏差的地方,多提宝贵意见,回复有时候比文章好看!
参考资料
http://www.sqlsnippets.com/en/topic-13128.html
http://download.oracle.com/docs/cd/B28359_01/server.111/b28313/aggreg.htm#i1007021
