• 售前

  • 售后

热门帖子
入门百科

SQLServer中汇总功能的使用GROUPING,ROLLUP和CUBE

[复制链接]
晴空万里659 显示全部楼层 发表于 2021-10-26 12:17:42 |阅读模式 打印 上一主题 下一主题
第一次看到如许的SQL语句,看不懂,此中用到了下面的不常用的
聚集函数:GROUPING
用于汇总数据用的运算符: ROLLUP
SELECT

CASE GROUPING(o.customerid) WHEN 0 THEN o.customerid ELSE  '(Total)' END

AS AllCustomersSummary,

CASE GROUPING(od.orderid) WHEN 0 THEN od.orderid ELSE -1 END

AS IndividualCustomerSummary,

SUM(od.quantity*od.unitprice) AS price

FROM Orders o, [Order Details] od

WHERE Year(o.orderdate) = 1998 AND od.orderid=o.orderid

GROUP BY o.customerid, od.orderid WITH ROLLUP

ORDER BY AllCustomersSummary 检察SQL Server的帮助才发现,厉害啊,原来还有这么厉害的东西,不由的想起以前做水晶报表的时间,原来在SQL Server中就可以实现如许的功能.


1.用 CUBE 汇总数据

CUBE 运算符天生的结果集是多维数据集。多维数据集是事实数据的扩展,事实数据即记录个别事件的数据。扩展建立在用户打算分析的列上。这些列被称为维。多维数据集是一个结果集,此中包罗了各维度的全部可能组合的交叉表格。
CUBE 运算符在 SELECT 语句的 GROUP BY 子句中指定。该语句的选择列表应包罗维度列和聚合函数表达式。GROUP BY 应指定维度列和关键字 WITH CUBE。结果集将包罗维度列中各值的全部可能组合,以及与这些维度值组合相匹配的根本行中的聚合值。
比方,一个简朴的表 Inventory 中包罗:

[code]Item         Color        Quantity          -------------------- -------------------- -------------------------- Table        Blue         124            Table        Red         223            Chair        Blue         101            Chair        Red         210            下列查询返回的结果会合,将包罗 ItemColor 的全部可能组合的 Quantity 小计:

[code]SELECT Item, Color, SUM(Quantity) AS QtySumFROM InventoryGROUP BY Item, Color WITH CUBE下面是结果集:

[code]Item         Color        QtySum           -------------------- -------------------- -------------------------- Chair        Blue         101.00           Chair        Red         210.00           Chair        (null)        311.00           Table        Blue         124.00           Table        Red         223.00           Table        (null)        347.00           (null)        (null)        658.00           (null)        Blue         225.00           (null)        Red         433.00           我们偏重考察下列各行:

[code]Chair        (null)        311.00           这一行报告了 Item 维度中值为 Chair 的全部行的小计。对 Color 维度返回了 NULL 值,表现该行所报告的聚合包罗 Color 维度为恣意值的行。

[code]Table        (null)        347.00           这一行类似,但报告的是 Item 维度中值为 Table 的全部行的小计。

[code](null)        (null)        658.00           这一行报告了多维数据集的总计。ItemColor 维度的值都是 NULL,表现两个维度中的全部值都汇总在该行中。

[code](null)        Blue         225.00           (null)        Red         433.00           这两行报告了 Color 维度的小计。两行中的 Item 维度值都是 NULL,表现聚合数据来自 Item 维度为恣意值的行。
利用 GROUPING 区分空值

CUBE 操纵所天生的空值带来一个标题:怎样区分 CUBE 操纵所天生的 NULL 值和从实际数据中返回的 NULL 值?这个标题可用 GROUPING 函数办理。假如列中的值来自事实数据,则 GROUPING 函数返回 0;假如列中的值是 CUBE 操纵所天生的 NULL,则返回 1。在 CUBE 操纵中,所天生的 NULL 代表全体值。可将 SELECT 语句写成利用 GROUPING 函数将所天生的 NULL 更换为字符串 ALL。由于事实数据中的 NULL 表明数据值未知,以是 SELECT 语句还可译码为返回字符串 UNKNOWN 更换来自事实数据的 NULL。比方:

[code]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 QtySumFROM InventoryGROUP BY Item, Color WITH CUBE多维数据集

CUBE 运算符可用于天生 n 维的多维数据集,即具有恣意数目维度的多维数据集。只有一个维度的多维数据集可用于天生合计,比方:

