澳门金沙vip 5

【澳门金沙vip】SQLServer中汇总功能的使用GROUPING,ROLLUP和CUBE

/* –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 值的所有可能组合,
而且报告与 Color 值 Red 相组合的 Item 值的所有可能组合。 对于 GROUP BY
子句中右边的列中的每个值,ROLLUP
操作并不报告左边一列中值的所有可能组合。例如, ROLLUP 并不对每个 Color
值报告 Item 值的所有可能组合。 ROLLUP 操作的结果集具有类似于 COMPUTE BY
所返回结果集的功能;然而,ROLLUP 具有下列优点: ROLLUP
返回单个结果集;COMPUTE BY
返回多个结果集,而多个结果集会增加应用程序代码的复杂性。 ROLLUP
可以在服务器游标中使用;COMPUTE BY 不可以。 有时,查询优化器为 ROLLUP
生成的执行计划比为 COMPUTE BY 生成的更为高效。 */

GROUP
BY语句从英文的字面意义上理解就是“根据(By)一定的规则进行分组(Group)”

作用是通过一定的规则将一个数据集划分成若干个小的区域,然后针对若干个小区域进行数据处理

第一次看到这样的SQL语句,看不懂,其中用到了下面的不常用的

在介绍GROUP BY语句之前,首先需要介绍和它关系密切的小伙伴——聚合函数


聚合函数是对多值数据执行计算并返回单值的函数

以下是常用的聚合函数:

  1. AVG——返回平均值,其中空值被忽略

例: select  dept_no, avg(sal) from table group by dept_no  
//统计不同部门的平均工资

2.COUNT——返回数量

例: select count(name) from table    //统计公司员工总数

count(字段名)与count(*)的区别:

如果字段名中包含空值NULL,那么count(字段名)会忽略该空值,而count(*)不会忽略,依然将其计入总数

例: 

dept_1 dept_2

 A             D

 B           null

 C            E

由于dept_2 中有 null 值,用 count(dept_2) 的结果就是 2,用 count(*)
的结果就是 3

  1. MAX——返回最大值

例: select max(sal) from table   //查找公司的最高工资

  1. MIN——返回最大值

例: select min(sal) from table   //查找公司的最低工资

5.SUM——返回和

例: select sum(sal) from table   //统计公司工资总额


聚集函数:GROUPING

GROUP BY + [分组字段]

其中分组字段可以有多个。在执行了这个操作以后,数据集将根据分组字段的值将一个数据集划分成各个不同的小组。

比如有如下数据集(Table_Fruitinfo),其中水果名称(FruitName)和出产国家(ProductPlace)为联合主键:

澳门金沙vip 1

Table_Fruitinfo

如果我们想知道每个国家有多少种水果,那么我们可以通过如下SQL语句来完成:

SELECT COUNT(\) AS 水果种类, ProductPlaceAS 出产国*

FROM Table_Fruitinfo

GROUP BY ProductPlace

这句SQL语句可以解释成“我按照出产国家(ProductPlace)将数据集进行分组,然后分别统计各个国家的水果种类数”。

注意:如果我们这里水果种类不是用Count(*),而是类似如下写法的话:

SELECT FruitName, ProductPlace

FROM Table_Fruitinfo**

GROUP BY ProductPlace

那么SQL在执行此语句的时候会报如下的类似错误:

选择列表中的列’Table_Fruitinfo.FruitName’无效,因为该列没有包含在聚合函数或GROUPBY子句中

这就是我们需要注意的一点,使用GROUP
BY语句时,返回集中的非聚合字段要么包含在Group
By语句的后面,作为分组的依据;要么就要包含在聚合函数中。

我们可以将GROUP BY操作想象成如下的一个过程:

首先通过SELECT语句得到一个结果集,然后根据分组字段,将具有相同分组字段的记录归并成了一条记录。这时候那些不作为分组依据的字段就有可能出现多个值(非聚合字段),但是一种分组情况只能有一条记录,而一个数据格是无法放入多个数值的,所以就需要通过一定的处理(聚合函数)将这些多值的列转化成单值,然后放入对应的数据格中。

用于汇总数据用的运算符: ROLLUP

GROUP BY ALL + [分组字段]

在不使用 ALL 关键字的情况下,包含 GROUP BY 子句的 SELECT
语句查找的结果只会显示满足搜索条件的记录。而使用 ALL
关键字,即使某些记录不满足搜索条件,查询结果也将显示 GROUP BY
子句生成的所有组,只是这些不满足搜索条件的记录不会进行真正的统计,而是用默认值0或者NULL来代替聚合函数的返回值。
说得有点绕,举个例子吧。

还是使用之前的水果信息数据集

澳门金沙vip 2

Table_Fruitinfo

首先我们不使用带ALL关键字的Group By语句:

SELECT COUNT(\) AS 水果种类, ProductPlace AS 出产国*

FROM Table_Fruitinfo

WHERE (ProductPlace <> ‘Japan’)

GROUP BY ProductPlace

操作符 <> 表示  “不等于”

那么在最后结果中由于Japan不符合where语句,所以分组结果中将不会出现Japan。

现在我们加入ALL关键字:

SELECT COUNT(\) AS 水果种类, ProductPlace AS 出产国*

FROM Table_Fruitinfo**

WHERE ( ProductPlace <> ‘Japan’)

GROUP BY ALL ProductPlace

重新运行后,我们可以看到Japan的分组,但是对应的“水果种类”不会进行真正的统计,聚合函数会根据返回值的类型用默认值0或者NULL来代替聚合函数的返回值。

