最近这段时间一直在看SQLServer视频,虽然了解了SQLServer运行环境、语言基础和设计基础,但还没有真正的转化为自己的知识。一方面,是刚接触数据库,另一方面,就是自己只是看视频、记笔记,没有去思考和总结。下面是我对groupby字句中withcube和withrollup的总结:
当需要对数据库数据进行分类统计的时候,往往会用上groupby进行分组。而在groupby后面还可以加入withcube和withrollup等关键字对数据进行汇总。那什么时候用withcube?什么时候用withrollup呢?
一、两者的区别
CUBE生成的结果集显示了所选列中值的所有组合的聚合。
ROLLUP生成的结果集显示了所选列中值的某一层次结构的聚合。
二、两者的用法(实例说明)
CUBE:除了返回GROUPBY子句指定的列外,还返回按组统计的行。返回的结果先按分组的第一个条件排序显示,再按第二个条件列排序显示,以此类推。统计行包括了GROUPBY子句指定的列的各种组合的数据统计
Select所属部门,性别,AVG(年龄)FROM 导师表Groupby所属部门,性别Withcube
所属部门 | 性别 | 年龄 |
纪律部 | 男 | 24 |
纪律部 | 女 | 22 |
纪律部 | null | 23纪律部成员的平均年龄 |
学习部 | 男 | 25 |
学习部 | 女 | 22 |
学习部 | null | 24学习部成员的平均年龄 |
博客检查委员会 | 男 | 24 |
博客检查委员会 | 女 | 22 |
博客检查委员会 | null | 23博委所有成员的平均年龄 |
null | null | 23所有成员的平均年龄 |
null | 男 | 22所有女成员的平均年龄 |
null | 女 | 24所有男成员的平均年龄 |
ROLLUP:与CYBE不同的是,此选项对GROUPBY子句中的列顺序敏感,它只返回第一个分组条件指定的列的统计行,改变列的顺序会使返回的结果的行数发生改变。
Select所属部门,性别,AVG(年龄)FROM导师表Groupby所属部门,性别With rollup
所属部门 | 性别 | 年龄 |
纪律部 | 男 | 24 |
纪律部 | 女 | 22 |
纪律部 | null | 23纪律部成员的平均年龄 |
学习部 | 男 | 25 |
学习部 | 女 | 22 |
学习部 | null | 24学习部成员的平均年龄 |
博客检查委员会 | 男 | 24 |
博客检查委员会 | 女 | 22 |
博客检查委员会 | null | 23博委所有成员的平均年龄 |
null | null | 23所有成员的平均年龄 |
null | 男 | 22 |
null | 女 | 24 |
还有一个比较简单的实例,拿来和大家分享一下:
简单表 Inventory 中包含:
Item Color Quantity
-------------------- -------------------- --------------------------
Table Blue 124
Table Red 223
Chair Blue 101
Chair Red 210
下列查询将生成小计报表:
SELECT CASE WHEN (GROUPING(Item) = 1) THEN 'ALL '
ELSE ISNULL(Item, 'UNKNOWN ')
END AS Item,
CASE WHEN (GROUPING(Color) = 1) THEN 'ALL '
ELSE ISNULL(Color, 'UNKNOWN ')
END AS Color,
SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color WITH ROLLUP
Item Color QtySum
-------------------- -------------------- --------------------------
Chair Blue 101.00
Chair Red 210.00
Chair ALL 311.00
Table Blue 124.00
Table Red 223.00
Table ALL 347.00
ALL ALL 658.00
(7 row(s) affected)
如果查询中的 ROLLUP 关键字更改为 CUBE,那么 CUBE 结果集与上述结果相同,只是在结果集的末尾还会返回下列两行:
ALL Blue 225.00
ALL Red 433.00