请教有关Grouping的用法

[Antiquesoft]

Select
Case When Grouping(fID) = 1 and Grouping(网上网下) = 1 and Grouping(市场) = 1 and Grouping(证券分类) = 1 then ''合计''
When Grouping(fID) = 1 and Grouping(网上网下) = 1 and Grouping(市场) = 1 then 证券分类 + ''小计''
When Grouping(fID) = 1 and Grouping(网上网下) = 1 then Null
else Max(日期) end 日期,
from
....
Group by 证券分类 ,市场, 网上网下, fID
with Rollup
having Grouping(fID) = 1 and not(Grouping(网上网下) = 1 and not (Grouping(市场) = 1 and not Grouping(证券分类) = 1) )

以上语句中如果 不用Having 来进行分组后的过滤的话，数据会有冗余！
但遇到 多分组条件的情况，却终始得不到需要的结果！
分组字段 的前后顺序对结果 影响很大

请教 Having中的Grouping有什么规律，

谢谢！

[dawugui]

有关ROLLUP,CUBE,GROUPING,COMPUTE,COMPUTE BY的用法
(爱新觉罗.毓华 2007-10-23于浙江杭州)

ROLLUP
ROLLUP运算符生成的结果集显示了所选列中值的某一层次结构的聚合。返回单个结果集。
在生成包含小计和合计的报表时，ROLLUP运算符很有用。
ROLLUP运算符生成的结果集类似于CUBE运算符所生成的结果集。

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

GROUPING
是一个聚合函数,它产生一个附加的列，当用CUBE或ROLLUP运算符添加行时，附加的列输出值为1，
当所添加的行不是由CUBE或ROLLUP产生时，附加列值为0。
仅在与包含CUBE或ROLLUP运算符的GROUP BY子句相联系的选择列表中才允许分组。
注释：分组用于区分由CUBE和ROLLUP返回的空值和标准的空值。作为CUBE或ROLLUP操作结果返回的NULL是NULL的特殊应用。
它在结果集内作为列的占位符，意思是"全体"。

COMPUTE
COMPUTE子句生成合计作为附加的汇总列出现在结果集的最后。当与BY一起使用时，COMPUTE子句在结果集内生成控制中断和分类汇总。
可在同一查询内指定COMPUTE BY和COMPUTE。
COMPUTE子句需要下列信息：
1、可选的 BY 关键字，该关键字可按对一列计算指定的行聚合。
2、行聚合函数名称；例如：AVG --数字表达式中所有值的平均值，
COUNT --选定的行数，
MAX --表达式中的最(大)高值，
MIN --表达式中的最(小)低值，
STDEV --表达式中所有值的统计标准偏差，
STDEVP --表达式中所有值的填充统计标准偏差，
VAR --表达式中所有值的统计方差，
VARP --表达式中所有值的填充统计方差，
SUM --数字表达式中所有值的和。
3、要对其执行行聚合函数的列。
说明：在带 COMPUTE 子句的 SELECT 语句中，选择列表中的列顺序替代 COMPUTE 子句中的聚合函数顺序。
ODBC 和 DB-library 程序员必须知道这种顺序要求，以将聚合函数结果放在正确的位置。
由于包含 COMPUTE 的语句生成表并且这些表的汇总结果不存储在数据库中，因此在 SELECT INTO 语句中不能使用 COMPUTE。
因而，任何由 COMPUTE 生成的计算结果不出现在用 SELECT INTO 语句创建的新表内。
当 SELECT 语句是 DECLARE CURSOR 语句的一部分时，不能使用 COMPUTE 子句。

COMPUTE BY
COMPUTE BY子句使您得以用同一SELECT语句既查看明细行，又查看汇总行。可以计算子组的汇总值，也可以计算整个结果集的汇总值。
如果使用 COMPUTE BY，则必须也使用 ORDER BY 子句。表达式必须与在 QRDER BY 后列出的子句相同或是其子集，
并且必须按相同的序列。例如，如果 ORDER BY 子句是：
ORDER BY a, b, c
则 COMPUTE子句可以是下面的任意一个（或全部）：
COMPUTE BY a, b, c
COMPUTE BY a, b
COMPUTE BY a

