查询难点.docx
《查询难点.docx》由会员分享，可在线阅读，更多相关《查询难点.docx（15页珍藏版）》请在冰豆网上搜索。
查询难点
SQLServer2005联机丛书（2008年11月）
使用CUBE汇总数据
CUBE运算符生成的结果集是多维数据集。
多维数据集是事实数据（即记录个别事件的数据）的扩展。
扩展是基于用户要分析的列建立的。
这些列称为维度。
多维数据集是结果集，其中包含各维度的所有可能组合的交叉表格。
CUBE运算符在SELECT语句的GROUPBY子句中指定。
该语句的选择列表包含维度列和聚合函数表达式。
GROUPBY指定了维度列和关键字WITHCUBE。
结果集包含维度列中各值的所有可能组合，以及与这些维度值组合相匹配的基础行中的聚合值。
例如，简单表Inventory包含下列数据：
复制代码
ItemColorQuantity
------------------------------------------------------------------
TableBlue124
TableRed223
ChairBlue101
ChairRed210
以下查询将返回一个结果集，其中包含Item和Color的所有可能组合的Quantity小计：
复制代码
SELECTItem,Color,SUM（Quantity）ASQtySum
FROMInventory
GROUPBYItem,ColorWITHCUBE
下面是结果集：
复制代码
ItemColorQtySum
------------------------------------------------------------------
ChairBlue101.00
ChairRed210.00
Chair（null）311.00
TableBlue124.00
TableRed223.00
Table（null）347.00
（null）（null）658.00
（null）Blue225.00
（null）Red433.00
我们着重考查结果集中的以下几行：
复制代码
Chair（null）311.00
此行报告了在Item维度中包含Chair值的所有行的小计。
对Color维度返回了null值，用以表示该行报告的聚合包括Color维度为任意值的行。
复制代码
Table（null）347.00
这一行类似，但报告的是Item维度中包含Table值的所有行的小计。
复制代码
（null）（null）658.00
这一行报告了多维数据集的总计。
Item和Color维度都包含null值。
这表示此行中汇总了这两个维度的所有值。
复制代码
（null）Blue225.00
（null）Red433.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。
例如：
复制代码
SELECTCASEWHEN（GROUPING（Item）=1）THEN'ALL'
ELSEISNULL（Item,'UNKNOWN'）
ENDASItem,
CASEWHEN（GROUPING（Color）=1）THEN'ALL'
ELSEISNULL（Color,'UNKNOWN'）
ENDASColor,
SUM（Quantity）ASQtySum
FROMInventory
GROUPBYItem,ColorWITHCUBE
多维数据集
CUBE运算符可用于生成n维的多维数据集，即具有任意维数的多维数据集。
只有一个维度的多维数据集可用于生成合计，例如：
复制代码
SELECTCASEWHEN（GROUPING（Item）=1）THEN'ALL'
ELSEISNULL（Item,'UNKNOWN'）
ENDASItem,
SUM（Quantity）ASQtySum
FROMInventory
GROUPBYItemWITHCUBE
GO
此SELECT语句返回的结果集既显示了Item中每个值的小计，也显示了Item中所有值的总计：
复制代码
ItemQtySum
----------------------------------------------
Chair311.00
Table347.00
ALL658.00
包含具有多个维度的CUBE的SELECT语句可生成大型结果集，因为这些语句会为所有维度中各值的所有组合都生成相应的行。
这些大型结果集包含的数据可能会过多而不易于阅读和理解。
此问题的一种解决办法是将SELECT语句放入视图中：
复制代码
CREATEVIEWInvCubeAS
SELECTCASEWHEN（GROUPING（Item）=1）THEN'ALL'
ELSEISNULL（Item,'UNKNOWN'）
ENDASItem,
CASEWHEN（GROUPING（Color）=1）THEN'ALL'
ELSEISNULL（Color,'UNKNOWN'）
ENDASColor,
SUM（Quantity）ASQtySum
FROMInventory
GROUPBYItem,ColorWITHCUBE
然后即可用该视图来仅查询您感兴趣的维度值：
复制代码
SELECT*
FROMInvCube
WHEREItem='Chair'
ANDColor='ALL'
ItemColorQtySum
------------------------------------------------------------------
ChairALL311.00
（1row（s）affected）
SQLServer2005联机丛书（2008年11月）
使用ROLLUP汇总数据
在生成包含小计和合计的报表时，ROLLUP运算符很有用。
ROLLUP运算符生成的结果集类似于CUBE运算符生成的结果集。
有关详细信息，请参阅使用CUBE汇总数据。
下面是CUBE和ROLLUP之间的具体区别：
∙CUBE生成的结果集显示了所选列中值的所有组合的聚合。
∙ROLLUP生成的结果集显示了所选列中值的某一层次结构的聚合。
例如，简单表Inventory包含下列数据：
复制代码
ItemColorQuantity
------------------------------------------------------------------
TableBlue124
TableRed223
ChairBlue101
ChairRed210
以下查询将生成小计报表：
复制代码
SELECTCASEWHEN（GROUPING（Item）=1）THEN'ALL'
ELSEISNULL（Item,'UNKNOWN'）
ENDASItem,
CASEWHEN（GROUPING（Color）=1）THEN'ALL'
ELSEISNULL（Color,'UNKNOWN'）
ENDASColor,
SUM（Quantity）ASQtySum
FROMInventory
GROUPBYItem,ColorWITHROLLUP
ItemColorQtySum
------------------------------------------------------------------
ChairBlue101.00
ChairRed210.00
ChairALL311.00
TableBlue124.00
TableRed223.00
TableALL347.00
ALLALL658.00
（7row（s）affected）
如果将查询中的ROLLUP关键字更改为CUBE，那么CUBE结果集保持不变，只是在末尾还会返回下列两行：
复制代码
ALLBlue225.00
ALLRed433.00
CUBE操作为Item和Color中值的可能组合生成行。
例如，CUBE不仅报告Color值与Item值Chair（Red、Blue和Red+Blue）的所有可能组合，还报告Item值与Color值Red（Chair、Table和Chair+Table）的所有可能组合。
对于GROUPBY子句的右列中的每个值，ROLLUP操作并不报告左列中各值的所有可能组合。
例如，ROLLUP并不报告每个Color值的Item值的所有可能组合。
ROLLUP操作的结果集与COMPUTEBY返回的结果集具有类似的功能。
然而，ROLLUP具有下列优点：
∙ROLLUP返回单个结果集，而COMPUTEBY返回多个结果集，而多个结果集会增加应用程序代码的复杂性。
∙ROLLUP可以在服务器游标中使用，而COMPUTEBY则不可以。
∙有时，查询优化器为ROLLUP生成的执行计划比为COMPUTEBY生成的更为高效。
SQLServer2008R2联机丛书（11月CTP）
用COMPUTE和COMPUTEBY汇总数据
[本主题为预先发布的文档内容，在未来的版本中可能会更改。
包括以占位符形式出现的空白主题。
请注意：
为了提供更多的中文内容，Microsoft引进了非传统翻译方式。
在本预发行版本中，文档中的部分内容就是使用非传统翻译方式翻译。
Microsoft知道使用非传统翻译方式翻译的文档很难尽善尽美，内容中或许会有词汇、句法、语法方面的错误。
本文档的正式版本将使用传统翻译方式翻译，提供与上一版本正式版相同的译文品质。
]
提供COMPUTE和COMPUTEBY是为了向后兼容。
请改为使用下列组件：
∙SQLServer2005AnalysisServices（SSAS）与用于AnalysisServices的OLEDB或MicrosoftActiveX多维数据对象（ADOMD）一起使用。
有关详细信息，请参阅AnalysisServices数据访问接口（AnalysisServices-多维数据）。
COMPUTEBY子句使您得以用同一SELECT语句既查看明细行，又查看汇总行。
可以计算子组的汇总值，也可以计算整个结果集的汇总值。
COMPUTE子句需要下列信息：
∙可选BY关键字。
它基于每一列计算指定的行聚合。
∙行聚合函数名称。
包括SUM、AVG、MIN、MAX或COUNT。
∙要对其执行行聚合函数的列。
COMPUTE生成的结果集
COMPUTE所生成的汇总值在查询结果中显示为单独的结果集。
包括COMPUTE子句的查询结果类似于控制中断报表。
此报表的汇总值由指定的分组（或中断）控制。
可以为各组生成汇总值，也可以对同一组计算多个聚合函数。
当COMPUTE带有可选的BY子句时，符合SELECT条件的每个组都有两个结果集：
∙每个组的第一个结果集是明细行集，其中包含该组的选择列表信息。
∙每个组的第二个结果集有一行，其中包含该组的COMPUTE子句中所指定的聚合函数的小计。
当COMPUTE不带可选的BY子句时，SELECT语句有两个结果集：
∙每个组的第一个结果集是包含选择列表信息的所有明细行。
∙第二个结果集有一行，其中包含COMPUTE子句中所指定的聚合函数的合计。
使用COMPUTE的示例
下列SELECT语句使用简单COMPUTE子句来生成SalesOrderDetail表的单价和折扣的总计：
复制代码
USEAdventureWorks2008R2;
GO
SELECTSalesOrderID,UnitPrice,UnitPriceDiscount
FROMSales.SalesOrderDetail
ORDERBYSalesOrderID
COMPUTESUM（UnitPrice）,SUM（UnitPriceDiscount）;
下列查询在COMPUTE子句中加入BY关键字，以生成每个销售订单的小计。
复制代码
USEAdventureWorks2008R2;
GO
SELECTSalesOrderID,UnitPrice,UnitPriceDiscount
FROMSales.SalesOrderDetail
ORDERBYSalesOrderID
COMPUTESUM（UnitPrice）,SUM（UnitPriceDiscount）BYSalesOrderID;
此SELECT语句的结果为每个销售订单返回两个结果集。
每个销售订单的第一个结果集是一个行集，其中包含选择列表中所指定的信息。
每个销售订单的第二个结果集包含COMPUTE子句中的两个SUM函数的小计。
注意：
在某些实用工具（如osql）中，显示多个小计或合计聚合汇总，似乎每个总计都是结果集中的单独一行。
这与该实用工具设置的输出格式有关；小计或合计聚合返回时单独占用一行。
其他应用程序（如SQLServerManagementStudio）将多个聚合设置在同一行。
比较COMPUTE和GROUPBY
COMPUTE和GROUPBY之间的区别汇总如下：
∙GROUPBY生成单个结果集。
每个组都有一个只包含分组依据列和显示该组子聚合的聚合函数的行。
选择列表只能包含分组依据列和聚合函数。
∙COMPUTE生成多个结果集。
一种结果集包含每个组的明细行，其中包含选择列表中的表达式。
另一种结果集包含组的子聚合，或SELECT语句的总聚合。
选择列表可包含除分组依据列或聚合函数之外的其他表达式。
聚合函数在COMPUTE子句中指定，而不是在选择列表中指定。
下列查询使用GROUPBY和聚合函数。
该查询将返回一个结果集，其中每个组有一行，该行包含该组的聚合小计。
复制代码
USEAdventureWorks2008R2;
GO
SELECTSalesOrderID,SUM（UnitPrice）,SUM（UnitPriceDiscount）
FROMSales.SalesOrderDetail
GROUPBYSalesOrderID;
注意：
您不能在COMPUTE或COMPUTEBY子句中包括ntext、text或image数据类型。
SQL连接JOIN例解。
（左连接，右连接，全连接，内连接，交叉连接，自连接）
假设有如下表：
一个为投票主表，一个为投票者信息表～记录投票人IP及对应投票类型，左右连接实际说是我们联合查询的结果以哪个表为准～
1：
如右接连rightjoin或rightouterjoin：
我们以右边voter表为准，则左表（voteMaster）中的记录只有当其ID在右边（voter）中存在时才会显示出来，如上图，左边中ID为3.4.5.6因为这些ID右表中没有相应记录，所以没有显示！
2：
因此我们自然能理解左连接leftjoin或者leftouterjoin
可见，现在右边中ID在中存在时才会显示，当右边中没有相应数据时则用NULL代替！
3：
全连接fulljoin或者fullouterjoin,为二个表中的数据都出来，这里演示效果与上一样！
4：
内连接innerjoin或者join;它为返回字段ID同时存在于表voteMaster和voter中的记录
5：
交叉连接（完全连接）crossjoin不带where条件的
没有WHERE子句的交叉联接将产生联接所涉及的表的笛卡尔积。
第一个表的行数乘以第二个表的行数等于笛卡尔积结果集的大小。
（table1和table2交叉连接产生6*3=18条记录）
等价selectvm.id,vm.voteTitle,vt.ipfromvoteMasterasvm,voterasvt
6:
自连接。
在这里我用我前段时间一个电力项目中的例子（改造过）
如下表：
这是一个部门表，里面存放了部门及其上级部门，但都放在同一张表中，我们假设现在需要用SQL查询出各部门及其上级部门！
就如何做，
当然，不用自连接也一样，可以如下：
我们达到预期目的！
在这个查询中使用了一个子查询完成对上级部门名的查询，如果使用自连接，那么结构上感觉会清晰很多。
是不是也同样完成了功能呢，这里除了使用自连接外，还使用了左连接，因为省电力没有上级部门，他是老大，如果使用内连接，就会把这条记录过滤掉，因为没有和他匹配的上级部门。
自连接用的比较多的就是对权形结构的查询！
类似上表！
升级会员 