Group By 多个分组集小结 --GROUPING SETS，GROUP BY CUBE，GROUP BY ROLLUP，GROUPING(),GROUPIN... - 推酷
Group By 多个分组集小结 --GROUPING SETS，GROUP BY CUBE，GROUP BY ROLLUP，GROUPING(),GROUPIN...
T-SQL 多个分组集共有三种 GROUPING SETS, CUBE,&以及ROLLUP， 其中 CUBE和ROLLUP可以当做是GROUPING SETS的简写版
示例数据库下载：
/files/haseo/TSQL2012.rar
GROUPING SETS
列出所有你设置的分组集
SELECT shipperid, YEAR(shippeddate) AS shipyear, COUNT(*) AS numorders
FROM Sales.Orders
GROUP BY GROUPING SETS
( shipperid, YEAR(shippeddate) ),
( shipperid ),
( YEAR(shippeddate) ),
列出所有可能的分组集
shipperid ,
YEAR(shippeddate) AS shipyear ,
COUNT(*) AS numorders
Sales.Orders
GROUP BY CUBE(shipperid, YEAR(shippeddate));
1.&(&shipperid,&YEAR(shippeddate)&)
2.&(&shipperid&)
3.&(&YEAR(shippeddate)&)
以层级的方式列出分组集
shipcountry ,
shipregion ,
shipcity ,
COUNT(*) AS numorders
Sales.Orders
GROUP BY ROLLUP(shipcountry, shipregion, shipcity);
1.&(&shipcountry,&shipregion,&shipcity&)
2.&(&shipcountry,&shipregion&)
3.&(&shipcountry&)
GROUPING（）
该函数用来区分带入的元素是否属于分组的一部分， 返回0表示属于，1表示不属于
shipcountry ,
GROUPING(shipcountry) AS grpcountry ,
shipregion ,
GROUPING(shipregion) AS grpcountry ,
shipcity ,
GROUPING(shipcity) AS grpcountry ,
COUNT(*) AS numorders
Sales.Orders
GROUP BY ROLLUP(shipcountry, shipregion, shipcity);
GROUPING_ID（）
该函数返回分组列的位图（学过二进制的小伙伴都懂的，8421...以此类推） ，如果是0则所有分组字段都是分组的一部分，如果某个分组字段不是分组集的一部分则返回对应数字（既相关二进制位置1），最后进行汇总。
如下代码，如果shipcountry, shipregion, shipcity均是分组的一部分则返回0.& 如果shipregion, shipcity 不在分组内，则是3 （0+2+1 ）
GROUPING_ID(shipcountry, shipregion, shipcity) AS grp_id ,
shipcountry ,
shipregion ,
shipcity ,
COUNT(*) AS numorders
Sales.Orders
GROUP BY ROLLUP(shipcountry, shipregion, shipcity);
已发表评论数()
已收藏到推刊！
请填写推刊名
描述不能大于100个字符!
权限设置： 公开
仅自己可见
正文不准确
标题不准确
排版有问题
没有分页内容
图片无法显示
视频无法显示
与原文不一致jimshu 的BLOG
用户名：jimshu
文章数：339
评论数：169
访问量：853804
注册日期：
阅读量：3416
阅读量：283519
阅读量：1010269
阅读量：155742
51CTO推荐博文
　　GROUP BY 子句可以包括 GROUPING SETS、CUBE、ROLLUP、WITH CUBE 或 WITH ROLLUP等从属子句，从而为 SELECT 子句 &select& 列表中的聚合函数提供有关每个组（而不是各行）的信息。　　更多详细信息，请参考 　　WITH CUBE 或 WITH ROLLUP 等从属子句不符合 ISO 规范，因此后续版本 SQL Server 将删除该功能。请避免在新的开发工作中使用该功能。一、ROLLUP 从属子句1. 语法　　ROLLUP 从属子句生成简单的 GROUP BY 聚合行以及小计行或超聚合行，还生成一个总计行。　　返回的分组数等于&composite element list&中的表达式数加一。2. 示例　　下面的语句会为 (a, b, c)、(a, b) 和 (a) 值的每个唯一组合生成一个带有小计的行。 还将计算一个总计行。　　列是按照从右到左的顺序汇总的。 列的顺序会影响 ROLLUP 的输出分组，而且可能会影响结果集内的行数。SELECT a, b, c, SUM ( &expression& )FROM TGROUP BY ROLLUP (a,b,c);二、CUBE 从属子句1. 语法　　生成简单的 GROUP BY 聚合行、ROLLUP 超聚合行和交叉表格行。　　CUBE 针对 &composite element list& 中表达式的所有排列输出一个分组。　　生成的分组数等于 (2n)，其中 n = &composite element list& 中的表达式数。 2. 示例　　下面的语句会为 (a, b, c)、(a, b)、(a, c)、(b, c)、(a)、(b) 和 (c) 值的每个唯一组合生成一个带有小计的行，还会生成一个总计行。　　列的顺序不影响 CUBE 的输出。SELECT a, b, c, SUM (&expression&)FROM TGROUP BY CUBE (a,b,c);三、GROUPING SETS 从属子句1. 语法　　在一个查询中指定数据的多个分组。 仅聚合指定组，而不聚合由 CUBE 或 ROLLUP 生成的整组聚合。 其结果与针对指定的组执行 UNION ALL 运算等效。 　　GROUPING SETS 可以包含单个元素或元素列表。GROUPING SETS 可以指定与 ROLLUP 或 CUBE 返回的内容等效的分组。&grouping set item list& 可以包含 ROLLUP 或 CUBE。2. 示例　　下面的语句会为 (a,b) 和 (c) 值的每个分组集生成一个带有小计的行。还将计算一个总计行。SELECT a, b, SUM (&expression&)FROM TGROUP BY GROUPING SETS ( & (a,b） & (c) & () );　　上例相当于：SELECT a, b, SUM (&expression&)FROM TGROUP BY a,bUNION ALLSELECT NULL, NULL, SUM (&expression&)FROM TGROUP BY cUNION ALLSELECT NULL, NULL, SUM (&expression&)FROM T本文出自 “” 博客，谢绝转载！
了这篇文章
类别：┆阅读(0)┆评论(0)7318人阅读
SQL SERVER（22）
使用 GROUPING SETS 的 GROUP BY 子句可以生成一个等效于由多个简单 GROUP BY 子句的 UNION ALL 生成的结果集
示例:(sql server 2008 R2)
创建测试数据库及表并插入测试数据
use master
CREATE DATABASE db_sales
use db_sales
CREATE TABLE [dbo].[tb_sale](
[id] [int] IDENTITY(1,1) NOT NULL,
[server] [nvarchar](50) NULL,
[pname] [nvarchar](50) NULL,
[pinpai] [nvarchar](50) NULL,
[dates] [smalldatetime] NULL,
[cnt] [int] NULL
) ON [PRIMARY]
INSERT INTO [db_ControlManager_ft].[dbo].[test]([server],[pname],[pinpai],[dates],[cnt])
VALUES('A','computer','hp','',1),
('A','computer','hp','',3),
('A','computer','hp','',5),
('A','computer','hp','',1),
('A','computer','hp','',3),
('A','computer','hp','',5),
('A','computer','dell','',2),
('A','computer','dell','',4),
('A','computer','dell','',6),
('A','computer','dell','',7),
('A','computer','dell','',2),
('A','computer','dell','',4),
('B','computer','hp','',3),
('B','computer','hp','',3),
('B','computer','hp','',3),
('B','computer','hp','',3),
('B','computer','hp','',3),
('B','computer','hp','',2),
('B','computer','dell','',2),
('B','computer','dell','',2),
('B','computer','dell','',2),
('B','computer','dell','',2),
('B','computer','dell','',1),
('B','computer','dell','',1),
('A','TV','hp','',1),
('A','TV','hp','',3),
('A','TV','hp','',5),
('A','TV','hp','',1),
('A','TV','hp','',3),
('A','TV','hp','',5),
('A','TV','dell','',2),
('A','TV','dell','',4),
('A','TV','dell','',6),
('A','TV','dell','',7),
('A','TV','dell','',2),
('A','TV','dell','',4),
('B','TV','hp','',3),
('B','TV','hp','',3),
('B','TV','hp','',3),
('B','TV','hp','',3),
('B','TV','hp','',3),
('B','TV','hp','',2),
('B','TV','dell','',2),
('B','TV','dell','',2),
('B','TV','dell','',2),
('B','TV','dell','',2),
('B','TV','dell','',1),
('B','TV','dell','',1)
现在要求分别计算出每天的销量,总销量,每个销售员的总销量,每个产品的总销量,每个品牌的总销量,及每个销售员按品牌的产品销量
使用普通的UNION ALL语句
select N'总销量',null,null,null,null,SUM(cnt) from tb_sale
select N'每日销量',null,null,null,dates,SUM(cnt) from tb_sale group by dates
select N'按人员总销量',[server],null,null,null,SUM(cnt) from tb_sale group by [server]
select N'按品牌总销量',null,null,pinpai,null,SUM(cnt) from tb_sale group by pinpai
select N'按产品总销量',null,pname,null,null,SUM(cnt) from tb_sale group by pname
select N'按产品及品牌总销量',null,pname,pinpai,null,SUM(cnt) from tb_sale group by pname,pinpai
select N'按人员产品及品牌总销量',[server],pname,pinpai,null,SUM(cnt) from tb_sale group by [server],pname,pinpai
使用grouping sets 和grouping_id实现
[server] as 销售人员 ,
pname as 产品名称,
dates as 销售时间,
SUM(cnt) as 销售数量,
when GROUPING_ID([server],pname,pinpai,dates)=15 then N'总销量'
when GROUPING_ID([server],pname,pinpai,dates)=14 then N'每日销量'
when GROUPING_ID([server],pname,pinpai,dates)=13 then N'按品牌总销量'
when GROUPING_ID([server],pname,pinpai,dates)=11 then N'按产品总销量'
when GROUPING_ID([server],pname,pinpai,dates)=9 then N'按产品及品牌总销量'
when GROUPING_ID([server],pname,pinpai,dates)=7 then N'按人员总销量'
when GROUPING_ID([server],pname,pinpai,dates)=1 then N'按人员产品及品牌总销量'
as 项目说明
from tb_sale
group by GROUPING sets(([server],pname,pinpai),(pname,pinpai),[server],pname,pinpai,dates,())
order by [server],pname,pinpai,dates
使用union操作会增加IO开销,会减少cpu和内存的开销,使用grouping sets会减少IO开销,会增加cpu和内存的消耗.
GROUPING SETS在遇到多个条件时，聚合是一次性从数据库中取出所有需要操作的数据,在内存中对数据库进行聚合操作并生成结果。而UNION ALL是多次扫描表，将返回的结果进行UNION操作.这也就是为什么GROUPING SETS和UNION操作所返回的数据顺序是不同的.
grouping sets的执行方式在group by后面有多列的时候,grouping sets带来的性能提升非常明显
参考知识库
* 以上用户言论只代表其个人观点，不代表CSDN网站的观点或立场
访问：1362403次
积分：16038
积分：16038
排名：第383名
原创：362篇
转载：65篇
评论：283条
(1)(1)(2)(1)(1)(1)(1)(1)(3)(2)(15)(6)(2)(5)(4)(10)(12)(13)(2)(2)(1)(1)(4)(1)(1)(6)(2)(2)(2)(1)(1)(3)(5)(3)(1)(3)(6)(2)(5)(3)(3)(1)(7)(12)(9)(2)(7)(11)(6)(4)(4)(1)(9)(16)(11)(4)(1)(3)(2)(5)(2)(1)(2)(13)(10)(5)(24)(24)(35)(10)(1)(10)(4)(1)(7)(17)(5)oracle中grouping()的使用方法_百度知道
oracle中grouping()的使用方法
我有更好的答案
是SQL SERVER中的一个函数。 　　指示是否聚合 GROUP BY 列表中的指定列表达式。在结果集中，如果 GROUPING 返回 1 则指示聚合；返回 0 则指示不聚合。如果指定了 GROUP BY，则 GROUPING 只能用在 SELECT &select& 列表、HAVING 和 ORDER BY 子句中。 　　GROUPING 用于区分标准空值和由 ROLLUP、CUBE 或 GROUPING SETS 返回的空值。作为 ROLLUP、CUBE 或 GROUPING SETS 操作结果返回的 NULL 是 NULL 的特殊应用。它在结果集内作为列的占位符，表示全体。在用SQL时，我们经常会碰到这样的问题，要求分级求合计数，你是不是经常为怎么在一张表里分级统计而烦恼？这里我们可以用GROUPING()函数来解决该问题。 　　下面用实际的例子来说明，例子是一个行政区、单位、销售额的数据表。 　　注意：在运行这个示例时，...
其他类似问题
166人觉得有用
为您推荐：
grouping的相关知识
等待您来回答
下载知道APP
随时随地咨询
出门在外也不愁}