多字段统计并加上合计的取值有关问题！（）

多字段统计并加上合计的取值问题！！！！！！（在线等）
 
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----fthreename----fthreecode----fqty-----fseqty 
1241----www---------eee---------q111---------20010101-----22.4-----43.09 
3345----www---------eee---------q111---------20010102-----44-------31.99 
3166----www---------eee---------d222--------20010103-----21-------66 
  小计-----------------------------------107.72---186.39 
3211----www---------dsdw--------ww1111--------30010101-----32.32----33.3 
  小计------------------------------------32.32----33.3 
  合计------------------------------------------------140.04---219.69 
4677----aaa---------ooo---------s11111--------40010101------321------434 
4696----aaa---------ooo---------s22222--------40010102------434------211 
  小计-------------------------------------755------645 
5465----aaa---------rrr---------g11111--------50010101-------112------343 
  合计 -------------------------------------------------867------988 
也就是按照fonename ftwoname分组算出合计，同时要获得fthreename,fthreecode三级名称和代码。
下面的可以实现fonename ftwoname的分组合计，不能获取到fthreename,fthreecode
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
怎么样才能取到fthreename,fthreecode的值呢？

[解决办法]

SQL code

 
 楼上的思路真的不错。学习了。。 
  
 create table b(fid int,fonename varchar(50),ftwoname varchar(50),fqty numeric(8,2),fseqty numeric(8,2)) 
 insert into b select 1241,'www','eee',22.4,  43.09   
 insert into b select 3345  ,  'www'     ,  'eee'   ,   32,    44   
 insert into b select 3166 ,   'www'  ,   'eee'   ,   21   ,   66   
 insert into b select 3211  ,  'www'  ,   'dsdw' ,  32.32  , 33.3   
 insert into b select 4677  ,  'aaa'   ,    'ooo' ,   321   ,  434   
 insert into b select 4696   ,   'aaa'  ,     'ooo'  ,  434  , 211   
 insert into b select 5465   ,   'aaa'  ,     'rrr' ,   112   , 3432  
  
  
 select  
   fid, 
   [fonename1]=case when fid='' and fonename>'' then '小计' when  fid=0 then ftwoname else fonename end ,   
   [ftwoname1]=case when fid=''  then '' else ftwoname end,   
   fqty, 
   fseqty 
 from  
 (select  *  from   b 
 union all  
 select '',fonename,ftwoname,sum(fqty),sum(fseqty) from b  group by fonename,ftwoname  
 
 union all  
 select '','','合计'+fonename as ftwoname,sum(fqty),sum(fseqty) from b group by  fonename 
 )t 
 order by fonename desc,ftwoname desc 
 

[解决办法]
CREATE TABLE tOne
(
fid varchar(20),
fonename varchar(20),
ftwoname varchar(20),
fqty decimal(10,2),
fseqty decimal(10,2)
)

INSERT INTO tOne
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,32.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

CREATE TABLE tTwo
(
tid VARCHAR(20),
fthreename VARCHAR(20),
fthreecode VARCHAR(20)
)

INSERT INTO tTwo
SELECT '1241','q111','20010101' UNION ALL
SELECT '3345','q111','20010102' UNION ALL
SELECT '3166','d222','20010103' UNION ALL
SELECT '3211','ww111','30010101' UNION ALL
SELECT '4677','s1111','40010101' UNION ALL
SELECT '4696','s2222','40010102' UNION ALL
SELECT '5465','g1111','50010101' 

SELECT fid,fonename,ftwoname,fthreename,fthreecode,fqty,fseqty FROM
(
SELECT *,c1=0,c2=fonename,c3=0,c4 = LEFT(fthreecode,6), c5 = 0 FROM
(
SELECT fid,fonename,ftwoname,fthreename,fthreecode,fqty,fseqty FROM tOne A INNER JOIN tTwo B ON A.fid = B.tid
) A
UNION ALL
SELECT '','','小计','','',SUM(fqty),SUM(fseqty),c1=0,c2=fonename,c3=0,c4 = LEFT(fthreecode,6), c5 = 1 FROM 
(
SELECT fid,fonename,ftwoname,fthreename,fthreecode,fqty,fseqty FROM tOne A INNER JOIN tTwo B ON A.fid = B.tid
) B GROUP BY fonename,LEFT(fthreecode,6)
UNION ALL
SELECT '','合计','','','',SUM(fqty),SUM(fseqty),c1=0,c2=fonename,c3=1,c4 = '', c5 = 1 FROM
(
SELECT fid,fonename,ftwoname,fthreename,fthreecode,fqty,fseqty FROM tOne A INNER JOIN tTwo B ON A.fid = B.tid
) A GROUP BY fonename
UNION ALL
SELECT '总计','','','','',SUM(fqty),SUM(fseqty),c1=1,c2='',c3=1,c4 = '', c5 = 1 FROM
(
SELECT fid,fonename,ftwoname,fthreename,fthreecode,fqty,fseqty FROM tOne A INNER JOIN tTwo B ON A.fid = B.tid
) A 
) A
ORDER BY c1,c2 DESC,c3,c4,c5

DROP TABLE tTwo
DROP TABLE tOne


fid fonename ftwoname fthreename fthreecode fqty fseqty
-------------------- -------------------- -------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- 
1241 www eee q111 20010101 22.40 43.09
3345 www eee q111 20010102 32.00 44.00
3166 www eee d222 20010103 21.00 66.00
小计 75.40 153.09
3211 www dsdw ww111 30010101 32.32 32.30
小计 32.32 32.30
合计 107.72 185.39
4677 aaa ooo s1111 40010101 321.00 434.00

4696 aaa ooo s2222 40010102 434.00 211.00
小计 755.00 645.00
5465 aaa rrr g1111 50010101 112.00 343.00
小计 112.00 343.00
合计 867.00 988.00
总计 974.72 1173.39

（所影响的行数为 14 行）

查看更多 下一篇