MSSQL

  • MYSQL
  • MSSQL
  • Redis
  • MongoDB
  • oracle数据库
  • 数据管理

    SQLSERVER中union,cube,rollup,cumpute运算符使用说明

    2018-05-16 07:55:44 次阅读 稿源:互联网
    非官媒广告
    /*
    --1 UNION 运算符是将两个或更多查询的结果组合为单个结果集
    使用 UNION 组合查询的结果集有两个最基本的规则:
    1。所有查询中的列数和列的顺序必须相同。
    2。数据类型必须兼容
    a.UNION的结果集列名与第一个select语句中的结果集中的列名相同,其他select语句的结果集列名被忽略
    b.默认情况下,UNION 运算符是从结果集中删除重复行。如果使用all关键字,那么结果集将包含所有行并且不删除重复行
    c.sql是从左到右对包含UNION 运算符的语句进行取值,使用括号可以改变求值顺序
    --例如:
    */
    select * from tablea
    union all
    (
    select * from tableb
    union all
    select * from tablec
    )
    /*
    这样就可以先对tableb和tablec合并,再合并tablea
    d.如果要将合并后的结果集保存到一个新数据表中,那么into语句必须加入到第一条select中
    e.只可以在最后一条select语句中使用 order by 和 compute 子句,这样影响到最终合并结果的排序和计数汇总
    f.group by 和 having 子句可以在单独一个select查询中使用,它们不影响最终结果
    */
    --2 CUBE 汇总数据
    /*
    CUBE 运算符生成的结果集是多维数据集。多维数据集是事实数据的扩展,事实数据即记录个别事件的数据。
    扩展建立在用户打算分析的列上。这些列被称为维。多维数据集是一个结果集,其中包含了各维度的所有可能组合的交叉表格。
    CUBE 运算符在 SELECT 语句的 GROUP BY 子句中指定。该语句的选择列表应包含维度列和聚合函数表达式。
    GROUP BY 应指定维度列和关键字 WITH CUBE。结果集将包含维度列中各值的所有可能组合,以及与这些维度值组合相匹配的基础行中的聚合值。
    */
    --下列查询返回的结果集中,将包含 Item 和 Color 的所有可能组合的 Quantity 小计:
    -->Title:生成
    -->Author:wufeng4552
    -->Date :2009-09-10 14:36:20
    if not object_id('Tempdb..#t') is null
    drop table #t
    Go
    Create table #t([Item] nvarchar(5),[Color] nvarchar(4),[Quantity] int)
    Insert #t
    select N'Table',N'Blue',124 union all
    select N'Table',N'Red',223 union all
    select N'Chair',N'Blue',101 union all
    select N'Chair',N'Red',210
    Go
    select [Item],
    [Color],
    sum([Quantity])[Quantity]
    from #t group by [Item],[Color] with cube
    /*
    Item Color Quantity
    ----- ----- -----------
    Chair Blue 101
    Chair Red 210
    Chair NULL 311
    Table Blue 124
    Table Red 223
    Table NULL 347
    NULL NULL 658
    NULL Blue 225
    NULL Red 433
    */
    /*CUBE 操作所生成的空值带来一个问题:如何区分 CUBE 操作所生成的 NULL 值和从实际数据中返回的 NULL 值?
    这个问题可用 GROUPING 函数解决。
    如果列中的值来自事实数据,则 GROUPING 函数返回 0;如果列中的值是 CUBE 操作所生成的 NULL,则返回 1。
    在 CUBE 操作中,所生成的 NULL 代表全体值。可将 SELECT 语句写成使用 GROUPING 函数将所生成的 NULL 替换为字符串 ALL。
    因为事实数据中的 NULL 表明数据值未知,所以 SELECT 语句还可译码为返回字符串 UNKNOWN 替代来自事实数据的 NULL。
    例如:
    */
    -->Title:生成
    -->Author:wufeng4552
    -->Date :2009-09-10 14:36:20
    if not object_id('Tempdb..#t') is null
    drop table #t
    Go
    Create table #t([Item] nvarchar(5),[Color] nvarchar(4),[Quantity] int)
    Insert #t
    select N'Table',N'Blue',124 union all
    select N'Table',N'Red',223 union all
    select N'Chair',N'Blue',101 union all
    select N'Chair',N'Red',210
    Go
    select [Item]=case when grouping([Item])=1 then 'ALL' else isnull(Item, 'UNKNOWN')end,
    [Color]=case when grouping([Color])=1 then 'ALL' else isnull([Color],'UNKNOWN')end,
    sum([Quantity])[Quantity]
    from #t group by [Item],[Color] with cube
    /*
    Item Color Quantity
    ----- ----- -----------
    Chair Blue 101
    Chair Red 210
    Chair ALL 311
    Table Blue 124
    Table Red 223
    Table ALL 347
    ALL ALL 658
    ALL Blue 225
    ALL Red 433
    (9 料列受到影)
    */
    /*
    包含带有许多维度的 CUBE 的 SELECT 语句可能生成很大的结果集,因为这些语句会为所有维度中值的所有组合生成行。
    这些大结果集包含的数据可能过多而不易于阅读和理解。这个问题有一种解决办法是将 SELECT 语句放在视图中:
    */
    create view view_cube
    as
    select [Item]=case when grouping([Item])=1 then 'ALL' else isnull(Item, 'UNKNOWN')end,
    [Color]=case when grouping([Color])=1 then 'ALL' else isnull([Color],'UNKNOWN')end,
    sum([Quantity])[Quantity]
    from tb group by [Item],[Color] with cube --中不能用表,故改之
    --然后即可用该视图来只查询您感兴趣的维度值:
    SELECT *
    FROM InvCube
    WHERE Item = 'Chair' AND Color = 'ALL'
    /*
    Item Color QtySum
    -------------------- -------------------- ---------
    Chair ALL 311.00
    */
    --3 ROLLUP 汇总数据
    /*
    用 ROLLUP 汇总数据在生成包含小计和合计的报表时,ROLLUP 运算符很有用。
    ROLLUP 运算符生成的结果集类似于 CUBE 运算符所生成的结果集。
    CUBE 和 ROLLUP 之间的区别在于: CUBE 生成的结果集显示了所选列中值的所有组合的聚合。
    ROLLUP 生成的结果集显示了所选列中值的某一层次结构的聚合。 例如,简单表 #t
    中包含:Item Color Quantity
    */
    select [Item]=case when grouping([Item])=1 then 'ALL' else isnull(Item, 'UNKNOWN')end,
    [Color]=case when grouping([Color])=1 then 'ALL' else isnull([Color],'UNKNOWN')end,
    sum([Quantity])[Quantity]
    from #t group by [Item],[Color] with rollup
    /*
    Item Color Quantity
    ----- ----- -----------
    Chair Blue 101
    Chair Red 210
    Chair ALL 311
    Table Blue 124
    Table Red 223
    Table ALL 347
    ALL ALL 658
    (7 料列受到影)
    */
    /*
    如果查询中的 ROLLUP 关键字更改为 CUBE,那么 CUBE 结果集与上述结果相同,只是在结果集的末尾还会返回下列两行:ALL Blue 225.00
    ALL Red 433.00
    CUBE 操作为 Item 和 Color 中值的可能组合生成行。
    例如,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 生成的更为高效。
    */

    非官媒部分新闻及文章转载自互联网,供读者交流和学习,若有涉及作者版权等问题请及时与我们联系,以便更正、删除或按规定办理。感谢所有提供资讯的网站,欢迎各类媒体与非官媒进行文章共享合作。

    非官媒广告
    非官媒广告
    非官媒广告