关于多字段统计并加上合计的问题！！！！！！（在线等）
表
fid fonename ftwoname fqty fseqty
1241 www eee 22.4 43.09
3345 www eee 32 44
3166 www eee 21 66
3211 www dsdw 32.32 33.3
4677 aaa ooo 321 434
4696 aaa ooo 434 211
5465 aaa rrr 112 3432
能不能将fonename 和 ftwoname统计结果插入到该表中呢？要实现的结果表如下：
fid fonename ftwoname fqty fseqty
1241 www eee 22.4 43.09
3345 www eee 32 44
3166 www eee 21 66
小计 107.72 186.39
3211 www dsdw 32.32 33.3
小计 32.32 33.3
合计 140.04 219.69
4677 aaa ooo 321 434
4696 aaa ooo 434 211
小计 755 645
5465 aaa rrr 112 343
合计 867 988
怎么实现这个亚？
[解决办法]
create table #表(fid int,fonename varchar(20),ftwoname varchar(20),fqty numeric(8,2),fseqty numeric(8,2))
insert #表 select
1241,'www','eee', 22.4, 43.09 insert #表 select
3345,'www','eee', 32, 44 insert #表 select
3166,'www','eee', 21, 66 insert #表 select
3211,'www','dsdw',32.32,33.3 insert #表 select
4677,'aaa','ooo', 321, 434 insert #表 select
4696,'aaa','ooo', 434, 211 insert #表 select
5465,'aaa','rrr', 112, 3432
select fid,(case when ftwoname is null and fonename is not null then '' else isnull(fonename,'合计')end) fonename,
(case when fonename is null then '' else isnull(ftwoname,'小计') end)ftwoname,sum(fqty) fqty,sum(fseqty) fseqty
from #表 group by fonename,ftwoname,fid with rollup
-----------------
4677aaaooo321.00434.00
4696aaaooo434.00211.00
NULLaaaooo755.00645.00
5465aaarrr112.003432.00
NULLaaarrr112.003432.00
NULL小计867.004077.00
3211wwwdsdw32.3233.30
NULLwwwdsdw32.3233.30
1241wwweee22.4043.09
3166wwweee21.0066.00
3345wwweee32.0044.00
NULLwwweee75.40153.09
NULL小计107.72186.39
NULL合计974.724263.39
[解决办法]
declare @a table(fid varchar(20), fonename varchar(20), ftwoname varchar(20), fqty decimal(20,2), fseqty decimal(20,2))
insert @a select '1241', 'www', 'eee', 22.4, 43.09
union all select '3345' ,'www' ,'eee', 32, 44
union all select '3166' ,'www' ,'eee', 21, 66
union all select '3211' ,'www' ,'dsdw', 32.32, 33.3
union all select '4677' ,'aaa' ,'ooo', 321, 434
union all select '4696' ,'aaa' ,'ooo', 434, 211
union all select '5465' ,'aaa' ,'rrr', 112, 343
select case when fonename is null and ftwoname is null then '总计' else isnull(fid,'') end fid,
case when fonename is null and ftwoname is null then ''
when fid is null and ftwoname is null then '合计'
when fid is null then '' else fonename end fonename,
case when fid is null and ftwoname is null then ''
when fid is null then '小计' else ftwoname end ftwoname,sum(fqty)fqty,sum(fseqty)fseqty
from @a
group by fonename,ftwoname,fid with rollup
