要求按A.Contract_NO, B.Style, C.Color 分别统计出S.Quantity的值 只能是通过查询
select A.Contract_NO, B.Style, C.Color , sum(S.Quantity)
from A,B,C,S
where ....
group by A.Contract_NO, B.Style, C.Color 具体的连接如楼上的建议.
select A.Contract_NO, B.Style, C.Color , sum(S.Quantity)
from A,B,C,S
where ....
group by A.Contract_NO, B.Style, C.Color 具体的连接如楼上的建议.
, B.Style, C.Color into #
From A inner join
B B on A.id = B.A_id inner join
C C on B.id = C.B_id inner join
S S on S.id = C.S_ID
Where A.contract_no = '@a'
group by d.customer, A.contract_no, B.Date, C.supplier, C.Supplier_Address
, B.Style, C.Color select date,supplier,contract_no item,sum(sumty) sumty from # group by date,supplier,contract_no
union
select date,supplier,style item,sum(sumty) sumty from # group by date,supplier,style
union
select date,supplier,color item,sum(sumty) sumty from # group by date,supplier,color
ContractNO, Date, Supplier, Quantity, Style, Color
-------------------------------------------------
BGO07001 2008.1.12 VCL 12 A Red
BGO07001 2008.1.12 VCL 12 A Blue
BGO07001 2008.1.12 VCL 12 B Red
BGO07001 2008.1.12 VCL 12 B Red
BGO07002 2008.1.12 VCL 12 C Red
BGO07002 2008.1.12 VCL 12 C Red结果应该为:
BGO07001 2008.1.12 VCL 12 A Red
BGO07001 2008.1.12 VCL 12 A Blue
BGO07001 2008.1.12 VCL 24 B Red
BGO07002 2008.1.12 VCL 24 C Red
最好还有下面几行:(不过我觉得似乎不可能得到)
BGO07001 2008.1.12 VCL 24 A
BGO07001 2008.1.12 VCL 24 B
BGO07001 2008.1.12 VCL 24 c
BGO07001 2008.1.12 VCL 48
BGO07002 2008.1.12 VCL 24
Quantity int, Style varchar(10), Color varchar(10))
insert @t select 'BGO07001', '2008.1.12', 'VCL', 12, 'A', 'Red'
union all select 'BGO07001', '2008.1.12', 'VCL', 12, 'A', 'Blue'
union all select 'BGO07001', '2008.1.12', 'VCL', 12, 'B', 'Red'
union all select 'BGO07001', '2008.1.12', 'VCL', 12, 'B', 'Red'
union all select 'BGO07002', '2008.1.13', 'VCL', 12, 'C', 'Red'
union all select 'BGO07002', '2008.1.12', 'VCL', 12, 'C', 'Red' select ContractNo,date,supplier
,case when grouping(style)+grouping(color)=2 then '合计' else style end style
,case when grouping(color)=1 then isnull(style+'小计','') else color end color
,sum(quantity) as quantityfrom @t
group by contractno,date,supplier,style,color
with rollup
having grouping(contractno)+grouping(date)+grouping(supplier) =0
/*
ContractNo date supplier style color quantity
---------- ---------- ---------- ---------- -------------- -----------
BGO07001 2008.1.12 VCL A Blue 12
BGO07001 2008.1.12 VCL A Red 12
BGO07001 2008.1.12 VCL A A小计 24
BGO07001 2008.1.12 VCL B Red 24
BGO07001 2008.1.12 VCL B B小计 24
BGO07001 2008.1.12 VCL 合计 48
BGO07002 2008.1.12 VCL C Red 12
BGO07002 2008.1.12 VCL C C小计 12
BGO07002 2008.1.12 VCL 合计 12
BGO07002 2008.1.13 VCL C Red 12
BGO07002 2008.1.13 VCL C C小计 12
BGO07002 2008.1.13 VCL 合计 12
*/
(爱新觉罗.毓华 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
*/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
*/
Quantity int, Style varchar(10), Color varchar(10))
insert @t select 'BGO07001', '2008.1.12', 'VCL', 12, 'A', 'Red'
union all select 'BGO07001', '2008.1.12', 'VCL', 12, 'A', 'Blue'
union all select 'BGO07001', '2008.1.12', 'VCL', 12, 'B', 'Red'
union all select 'BGO07001', '2008.1.12', 'VCL', 12, 'B', 'Red'
union all select 'BGO07002', '2008.1.12', 'VCL', 12, 'C', 'Red'
union all select 'BGO07002', '2008.1.12', 'VCL', 12, 'C', 'Red'
select * from(
select ContractNo,date,supplier
,sum(quantity) as quantity
,case when grouping(style)+grouping(color)=2 then '' else style end style
,case when grouping(color)=1 then '' else color end colorfrom @t
group by contractno,date,supplier,style,color
with rollup
having grouping(contractno)+grouping(date)+grouping(supplier) =0
) a
order by case when style='' and color='' then 'ZZZ'
when style='' and color<>'' then 'ZZ'
when color='' then 'Z'
else style+color end/*
ContractNo date supplier quantity style color
---------- ---------- ---------- ----------- ---------- ----------
BGO07001 2008.1.12 VCL 12 A Blue
BGO07001 2008.1.12 VCL 12 A Red
BGO07001 2008.1.12 VCL 24 B Red
BGO07002 2008.1.12 VCL 24 C Red
BGO07002 2008.1.12 VCL 24 C
BGO07001 2008.1.12 VCL 24 B
BGO07001 2008.1.12 VCL 24 A
BGO07001 2008.1.12 VCL 48
BGO07002 2008.1.12 VCL 24
*/
Quantity int, Style varchar(10), Color varchar(10))
insert @t select 'BGO07001', '2008.1.12', 'VCL', 12, 'A', 'Red'
union all select 'BGO07001', '2008.1.12', 'VCL', 12, 'A', 'Blue'
union all select 'BGO07001', '2008.1.12', 'VCL', 12, 'B', 'Red'
union all select 'BGO07001', '2008.1.12', 'VCL', 12, 'B', 'Red'
union all select 'BGO07002', '2008.1.12', 'VCL', 12, 'C', 'Red'
union all select 'BGO07002', '2008.1.12', 'VCL', 12, 'C', 'Red'
select * from(
select ContractNo,date,supplier
,sum(quantity) as quantity
,case when grouping(style)+grouping(color)=2 then '' else style end style
,case when grouping(color)=1 then '' else color end colorfrom @t
group by contractno,date,supplier,style,color
with rollup
having grouping(contractno)+grouping(date)+grouping(supplier) =0
) a
order by case when style='' and color='' then 'ZZZ'
when style<>'' and color='' then 'Z'+style
else style+color end/*ContractNo date supplier quantity style color
---------- ---------- ---------- ----------- ---------- ----------
BGO07001 2008.1.12 VCL 12 A Blue
BGO07001 2008.1.12 VCL 12 A Red
BGO07001 2008.1.12 VCL 24 B Red
BGO07002 2008.1.12 VCL 24 C Red
BGO07001 2008.1.12 VCL 24 A
BGO07001 2008.1.12 VCL 24 B
BGO07002 2008.1.12 VCL 24 C
BGO07001 2008.1.12 VCL 48
BGO07002 2008.1.12 VCL 24
*/