如何实现小计和合计

[lzsywk]

有两张表：产品基本资料表（CPJBZL），库存表（KC）
其表的结构如下：
产品基本资料表（CPJBZL）：
cpdm(产品代码) cpmc（产品名称） cpcd（产品长度）
001 A 0.1
002 A 0.2
003 B 0.1
004 B 0.3
005 B 0.6
006 C 0.1

库存表（KC）：
scph（生产批号） cpdm（产品代码） kcsl（库存数量）
1 001 100
2 001 200
3 001 300
4 002 400
5 002 500
6 003 600
7 003 700
8 003 800
9 004 900
10 004 1000

我要得到如下的数据：

cpdm（产品代码） 产品规格（由“产品名称” 和“长度”组合而成） kcsl（库存数量）
001 A-0.1 600 （100+200+300）
002 A-0.2 900 （400+500）
小计： 名称为 A 的产品 1500（100+200+...+500）
003 B-0.1 2100（600+700+800）
004 B-0.3 1900（900+1000）
小计： 名称为 B 的产品 4000（600+700+...+1000）
合计： 5500（100+200+...+1000）

说明：要得到上面的数据其实是要进行两次的分组求和，其中的一次的分组求和是在库存表（KC）中按产品代
码（cpdm）进行的，可以得到：
cpdm（产品代码） 产品规格（由“产品名称” 和“产品长度”组合而成） kcsl（库存数量）
001 A-0.1 600 （100+200+300）
这样的数据；
而另一次的分组求和是在库存表（KC）中按产品名称(cpmc)进行的，可以得到：
小计： 名称为 A 的产品 1500（100+200+...+500）
这样的数据。

但我不懂得要如何才能把两次分组求和的数据放在同一个数据集中。
请问要求得上述的数据，能不能由SQL语句得到。如果可以请帮忙写个SQL语句。谢谢

[lzsywk]

谢谢邹建。问题解决了

[zjcxc]

select 产品代码=case grouping(a.cpdm) when 1 then '合计:' else a.cpdm end
,产品规格=case grouping(a.cpmc+'-'+cast(a.cpcd as varchar))
when 1 then '小计:' else a.cpmc+'-'+cast(a.cpcd as varchar) end
,库存数量=sum(b.kcsl)
from CPJBZL a,KC b
where a.cpdm=b.cpdm
group by a.cpdm,a.cpmc+'-'+cast(a.cpcd as varchar) with rollup

[friendly_2008]

when 1 里的 1 是 什么意思？

[RainYang]

当用 CUBE 或 ROLLUP 运算符添加行时，附加的列输出值为1，当所添加的行不是由 CUBE 或 ROLLUP 产生时，附加列值为0

[lzsywk]

有出现如下的错误：
服务器: 消息 8120，级别 16，状态 1，行 1
列 'a.cpmc' 在选择列表中无效，因为该列既不包含在聚合函数中，也不包含在 GROUP BY 子句中。
服务器: 消息 8120，级别 16，状态 1，行 1
列 'a.cpcd' 在选择列表中无效，因为该列既不包含在聚合函数中，也不包含在 GROUP BY 子句中。

请帮我看看要如何解决这个问题

[zjcxc]

select 产品代码=case
when grouping(a.cpdm)=1 then '合计:'
when grouping(a.cpmc)=1 then '小计:'
else a.cpdm end
,产品规格=case grouping(a.cpmc) when 1 then ''
else a.cpmc+'-'+cast(a.cpcd as varchar) end
,库存数量=sum(b.kcsl)
from CPJBZL a,KC b
where a.cpdm=b.cpdm
group by a.cpdm,a.cpmc,a.cpcd with rollup
having grouping(a.cpcd)=0 or grouping(a.cpmc)=1

[zjcxc]

--测试

--测试数据
create table CPJBZL(cpdm varchar(10),cpmc varchar(10),cpcd decimal(10,1))
insert CPJBZL select '001','A',0.1
union all select '002','A',0.2
union all select '003','B',0.1
union all select '004','B',0.3
union all select '005','B',0.6
union all select '006','C',0.1

create table KC(scph int,cpdm varchar(10),kcsl int)
insert KC select 1 ,'001',100
union all select 2 ,'001',200
union all select 3 ,'001',300
union all select 4 ,'002',400
union all select 5 ,'002',500
union all select 6 ,'003',600
union all select 7 ,'003',700
union all select 8 ,'003',800
union all select 9 ,'004',900
union all select 10,'004',1000
go

--查询
select 产品代码=case
when grouping(a.cpdm)=1 then '合计:'
when grouping(a.cpmc)=1 then '小计:'
else a.cpdm end
,产品规格=case grouping(a.cpmc) when 1 then ''
else a.cpmc+'-'+cast(a.cpcd as varchar) end
,库存数量=sum(b.kcsl)
from CPJBZL a,KC b
where a.cpdm=b.cpdm
group by a.cpdm,a.cpmc,a.cpcd with rollup
having grouping(a.cpcd)=0 or grouping(a.cpmc)=1
go

--删除测试
drop table CPJBZL,KC

/*--测试结果

产品代码 产品规格 库存数量
---------- ----------------------------------------- -----------
001 A-0.1 600
小计: 600
002 A-0.2 900
小计: 900
003 B-0.1 2100
小计: 2100
004 B-0.3 1900
小计: 1900
合计: 5500

（所影响的行数为 9 行）
--*/

[zjcxc]

不对,看错了分组

[zjcxc]

--测试

--测试数据
create table CPJBZL(cpdm varchar(10),cpmc varchar(10),cpcd decimal(10,1))
insert CPJBZL select '001','A',0.1
union all select '002','A',0.2
union all select '003','B',0.1
union all select '004','B',0.3
union all select '005','B',0.6
union all select '006','C',0.1

create table KC(scph int,cpdm varchar(10),kcsl int)
insert KC select 1 ,'001',100
union all select 2 ,'001',200
union all select 3 ,'001',300
union all select 4 ,'002',400
union all select 5 ,'002',500
union all select 6 ,'003',600
union all select 7 ,'003',700
union all select 8 ,'003',800
union all select 9 ,'004',900
union all select 10,'004',1000
go

--查询
select 产品代码=case
when grouping(a.cpmc)=1 then '合计:'
when grouping(a.cpdm)=1 then '小计:'
else a.cpdm end
,产品规格=case grouping(a.cpdm) when 1 then ''
else a.cpmc+'-'+cast(a.cpcd as varchar) end
,库存数量=sum(b.kcsl)
from CPJBZL a,KC b
where a.cpdm=b.cpdm
group by a.cpmc,a.cpdm,a.cpcd with rollup
having grouping(a.cpcd)=0 or grouping(a.cpdm)=1
go

--删除测试
drop table CPJBZL,KC

/*--测试结果

产品代码 产品规格 库存数量
---------- ----------------------------------------- -----------
001 A-0.1 600
002 A-0.2 900
小计: 1500
003 B-0.1 2100
004 B-0.3 1900
小计: 4000
合计: 5500

（所影响的行数为 7 行）
--*/