COMPUTE生成的结果集
COMPUTE所生成的汇总值在查询结果中显示为分离的结果集。包括COMPUTE子句的查询的结果类似于控制中断报表，
即汇总值由指定的组(或称中断)控制的报表。可以为各组生成汇总值，也可以对同一组计算多个聚合函数。
当COMPUTE 带有可选的 BY 子句时，符合 SELECT 条件的每个组都有两个结果集：
每个组的第一个结果集是明细行集，其中包含该组的选择列表信息。
每个组的第二个结果集有一行，其中包含该组的 COMPUTE 子句中所指定的聚合函数的小计。
当COMPUTE 不带可选的 BY 子句时，SELECT 语句有两个结果集：
每个组的第一个结果集是包含选择列表信息的所有明细行。
第二个结果集有一行，其中包含 COMPUTE 子句中所指定的聚合函数的合计。

说明：在COMPUTE或COMPUTE BY子句中，不能包含ntext、text或image数据类型。

比较COMPUTE和GROUP BY
COMPUTE和GROUP BY之间的区别汇总如下：
1、GROUP BY生成单个结果集。每个组都有一个只包含分组依据列和显示该组子聚合的聚合函数的行。
选择列表只能包含分组依据列和聚合函数。
2、COMPUTE生成多个结果集。一类结果集包含每个组的明细行，其中包含选择列表中的表达式。
另一类结果集包含组的子聚合，或 SELECT 语句的总聚合。
选择列表可包含除分组依据列或聚合函数之外的其它表达式。聚合函数在 COMPUTE 子句中指定，而不是在选择列表中。

---------------------------------------------------------------------------
--原始数据
Item Color Qty
---------- ---------- -----------
Table Blue 100
Table Blue 200
Table Red 100
Chair Blue 100
Chair Red 200
Chair Red 200
--创建表并插入数据
create table tb(Item varchar(10),Color varchar(10),Qty int)
insert into tb values('Table','Blue',100)
insert into tb values('Table','Blue',200)
insert into tb values('Table','Red ',100)
insert into tb values('Chair','Blue',100)
insert into tb values('Chair','Red ',200)
insert into tb values('Chair','Red ',200)
go
----------------------------------------------------------------------------
1、ROLLUP
select item,color,sum(qty) qtysum from tb group by item,color with rollup
/*
item color qtysum
---------- ---------- -----------
Chair Blue 100
Chair Red 400
Chair NULL 500
Table Blue 300
Table Red 100
Table NULL 400
NULL NULL 900
*/

2、GROUPING
select item,color,Sum(qty) qtysum,grouping(item) grouping_item,grouping(color) grouping_color
from tb group by item,color with rollup
/*
item color qtysum grouping_item grouping_color
---------- ---------- ----------- ------------- --------------
Chair Blue 100 0 0
Chair Red 400 0 0
Chair NULL 500 0 1
Table Blue 300 0 0
Table Red 100 0 0
Table NULL 400 0 1
NULL NULL 900 1 1
*/

3、利用grouping将上面的空(null)转换为合计，小计
SELECT CASE WHEN (GROUPING(Item) = 1) THEN '合计' else item END AS Item,
CASE WHEN (GROUPING(Item) = 1) and (GROUPING(Color) = 1) THEN '合计'
WHEN (GROUPING(Item) = 0) and (GROUPING(Color) = 1) THEN '小计'
ELSE color END AS Color,
SUM(Qty) AS QtySum
FROM tb
GROUP BY Item, Color
WITH ROLLUP
/*
Item Color QtySum
---------- ---------- -----------
Chair Blue 100
Chair Red 400
Chair 小计 500
Table Blue 300
Table Red 100
Table 小计 400
合计 合计 900
*/

[dawugui]

