GROUPBY中的WITHCUBE、WITHROLLUP原理测试及GROUPING应用

  • Z时代
  • 2024-01-10
  • 分类：综合

database

    前几天，看到一个群友用WITH ROLLUP运算符。由于自个儿没用过，看到概念及结果都云里雾里的，所以突然来了兴趣对生成结果测了一番。

    一、概念：

    WITH CUBE：生成的结果集显示了所选列中值的所有组合的聚合。

    WITH ROLLUP：生成的结果集显示了所选列中值的某一层次结构的聚合。

    GROUPING：当行由 WITH CUBE或WITH ROLLUP运算符添加时，该函数将导致附加列的输出值为 1；当行不由 CUBE 或 ROLLUP 运算符添加时，该函数将导致附加列的输出值为 0。仅在与包含 CUBE 或 ROLLUP 运算符的 GROUP BY 子句相关联的选择列表中才允许分组。

    二、测试：

    1、建立临时表

CREATETABLE #T0
(
[GRADE][VARCHAR](50) NULL,     --年级
[CLASS][VARCHAR](50) NULL,     --班级
[NAME][VARCHAR](50) NULL,      --姓名
[COURSE][VARCHAR](50) NULL,    --学科
[RESULT][NUMERIC](8,2) NULL--成绩
)
CREATETABLE #T1
(
[ID][INT]IDENTITY(1,1) NOTNULL,    --序号
[GRADE][VARCHAR](50) NULL,           --年级
[CLASS][VARCHAR](50) NULL,           --班级
[NAME][VARCHAR](50) NULL,            --姓名
[COURSE][VARCHAR](50) NULL,          --学科
[RESULT][NUMERIC](8,2) NULL--成绩
)
CREATETABLE #T2
(
[ID][INT]IDENTITY(1,1) NOTNULL,    --序号
[GRADE][VARCHAR](50) NULL,           --年级
[CLASS][VARCHAR](50) NULL,           --班级
[NAME][VARCHAR](50) NULL,            --姓名
[COURSE][VARCHAR](50) NULL,          --学科
[RESULT][NUMERIC](8,2) NULL--成绩
)

     2、插入测试数据

INSERTINTO #T0 (GRADE,CLASS,NAME,COURSE,RESULT)
SELECT"2019","CLASS1","9A01","C#",100
UNION
SELECT"2019","CLASS1","9A02","C#",100
UNION
SELECT"2019","CLASS2","9B01","C#",100
UNION
SELECT"2019","CLASS2","9B02","C#",100
UNION
SELECT"2018","CLASS1","8A01","JAVA",100
UNION
SELECT"2018","CLASS1","8A02","JAVA",100
UNION
SELECT"2018","CLASS2","8B01","JAVA",100
UNION
SELECT"2018","CLASS2","8B02","JAVA",100

    查询T0表结果：

    3、GROUP BY

    抛砖引玉，看看常用的GROUP BY排序：默认以SELECT字段顺序(GRADE->CLASS->NAME->COURSE)进行排序，以下两种查询结果是一样的。

SELECT GRADE,CLASS,NAME,COURSE,SUM(RESULT) RESULT
FROM #T0
GROUPBY GRADE,CLASS,NAME,COURSE
SELECT GRADE,CLASS,NAME,COURSE,SUM(RESULT) RESULT
FROM #T0
GROUPBY GRADE,CLASS,NAME,COURSE
ORDERBY GRADE,CLASS,NAME,COURSE

    4、WITH CUBE

    原理1：以GROUP BY字段依次赋以NULL值进行分组聚合。

    原理2：第1个字段(即GRADE字段)生成结果：除原始数据外，以第1个字段固定赋以NULL值，然后其它字段依次赋以NULL值进行分组聚合，结果由右往左进行排序。

    下面开始测第1个字段的结果是怎么来的：

INSERTINTO #T1 (GRADE,CLASS,NAME,COURSE,RESULT)
SELECT GRADE,CLASS,NAME,COURSE,SUM(RESULT) RESULT 
FROM #T0 
GROUPBY GRADE,CLASS,NAME,COURSE
INSERTINTO #T1 (GRADE,CLASS,NAME,COURSE,RESULT)
SELECT"ZZ" GRADE,CLASS,NAME,COURSE,SUM(RESULT) RESULT 
FROM #T0 
GROUPBY CLASS,NAME,COURSE
INSERTINTO #T1 (GRADE,CLASS,NAME,COURSE,RESULT)
SELECT"ZZ" GRADE,"ZZ" CLASS,NAME,COURSE,SUM(RESULT) RESULT 
FROM #T0 
GROUPBY NAME,COURSE
INSERTINTO #T1 (GRADE,CLASS,NAME,COURSE,RESULT)
SELECT"ZZ" GRADE,"ZZ" CLASS,"ZZ" NAME,COURSE,SUM(RESULT) RESULT 
FROM #T0 
GROUPBY COURSE
INSERTINTO #T1 (GRADE,CLASS,NAME,COURSE,RESULT)
SELECT"ZZ" GRADE,"ZZ" CLASS,"ZZ" NAME,"ZZ" COURSE,SUM(RESULT) RESULT 
FROM #T0
--第1个字段结果排序由右往左
INSERTINTO #T2 (GRADE,CLASS,NAME,COURSE,RESULT)
SELECT GRADE,CLASS,NAME,COURSE,RESULT FROM #T1 WHERE ID BETWEEN1AND27ORDERBY COURSE,NAME,CLASS,GRADE
UPDATE #T2 SET GRADE=NULLWHERE GRADE="ZZ"
UPDATE #T2 SET CLASS=NULLWHERE CLASS="ZZ"
UPDATE #T2 SET NAME=NULLWHERE NAME="ZZ"
UPDATE #T2 SET COURSE=NULLWHERE COURSE="ZZ"

    WITH CUBE的结果：