SELECTCASEGROUPING(o.customerid)WHEN0THENo.customeridELSE'(Total)’ENDASAllCustomersSummary,CASEGROUPING(od.orderid)WHEN0THENod.orderidELSE-1ENDASIndividualCustomerSummary,SUM(od.quantity*od.unitprice)ASpriceFROMOrderso,[OrderDetails]odWHEREYear(o.orderdate)=1998ANDod.orderid=o.orderidGROUPBYo.customerid,od.orderidWITHROLLUPORDERBYAllCustomersSummary

GROUP BY + [分组字段] WITH CUBE | ROLL UP

GROUP BY ALL语句不能和CUBE / ROLL UP关键字一起使用

CUBE 运算符在 SELECT 语句的 GROUP BY
子句中指定。SELECT语句后跟所查找的维度列聚合函数。GROUP BY
后跟所查找的维度列关键字 WITH CUBE

结果集包含维度列中各值的所有可能组合(笛卡尔积),以及与这些维度值组合相匹配的基础行中的聚合值

例:

澳门金沙vip 3

Inventory

以下查询将返回一个结果集,其中包含Item和Color的所有可能组合的Quantity小计:

SELECT Item, Color, SUM(Quantity) AS QtySum

FROM Inventory

GROUP BY Item, Color WITH CUBE

以下是结果集:

澳门金沙vip 4

结果集

可以看到,CUBE将维度列Item和Color的所有可能属性(包括null)进行组合,形成了3*3=9条不同的记录,每条记录都包含着一个小计QtySum

但是,CUBE 操作生成空值将会带来一个问题:如何区分 CUBE 操作生成的 NULL
值和在实际数据中返回的 NULL 值?

可以使用 GROUPING 函数解决此问题

如果列值来自真实数据(未知数据),GROUPING 函数将返回 0;如果列值是由
CUBE 操作生成的 NULL,则返回 1。

在 CUBE 操作中,生成的 NULL 代表所有值,所以可以将CUBE操作生成的任一
NULL 替换为字符串 ALL

真实数据中的 NULL 表示数据值未知,所以可以将真实数据中的
NULL替换为字符串 UNKNOWN

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 QtySum*

FROM Inventory

GROUP BY Item, Color WITH CUBE

ISNULL:使用指定的替换值替换 NULL。

语法:ISNULL ( check_expression , replacement_value )

如果 check_expression 不为 NULL,那么返回该表达式的值;否则返回
replacement_value。

包含具有多个维度的 CUBE 的 SELECT
语句可生成大型结果集,因为这些语句会为所有维度中各值的所有组合都生成相应的行。这些大型结果集包含的数据可能会过多而不易于阅读和理解。此问题的一种解决办法是将SELECT语句放入视图(VIEW)中:

数据库中的数据都是存储在表中的,而视图只是一个或多个表依照某个条件组合而成的结果集

一般来说可以用UPDATE,INSERT,DELETE等sql语句修改表中的数据,而对视图只能进行SELECT操作

CREATE VIEW InvCube AS

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 QtySum*

FROM Inventory

GROUP BY Item, Color WITH CUBE

然后即可用该视图来仅查询您感兴趣的维度值:

SELECT \
*

FROM InvCube

WHERE Item = ‘Chair’

AND Color = ‘ALL’

以下是结果集:

澳门金沙vip 5

结果集

参考链接:使用 CUBE
汇总数据.aspx)

参考链接:使用 ROLLUP
汇总数据.aspx)

由于暂时没有理解CUBE与ROLLUP有什么实质性区别,所以先挖个坑,以后有机会再填

查看SQL
Server的帮助才发现,厉害啊,原来还有这么厉害的东西,不由的想起以前做水晶报表的时候,原来在SQL
Server中就可以实现这样的功能.

1.用 CUBE 汇总数据

CUBE
运算符生成的结果集是多维数据集。多维数据集是事实数据的扩展,事实数据即记录个别事件的数据。扩展建立在用户打算分析的列上。这些列被称为维。多维数据集是一个结果集,其中包含了各维度的所有可能组合的交叉表格。

CUBE 运算符在 SELECT 语句的 GROUP BY
子句中指定。该语句的选择列表应包含维度列和聚合函数表达式。GROUP BY
应指定维度列和关键字 WITH
CUBE。结果集将包含维度列中各值的所有可能组合,以及与这些维度值组合相匹配的基础行中的聚合值。

例如,一个简单的表 Inventory 中包含:

Item Color Quantity -------------------- -------------------- -------------------------- Table Blue 124 Table Red 223 Chair Blue 101 Chair Red 210 

下列查询返回的结果集中,将包含 ItemColor 的所有可能组合的
Quantity 小计:

SELECT Item, Color, SUM(Quantity) AS QtySumFROM InventoryGROUP BY Item, Color WITH CUBE

下面是结果集:

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 

我们着重考查下列各行:

Chair (null) 311.00 

这一行报告了 Item 维度中值为 Chair 的所有行的小计。对 Color
维度返回了 NULL 值,表示该行所报告的聚合包括 Color
维度为任意值的行。

Table (null) 347.00 

这一行类似,但报告的是 Item 维度中值为 Table 的所有行的小计。

(null) (null) 658.00 

这一行报告了多维数据集的总计。ItemColor 维度的值都是
NULL,表示两个维度中的所有值都汇总在该行中。

(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。例如:

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
维的多维数据集,即具有任意数目维度的多维数据集。只有一个维度的多维数据集可用于生成合计,例如:

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 中所有值的总计:

Item QtySum -------------------- -------------------------- Chair 311.00 Table 347.00 ALL 658.00 

包含带有许多维度的 CUBE 的 SELECT
语句可能生成很大的结果集,因为这些语句会为所有维度中值的所有组合生成行。这些大结果集包含的数据可能过多而不易于阅读和理解。这个问题有一种解决办法是将
SELECT 语句放在视图中:

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

然后即可用该视图来只查询您感兴趣的维度值:

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