[code]SELECT CASE WHEN (GROUPING(Item) = 1) THEN 'ALL'      ELSE ISNULL(Item, 'UNKNOWN')    END AS Item,    SUM(Quantity) AS QtySumFROM InventoryGROUP BY Item WITH CUBEGO此 SELECT 语句返回的结果集既表现了 Item 中每个值的小计,也表现了 Item 中全部值的总计:

[code]Item         QtySum           -------------------- -------------------------- Chair        311.00           Table        347.00           ALL         658.00           包罗带有很多维度的 CUBE 的 SELECT 语句可能天生很大的结果集,由于这些语句会为全部维度中值的全部组合天生行。这些大结果集包罗的数据可能过多而不易于阅读和明白。这个标题有一种办理办法是将 SELECT 语句放在视图中:

[code]CREATE VIEW InvCube ASSELECT 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 QtySumFROM InventoryGROUP BY Item, Color WITH CUBE然后即可用该视图来只查询您感兴趣的维度值:

[code]SELECT *FROM InvCubeWHERE Item = 'Chair' AND Color = 'ALL'Item         Color        QtySum           -------------------- -------------------- -------------------------- Chair        ALL         311.00           (1 row(s) affected)


2.用 ROLLUP 汇总数据

在天生包罗小计和合计的报表时,ROLLUP 运算符很有用。ROLLUP 运算符天生的结果集类似于 CUBE 运算符所天生的结果集。有关更多信息.
CUBE 和 ROLLUP 之间的区别在于:

  • CUBE 天生的结果集表现了所选列中值的全部组合的聚合。
  • ROLLUP 天生的结果集表现了所选列中值的某一条理结构的聚合。
比方,简朴表 Inventory 中包罗:

[code]Item         Color        Quantity          -------------------- -------------------- -------------------------- Table        Blue         124            Table        Red         223            Chair        Blue         101            Chair        Red         210            下列查询将天生小计报表:

[code]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 QtySumFROM InventoryGROUP BY Item, Color WITH ROLLUPItem         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 结果集与上述结果相同,只是在结果集的末尾还会返回下列两行:

[code]ALL         Blue         225.00           ALL         Red         433.00           CUBE 操纵为 ItemColor 中值的可能组合天生行。比方,CUBE 不仅报告与 Item 值 Chair 相组合的 Color 值的全部可能组合(Red、Blue 和 Red + Blue),而且报告与 Color 值 Red 相组合的 Item 值的全部可能组合(Chair、Table 和 Chair + Table)。
对于 GROUP BY 子句中右边的列中的每个值,ROLLUP 操纵并不报告左边一列(或左边各列)中值的全部可能组合。比方,ROLLUP 并不对每个 Color 值报告 Item 值的全部可能组合。
ROLLUP 操纵的结果集具有类似于 COMPUTE BY 所返回结果集的功能;然而,ROLLUP 具有下列长处:

  • ROLLUP 返回单个结果集;COMPUTE BY 返回多个结果集,而多个结果集会增长应用程序代码的复杂性。
  • ROLLUP 可以在服务器游标中利用;COMPUTE BY 不可以。
  • 有时,查询优化器为 ROLLUP 天生的实行计划比为 COMPUTE BY 天生的更为高效。


3.GROUPING

是一个聚合函数,它产生一个附加的列,当用 CUBE 或 ROLLUP 运算符添加行时,附加的列输出值为1,当所添加的行不是由 CUBE 或 ROLLUP 产生时,附加列值为0。
仅在与包罗 CUBE 或 ROLLUP 运算符的 GROUP BY 子句相接洽的选择列表中才答应分组。
语法

GROUPING ( column_name )
参数

column_name
是 GROUP BY 子句中用于检查 CUBE 或 ROLLUP 空值的列。
返回类型

int
解释

分组用于区分由 CUBE 和 ROLLUP 返回的空值和尺度的空值。作为CUBE 或 ROLLUP 操纵结果返回的 NULL 是 NULL 的特别应用。它在结果集内作为列的占位符,意思是"全体"。
示例

下面的示例将 royalty 的数值分组,并聚合 advance 的数值。GROUPING 函数应用于 royalty 列。

