[MSSQL]GROUPING SETS,ROLLUP,CUBE初体验

看到某段代码,不懂:

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 行受影响)

 

推荐乡亲们去原站学习去,点我跳转

 

简单总结下

1,CUBE(1,2,3)等效于

GROUPING SETS(

(1,2,3),

(1,2),

(1,3),

(2,3),

1,

2,

3,

()

)

 

2,ROLLUP(1,2,3)等效于

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

 

猜测您可能对下边的文章感兴趣

SQL SERVER 2008 函数大全 - 字符串函数

SQL2008系统统计函数

[MSSQL]GROUPING SETS,ROLLUP,CUBE初体验

[MSSQL]ROW_NUMBER函数

[MSQL]RANK函数

[MSSQL]NTILE另类分页有么有?!

[MSQL]也说SQL中显示星期几函数

[MSSQL]COALESCE与ISNULL函数

[MSSQL]PIVOT函数

[MSSQL]FOR XML AUTO I

[MSSQL]FOR XML AUTO II

[MSSQL]TRY…CATCH…通用格式

如果您喜欢该博客请点击右下角推荐按钮,您的推荐是作者创作的动力!