如何实现这个精确小计的显示功能

[xej]

本人用SQL server2000写了一个查询临时表数据的语句,其语句如下,
select productname , warehouse ,warehouse_storage ,productcode ,colocode ,colorname,batchno,unitname,
ztzl,gyzl,f1,f2,f3,f4,f5,f6,f7,f8 from #tmp_collect_now_C
union all
select productname+N'小计' as productcode,'' as warehouse ,'' as warehouse_storage ,
'' as productcode ,'' as colocode ,'' as colorname,'' as batchno,
'' as unitname,'' as ztzl,'' as gyzl,isnull(sum(f1),0) ,isnull(sum(f2),0)
from #tmp_collect_now_C group by productname
union all
select N'总计' as productname, '' as warehouse ,'' as warehouse_storage ,
'' as productcode ,'' as colocode ,'' as colorname,'' as batchno,
'' as unitname,'' as ztzl,'' as gyzl,isnull(sum(f1),0) ,isnull(sum(f2),0)
from #tmp_collect_now_C order by productname
执行后结果如下
1110 DD DSS 3 整件 整件 NULL 0 13 26
B2005 dd DDSS 3 整件 整件 NULL 0 1012 2024
2 DDD F152776re 3 整件 整件 NULL 0 747 1494
2 DDD小计 NULL NULL 747 1494
1110 DD小计 NULL NULL 13 26
B2005 dd小计 NULL NULL 1012 2024
1 sdds2222 dddd 3 整件 整件 NULL 0 73 146
1 sdds2222小计 NULL NULL 73 146
总计 NULL NULL 1845 3690

而本人想实现精确的查询结果,即是小计排在每个品种之下,例如：1110 dd品种显示完后,显示1110 dd小计,请教各位如何修改这段语句实现这个精确小计的显示功能。

[子陌红尘]

try：
-----------------------------------------------------------------------------------------------------------------------
select
productname=(case
when productname is null
then '合计'
when productname is not null and warehouse is null
then productname+'小计'
else productname
end),
warehouse,
warehouse_storage,
productcode,
colocode,
colorname,
batchno,
unitname,
ztzl,
gyzl,
f1=sum(f1),
f2=sum(f2)
from
#tmp_collect_now_C
group by
productname,warehouse,warehouse_storage,
productcode ,colocode ,colorname,batchno,
unitname,ztzl,gyzl
having
grouping(gyzl)=0 or grouping(warehouse)=1

[olony]

其实或许并不需要你对"合计"进行什么特别的处理

请查看ROLLUP()这个函数的使用