[code]USE pubsSELECT royalty, SUM(advance) 'total advance',  GROUPING(royalty) 'grp'  FROM titles  GROUP BY royalty WITH ROLLUP结果集在 royalty 下表现两个空值。第一个 NULL 代表从表中这一列得到的空值组。第二个 NULL 在 ROLLUP 操纵所添加的汇总行中。汇总行表现的是全部 royalty 组的 advance 合计数值,而且在 grp 列中用 1 标识。
下面是结果集:

[code]royalty    total advance       grp ---------   ---------------------  ---NULL      NULL           0 10       57000.0000        0 12       2275.0000        0 14       4000.0000        0 16       7000.0000        0 24       25125.0000        0 NULL      95400.0000        1
  

3.GROUPING

是一个聚合函数,它产生一个附加的列,当用 CUBE 或 ROLLUP 运算符添加行时,附加的列输出值为1,当所添加的行不是由 CUBE 或 ROLLUP 产生时,附加列值为0。
仅在与包罗 CUBE 或 ROLLUP 运算符的 GROUP BY 子句相接洽的选择列表中才答应分组。
语法

GROUPING ( column_name )
参数

column_name
是 GROUP BY 子句中用于检查 CUBE 或 ROLLUP 空值的列。
返回类型

int
解释

分组用于区分由 CUBE 和 ROLLUP 返回的空值和尺度的空值。作为CUBE 或 ROLLUP 操纵结果返回的 NULL 是 NULL 的特别应用。它在结果集内作为列的占位符,意思是"全体"。
示例

下面的示例将 royalty 的数值分组,并聚合 advance 的数值。GROUPING 函数应用于 royalty 列。

[code]USE pubsSELECT royalty, SUM(advance) 'total advance',  GROUPING(royalty) 'grp'  FROM titles  GROUP BY royalty WITH ROLLUP结果集在 royalty 下表现两个空值。第一个 NULL 代表从表中这一列得到的空值组。第二个 NULL 在 ROLLUP 操纵所添加的汇总行中。汇总行表现的是全部 royalty 组的 advance 合计数值,而且在 grp 列中用 1 标识。
下面是结果集:

[code]royalty    total advance       grp ---------   ---------------------  ---NULL      NULL           0 10       57000.0000        0 12       2275.0000        0 14       4000.0000        0 16       7000.0000        0 24       25125.0000        0 NULL      95400.0000        1
  

3.GROUPING

是一个聚合函数,它产生一个附加的列,当用 CUBE 或 ROLLUP 运算符添加行时,附加的列输出值为1,当所添加的行不是由 CUBE 或 ROLLUP 产生时,附加列值为0。
仅在与包罗 CUBE 或 ROLLUP 运算符的 GROUP BY 子句相接洽的选择列表中才答应分组。
语法

GROUPING ( column_name )
参数

column_name
是 GROUP BY 子句中用于检查 CUBE 或 ROLLUP 空值的列。
返回类型

int
解释

分组用于区分由 CUBE 和 ROLLUP 返回的空值和尺度的空值。作为CUBE 或 ROLLUP 操纵结果返回的 NULL 是 NULL 的特别应用。它在结果集内作为列的占位符,意思是"全体"。
示例

下面的示例将 royalty 的数值分组,并聚合 advance 的数值。GROUPING 函数应用于 royalty 列。

[code]USE pubsSELECT royalty, SUM(advance) 'total advance',  GROUPING(royalty) 'grp'  FROM titles  GROUP BY royalty WITH ROLLUP结果集在 royalty 下表现两个空值。第一个 NULL 代表从表中这一列得到的空值组。第二个 NULL 在 ROLLUP 操纵所添加的汇总行中。汇总行表现的是全部 royalty 组的 advance 合计数值,而且在 grp 列中用 1 标识。
下面是结果集:

[code]royalty    total advance       grp ---------   ---------------------  ---NULL      NULL           0 10       57000.0000        0 12       2275.0000        0 14       4000.0000        0 16       7000.0000        0 24       25125.0000        0 NULL      95400.0000        1 对GROUPING,ROLLUP,CUBE的先容来自SQL Server2000中文版的帮助.

帖子地址: 

回复

使用道具 举报

分享
推广
火星云矿 | 预约S19Pro,享500抵1000!
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

草根技术分享(草根吧)是全球知名中文IT技术交流平台,创建于2021年,包含原创博客、精品问答、职业培训、技术社区、资源下载等产品服务,提供原创、优质、完整内容的专业IT技术开发社区。
  • 官方手机版

  • 微信公众号

  • 商务合作