现在的位置: 首页 > 综合 > 正文

汇总聚合函数 CUBE和ROLLUP

2013年01月27日 ⁄ 综合 ⁄ 共 2798字 ⁄ 字号小中大 ⁄ 评论关闭

先看看2005帮助的一些解释:

聚合函数；当行由 CUBE 或 ROLLUP 运算符添加时，该函数将导致附加列的输出值为 1；当行不由 CUBE 或 ROLLUP 运算符添加时，该函数将导致附加列的输出值为 0。

仅在与包含 CUBE 或 ROLLUP 运算符的 GROUP BY 子句相关联的选择列表中才允许分组。

语法


GROUPING ( column_name ) <OVER Clause>

备注

分组用于区分由标准空值产生的 CUBE 和 ROLLUP 所返回的空值。作为 CUBE 或 ROLLUP 操作结果返回的 NULL 是 NULL 的特殊应用。它在结果集内作为列的占位符，表示全体。

参数

column_name: GROUP BY 子句中的列，用于测试 CUBE 或 ROLLUP 空值。

<Over_Clause>: 将 FROM 子句产生的结果集划分成应用了 Ranking Window 或 Aggregate Window 函数的分区或窗口。

返回类型

int

示例

xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5" 2003 authoring ddue.schemas.microsoft.com 5:content>

以下示例将分组 SalesQuota 并聚合 SaleYTD 数量。GROUPING 函数应用于 SalesQuota 列。

	复制代码
USE AdventureWorks; GO SELECT SalesQuota, SUM(SalesYTD) 'TotalSalesYTD', GROUPING(SalesQuota) AS 'Grouping' FROM Sales.SalesPerson GROUP BY SalesQuota WITH ROLLUP; GO

结果集在 SalesQuota 下面显示两个空值。第一个 NULL 代表从表中的这一列得到的空值组。第二个 NULL 位于 ROLLUP 操作所添加的汇总行之中。汇总行显示所有 SalesQuota 组的 TotalSalesYTD 数量，并以 Grouping 列中的 1 进行指示。

下面是结果集：

	复制代码
SalesQuota TotalSalesYTD Grouping --------- ------------- -------- NULL 1533087.5999 0 250000.00 33461260.59 0 300000.00 9299677.9445 0 NULL 44294026.1344 1 (4 row(s) affected)

2003 authoring ddue.schemas.microsoft.com 5:content>

下面在看我写的一个小示例:

Code

/*
* Author:   董广祥
* 脚本日期: 2009/6/28
*/
WITH   cte_temp ( Name, Sex, Ages )
          AS (
      SELECT   '赵','男',25
               UNION ALL
               SELECT   '钱', '男',30
               UNION ALL
               SELECT   '孙','女',26
               UNION ALL
               SELECT   '李','女',32
             )
    -- SELECT * FROM cte_temp

SELECT NAME = CASE WHEN GROUPING([Name]) = 1
                             AND GROUPING(Sex) = 0 THEN '合计'
                        WHEN GROUPING([name]) = 1
                             AND GROUPING(Sex) = 1 THEN '总计'
                        ELSE [Name]
                   END,
            Sex = ISNULL(sex, ''),
            Ages = SUM(Ages) --总和
     FROM   cte_temp
     GROUP BY Sex,Name WITH ROLLUP

    /*SELECT NAME = CASE WHEN GROUPING([Name]) = 1
                             AND GROUPING(Sex) = 0 THEN '合计'
                        WHEN GROUPING([name]) = 1
                             AND GROUPING(Sex) = 1 THEN '总计'
                        WHEN GROUPING([name]) = 0
                             AND GROUPING(Sex) = 1 THEN [Name] + '总计'
                        ELSE [Name]
                   END,
            Sex = ISNULL(sex, ''),
            Ages = SUM(Ages) --总和
    FROM    cte_temp
    GROUP BY Sex,Name WITH CUBE*/v

总和查询结果(ROLLUP):

      Name Sex Ages
      ---- ---- -----------
      钱    男    30
      赵    男    25
      合计   男    55
      李    女    32
      孙    女    26
      合计   女    58
      总计        113

总和查询结果(CUBE):

      NAME   Sex Ages
      ------ ---- -----------
      钱      男    30
      赵      男    25
      合计   男    55
      李      女    32
      孙      女    26
      合计   女    58
      总计          113
      李总计       32
      钱总计       30
      孙总计       26
      赵总计       25

总结:通过以上的例子可以看出,使用ROLLUP是针对某一列进行分组汇总,而CUBE是对所有包含在Group By 里面的列进行分组汇总,而且对使用CUBE的列(Name WITH CUBE)还会进行一个组的汇总,这点和ROLLUP汇总是一样的......

本文出自:http://www.cnblogs.com/myssh/archive/2009/06/28/1512691.html