/*
--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 生成的更为高效。
*/
SQLSERVER中union,cube,rollup,cumpute运算符使用说明
时间: 2024-08-31 18:21:28
SQLSERVER中union,cube,rollup,cumpute运算符使用说明的相关文章
SQLSERVER中的ALL、PERCENT、CUBE关键字、ROLLUP关键字和GROUPING函数
原文:SQLSERVER中的ALL.PERCENT.CUBE关键字.ROLLUP关键字和GROUPING函数 SQLSERVER中的ALL.PERCENT.CUBE关键字.ROLLUP关键字和GROUPING函数 先来创建一个测试表 1 USE [tempdb] 2 GO 3 4 CREATE TABLE #temptb(id INT ,NAME VARCHAR(200)) 5 GO 6 7 INSERT INTO [#temptb] ( [id], [NAME] ) 8 SELECT 1,'
SQLserver中cube:多维数据集实例详解
1.cube:生成多维数据集,包含各维度可能组合的交叉表格,使用with 关键字连接 with cube 根据需要使用union all 拼接 判断 某一列的null值来自源数据还是 cube 使用GROUPING关键字 GROUPING([档案号]) = 1 : null值来自cube(代表所有的档案号) GROUPING([档案号]) = 0 : null值来自源数据 举例: SELECT * INTO ##GET FROM (SELECT * FROM ( SELECT CASE WHEN
SQLSERVER中如何快速比较两张表的不一样
原文:SQLSERVER中如何快速比较两张表的不一样 SQLSERVER中如何快速比较两张表的不一样 不知不觉要写2014年的最后一篇博文了~ 一般来说,如何检测两张表的内容是否一致,体现在复制的时候发布端和订阅端的两端的数据上面 我这里罗列了一些如何从数据库层面来解决此类问题的方法 第一步当然就是检查记录数是否一致,否则不用想其他方法了~这里我们用两张表t1_old,t1_new来演示 方法介绍 方法一:老老实实看表结构和表记录数,弊端是根本看不到两张表的数据是否一致,只是看到表结构和
SQLServer中SELECT语句的执行顺序
今天在写一条语句的时候,在查询分析器里边执行 要用10s,换用另外一种写法只用少于1s的时间,同事说是因为Sql句语执行顺序的原因.之前看过一点相 关的书,有一点印象,到网上找了资料,学习下. 逻辑查询处理步骤 复制代码 代码如下: (8)SELECT (9)DISTINCT (11)<TOP_specification> <select_list> (1)FROM <left_table> (3) <join_type> JOIN <right_ta
SQL中UNION 与 UNION ALL 的区别
区别 SQLServer2000中UNION与UNION ALL的区别 UNION 运算符: 将两个或更多查询的结果组合为单个结果集,该结果集包含联合查询中的所有查询的全部行.这与使用联接组合两个表中的列不同. 使用 UNION 组合两个查询的结果集的两个基本规则是: 所有查询中的列数和列的顺序必须相同. 数据类型必须兼容. 这种多结果的查询组合为单一结果集在实际中应用的非常方便.但在应用中也有有着问题,如下实例可以进一步说明问题. 问题描述: 为了进一步分析与统计企
SQLSERVER中XML查询:FORXML指定RAW
SQL SERVER中XML查询:FOR XML指定RAW 前言 在SQL SERVER中,XML查询可以指定RAW,AUTO,EXPLICIT,PATH.本文用一些实例介绍SQL SERVER中指定RAW的XML查询. 基础FOR XML查询 看实例: with TestXml as ( select 1 as id,'LeeWhoeeUniversity' as name union all select 2,'SQLSERVER中XML查询' union all select 3 ,'FO
SQLServer中SELECT语句的执行顺序_MsSql
今天在写一条语句的时候,在查询分析器里边执行 要用10s,换用另外一种写法只用少于1s的时间,同事说是因为Sql句语执行顺序的原因.之前看过一点相 关的书,有一点印象,到网上找了资料,学习下. 逻辑查询处理步骤 复制代码 代码如下: (8)SELECT (9)DISTINCT (11)<TOP_specification> <select_list> (1)FROM <left_table> (3) <join_type> JOIN <right_ta
详解C#批量插入数据到Sqlserver中的四种方式_C#教程
本篇,我将来讲解一下在Sqlserver中批量插入数据. 先创建一个用来测试的数据库和表,为了让插入数据更快,表中主键采用的是GUID,表中没有创建任何索引.GUID必然是比自增长要快的,因为你生成一个GUID算法所花的时间肯定比你从数据表中重新查询上一条记录的ID的值然后再进行加1运算要少.而如果存在索引的情况下,每次插入记录都会进行索引重建,这是非常耗性能的.如果表中无可避免的存在索引,我们可以通过先删除索引,然后批量插入,最后再重建索引的方式来提高效率. create database C
C#批量插入数据到Sqlserver中的三种方式_C#教程
本篇,我将来讲解一下在Sqlserver中批量插入数据. 先创建一个用来测试的数据库和表,为了让插入数据更快,表中主键采用的是GUID,表中没有创建任何索引.GUID必然是比自增长要快的,因为你生成一个GUID算法所花的时间肯定比你从数据表中重新查询上一条记录的ID的值然后再进行加1运算要少.而如果存在索引的情况下,每次插入记录都会进行索引重建,这是非常耗性能的.如果表中无可避免的存在索引,我们可以通过先删除索引,然后批量插入,最后再重建索引的方式来提高效率. create database C