• 主页
• 基础类
• 应用实例
• 新技术前沿

请教有关Grouping的用法

Antiquesoft 2007-12-27 02:49:27
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) )

...全文
170 点赞 收藏 4

4 条回复

-狙击手- 2007-12-27

dawugui 2007-12-27
--看这两段:
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

*/

dawugui 2007-12-27
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

Table (null) 400

(null) (null) 900

(null) Blue 400
(null) Red 500

*/

5、使用 GROUPING 区分空值
CUBE 操作所生成的空值带来一个问题：如何区分 CUBE 操作所生成的 NULL 值和从实际数据中返回的 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
*/
/*

*/

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 2007-12-27

(爱新觉罗.毓华 2007-10-23于浙江杭州)

ROLLUP
ROLLUP运算符生成的结果集显示了所选列中值的某一层次结构的聚合。返回单个结果集。

ROLLUP运算符生成的结果集类似于CUBE运算符所生成的结果集。

CUBE
CUBE运算符生成的结果集是多维数据集。多维数据集是事实数据的扩展，事实数据即记录个别事件的数据。

GROUPING

COMPUTE
COMPUTE子句生成合计作为附加的汇总列出现在结果集的最后。当与BY一起使用时，COMPUTE子句在结果集内生成控制中断和分类汇总。

COMPUTE子句需要下列信息：
1、可选的 BY 关键字，该关键字可按对一列计算指定的行聚合。
2、行聚合函数名称；例如：AVG --数字表达式中所有值的平均值，
COUNT --选定的行数，
MAX --表达式中的最(大)高值，
MIN --表达式中的最(小)低值，
STDEV --表达式中所有值的统计标准偏差，
STDEVP --表达式中所有值的填充统计标准偏差，
VAR --表达式中所有值的统计方差，
VARP --表达式中所有值的填充统计方差，
SUM --数字表达式中所有值的和。
3、要对其执行行聚合函数的列。

ODBC 和 DB-library 程序员必须知道这种顺序要求，以将聚合函数结果放在正确的位置。

COMPUTE BY
COMPUTE BY子句使您得以用同一SELECT语句既查看明细行，又查看汇总行。可以计算子组的汇总值，也可以计算整个结果集的汇总值。

ORDER BY a, b, c

COMPUTE BY a, b, c
COMPUTE BY a, b
COMPUTE BY a

COMPUTE生成的结果集
COMPUTE所生成的汇总值在查询结果中显示为分离的结果集。包括COMPUTE子句的查询的结果类似于控制中断报表，

COMPUTE和GROUP BY之间的区别汇总如下：
1、GROUP BY生成单个结果集。每个组都有一个只包含分组依据列和显示该组子聚合的聚合函数的行。

2、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

*/

MS-SQL Server

3.2w+

MS-SQL Server相关内容讨论专区