SELECT GRADE,CLASS,NAME,COURSE,SUM(RESULT) RESULT
FROM #T0
GROUPBY GRADE,CLASS,NAME,COURSE
WITH CUBE

    自已测试的结果：

SELECT*FROM #T2

    结果与上面一致。

    其它字段优先跟哪个字段组合、最终怎样排序？呃，测过，没搞清楚……

    5、WITH ROLLUP

    原理1：除原始数据外，以GROUP BY最后1个字段(即COURSE字段)固定赋以NULL值，然后其它字段依次赋以NULL值进行分组聚合，结果由左往右进行排序。

    这个跟WITH CUBE的第1个字段非常相象：一个是第1个字段，一个是最后1个字段；一个结果是由右往左排序，一个结果是由左往右排序。

    下面开始测结果是怎么来的：

TRUNCATETABLE #T1
TRUNCATETABLE #T2
INSERTINTO #T1 (GRADE,CLASS,NAME,COURSE,RESULT)
SELECT GRADE,CLASS,NAME,COURSE,SUM(RESULT) RESULT 
FROM #T0 
GROUPBY GRADE,CLASS,NAME,COURSE
INSERTINTO #T1 (GRADE,CLASS,NAME,COURSE,RESULT)
SELECT GRADE,CLASS,NAME,"ZZ" COURSE,SUM(RESULT) RESULT 
FROM #T0 
WHERENOTEXISTS (SELECT1FROM #T1 WHERE GRADE=#T0.GRADE AND CLASS=#T0.GRADE AND NAME=#T0.NAME AND COURSE="ZZ")
GROUPBY GRADE,CLASS,NAME
INSERTINTO #T1 (GRADE,CLASS,NAME,COURSE,RESULT)
SELECT GRADE,CLASS,"ZZ" NAME,"ZZ" COURSE,SUM(RESULT) RESULT 
FROM #T0 
WHERENOTEXISTS (SELECT1FROM #T1 WHERE GRADE=#T0.GRADE AND CLASS=#T0.CLASS AND NAME="ZZ"AND COURSE="ZZ")
GROUPBY GRADE,CLASS
INSERTINTO #T1 (GRADE,CLASS,NAME,COURSE,RESULT)
SELECT GRADE,"ZZ" CLASS,"ZZ" NAME,"ZZ" COURSE,SUM(RESULT) RESULT 
FROM #T0 
WHERENOTEXISTS (SELECT1FROM #T1 WHERE GRADE=#T0.GRADE AND CLASS="ZZ"AND NAME="ZZ"AND COURSE="ZZ")
GROUPBY GRADE
INSERTINTO #T1 (GRADE,CLASS,NAME,COURSE,RESULT)
SELECT"ZZ" GRADE,"ZZ" CLASS,"ZZ" NAME,"ZZ" COURSE,SUM(RESULT) RESULT 
FROM #T0 
--结果排序由左往右
INSERTINTO #T2 (GRADE,CLASS,NAME,COURSE,RESULT)
SELECT GRADE,CLASS,NAME,COURSE,RESULT FROM #T1 ORDERBY GRADE,CLASS,NAME,COURSE
UPDATE #T2 SET GRADE=NULLWHERE GRADE="ZZ"
UPDATE #T2 SET CLASS=NULLWHERE CLASS="ZZ"
UPDATE #T2 SET NAME=NULLWHERE NAME="ZZ"
UPDATE #T2 SET COURSE=NULLWHERE COURSE="ZZ"

    WITH ROLLUP的结果：

SELECT GRADE,CLASS,NAME,COURSE,SUM(RESULT) RESULT
FROM #T0
GROUPBY GRADE,CLASS,NAME,COURSE
WITH ROLLUP

    自己测试的结果：

SELECT*FROM #T2

    结果与上面一致。

    6、GROUPING

    这个就比较容易理解了，WITH CUBE与WITH ROLLUP用法一样，先看结果：

SELECT GRADE,CLASS,NAME,COURSE,SUM(RESULT) RESULT,GROUPING(COURSE) [GROUPING]
FROM #T0
GROUPBY GRADE,CLASS,NAME,COURSE
WITH ROLLUP

    上面GROUPING的是COURSE字段，有NULL值就是WITH ROLLUP额外添加的，GROUPING结果值为1。

    有了GROUPING，那做小计、总计就方便了。

SELECT
    GRADE,
CASEWHENGROUPING(GRADE)=1ANDGROUPING(CLASS)=1THEN"总计"WHENGROUPING(GRADE)=0ANDGROUPING(CLASS)=1THEN"小计"ELSE CLASS END CLASS,
    NAME,COURSE,SUM(RESULT) RESULT
FROM #T0
GROUPBY GRADE,CLASS,NAME,COURSE
WITH ROLLUP

     好了，原理测试及应用就到这里结束了。

以上是 GROUPBY中的WITHCUBE、WITHROLLUP原理测试及GROUPING应用 的全部内容， 来源链接： utcz.com/z/531231.html

回到顶部