4、CUBE
select item,color,sum(qty) qtysum from tb group by item,color with cube
/*
item color qtysum
---------- ---------- -----------
Chair Blue 100
Chair Red 400
Chair NULL 500
Table Blue 300
Table Red 100
Table NULL 400
NULL NULL 900
NULL Blue 400
NULL Red 500
*/
/*
我们着重考查下列各行：

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

Table (null) 400
这一行类似，但报告的是 Item 维度中值为 Table 的所有行的小计。

(null) (null) 900
这一行报告了多维数据集的总计。Item 和 Color 维度的值都是 NULL，表示两个维度中的所有值都汇总在该行中。

(null) Blue 400
(null) Red 500
这两行报告了 Color 维度的小计。两行中的 Item 维度值都是 NULL，表示聚合数据来自 Item 维度为任意值的行。
*/

5、使用 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(qty) AS QtySum
FROM tb
GROUP BY Item, Color WITH CUBE
/*
Item Color QtySum
---------- ---------- -----------
Chair Blue 100
Chair Red 400
Chair ALL 500
Table Blue 300
Table Red 100
Table ALL 400
ALL ALL 900
ALL Blue 400
ALL Red 500
*/

6、COMPUTE
select item,color,qty from tb order by item,color compute sum(qty)
/*
item color qty
---------- ---------- -----------
Chair Blue 100
Chair Red 200
Chair Red 200
Table Blue 100
Table Blue 200
Table Red 100

sum
===========
900
*/
--上面的结果也可以用union(all)来实现
select item,color,qty from tb
union all
select item='',color='',sum(qty) from tb
/*
item color qty
---------- ---------- -----------
Table Blue 100
Table Blue 200
Table Red 100
Chair Blue 100
Chair Red 200
Chair Red 200
900
*/

7、COMPUTE BY
select item,color,qty from tb order by item,color compute sum(qty) by item,color
/*
item color qty
---------- ---------- -----------
Chair Blue 100
sum
===========
100
item color qty
---------- ---------- -----------
Chair Red 200
Chair Red 200
sum
===========
400
item color qty
---------- ---------- -----------
Table Blue 100
Table Blue 200
sum
===========
300
item color qty
---------- ---------- -----------
Table Red 100
sum
===========
100
*/
/*
每个组的第一个结果集是一个行集，其中包含选择列表中所请求的信息。
每个组的第二个结果集包含 COMPUTE 子句中SUM 函数的小计。
*/

8、同时使用avg,sum
select item,color,qty from tb order by item,color compute avg(qty),sum(qty) by item,color
/*
item color qty
---------- ---------- -----------
Chair Blue 100
avg
===========
100
sum
===========
100
item color qty
---------- ---------- -----------
Chair Red 200
Chair Red 200
avg
===========
200
sum
===========
400
item color qty
---------- ---------- -----------
Table Blue 100
Table Blue 200
avg
===========
150
sum
===========
300
item color qty
---------- ---------- -----------
Table Red 100
avg
===========
100
sum
===========
100
*/

[dawugui]

--看这两段:
2、GROUPING
select item,color,Sum(qty) qtysum,grouping(item) grouping_item,grouping(color) grouping_color 
from tb group by item,color with rollup
/*
item       color      qtysum      grouping_item grouping_color 
---------- ---------- ----------- ------------- -------------- 
Chair      Blue       100         0             0
Chair      Red        400         0             0
Chair      NULL       500         0             1
Table      Blue       300         0             0
Table      Red        100         0             0
Table      NULL       400         0             1
NULL       NULL       900         1             1
*/

3、利用grouping将上面的空(null)转换为合计，小计
SELECT CASE WHEN (GROUPING(Item) = 1) THEN '合计' else item END AS Item,
       CASE WHEN (GROUPING(Item) = 1) and (GROUPING(Color) = 1) THEN '合计' 
            WHEN (GROUPING(Item) = 0) and (GROUPING(Color) = 1) THEN '小计'
            ELSE color END AS Color,
       SUM(Qty) AS QtySum
FROM tb
GROUP BY Item, Color 
WITH ROLLUP
/*
Item       Color      QtySum      
---------- ---------- ----------- 
Chair      Blue       100
Chair      Red        400
Chair      小计       500
Table      Blue       300
Table      Red        100
Table      小计       400
合计       合计       900
*/

[-狙击手-]

爱新觉罗