GROUP BY

忘是亡心i 2022-10-01 00:49 178阅读 0赞

常规GROUP BY 后的子句包括 GROUPING SETS、CUBE、ROLLUP
    
       这三个运算符可以生成与使用 UNION ALL 来组合单个分组查询时相同的结果集；但是使用者其中一种更有效。
    
     
    
     
    
       GROUPING SETS: 在一个查询中指定数据的多个分组。仅聚合指定分组，而不聚合由CUBE或ROLLUP生成的整组聚合。其结果与针对指定的租执行UNION ALL运算等效。GROUPING SETS 可以包括单个元素或元素列表，也可以指定与ROLLUP 、CUBE 返回的内容等效的分组。  它可以生成与使用单个 GROUP BY 、ROLLUP、CUBE 运算符所生成的结果集相同的结果集。如果不需要获得由完备的ROLLUP或CUBE运算符生成的全部分组，则可以使用GROUPING SETS仅指定所需的分组即可。GROUPING SETS 列表可以包含重复的分组；它 与ROLLUP、CUBE一起使用时，就有可能生成重复的分组。使用UNION ALL 可以原样保留重复的分组。
    
     
    
        ROLLUP:生成简单的GROUP BY 聚合行以及小计行或超聚合行，同时还生成一个总计行。列是按照从右到左的顺序汇总的。列的顺序会影响ROLLUP的输出分组，而且可能影响结果集内的行数。
    
                     
    
        CUBE：生成简单的GROUP BY聚合行、ROLLUP超聚合行和交叉表格行。列的顺序不影响CUBE的输出结果。
    
      
    
    CUBE、 ROLLUP 不支持非重复的聚合。例如：COUNT(DISTINCT USERID)、AVG(DISTINCT column_name).
    
     不能在索引视图中指定 CUBE 、ROLLUP、 GROUPING SETS
    
    GROUP BY 子句中不允许使用 GROUPING SETS ,除非它们是GROUPING SETS列表的一部分 例如:
    
     GROUP BY column1,(column2,column3)是错误的。但是可以是  GROUP BY GROUPING SETS (column1,(column2,column3))
    
    GROUPING SETS 内部不能使用 GROUPING SETS
    
     
    
     
    
    GROUPING
    
        表示是否聚合GROUP BY 列表中的指定列表达式。在结果集中，如果GROUPING 返回1 则表示聚合，返回0 则表示不聚合。  如果指定了 GROUP BY ，则GROUPING 只能在 SELECT 列表、HAVING、ORDER BY子句中。
    
        GROUPING 还用于区分标准空值和由 CUBE、ROLLUP、GROUPING SETS 返回的值。作为CUBE、ROLLUP、GROUPING SETS 操作结果返回的NULL是 NULL 的特殊应用。它在结果集内作为列的占位符，表示全体。
    
    例如：
    
         SELECT USERID,SUM(Quantiy)as qty,GROUPING(USERID) AS USERID_GROUP
    
                      FROM TAB     GROUP BY USERID WITH ROLLUP[CUBE];
    
     
    
    整理自 联机丛书
    
     
    
     
    
    举例：
    
    if object_id('[test]') is not null drop table [test]
    create table [test]([品名] varchar(10),[数量] int)
    insert [test]
    select 'A',1 union all
    select 'A',2 union all
    select 'A',2 union all
    select 'B',2 union all
    select 'B',1 union all
    select 'B',3
    
    select [品名],[数量] from [test] 
    union all
    select isnull([品名]+'汇总','总合计'),sum(数量) 
    from test group by [品名] with rollup
    order by 1
    
    
    Declare @T Table (
        品名    Varchar(10),
        数量    int
    )
    
    InSert Into @T
    Select 'A', 1
    Union All Select 'A', 2
    Union All Select 'A', 2
    Union All Select 'B', 2
    Union All Select 'B', 1
    Union All Select 'B', 3
    
    
    Select * from @T
    Union All
    Select 品名+'汇总', SUM(数量) From @T
    Group By RollUp(品名)
    Having ISNULL(品名,'') <> ''
    Order By 品名 
    
    Select * from @T
    Union All
    Select 品名+'汇总', SUM(数量) From @T
    Group By CUBE(品名)
    Having ISNULL(品名,'') <> ''
    Order By 品名