TABEL 1
类别TYPE 项目ITEM 金额AMOUNT
A A1 10
A A2 30
A A3 60
SUM 100
B B1 20
B B2 80
SUM 100
C C1 40
C C2 60
SUM 100
D D1 30
D D2 70
SUM 100
TABLE 1 每个类别的汇总是相等的,都是100.每个类别所属项目数量不限,金额可能是小数.
求:
TABLE 2
A B C D 金额AMOUNT
A1 B1 C1 D1 10
A2 B1 C1 D1 10
A2 B2 C1 D1 10
A2 B2 C1 D2 10
A3 B2 C2 D2 60
SUM 100
TABEL 2中的ABCD四列,共用金额AMOUNT列,金额AMOUNT列汇总为任何一个类别的汇总100
类别只会有ABCD四个,不会增加.
各位兄弟姐妹救命啊!!!!!!!
如果有谁可以解出来,给全部的分,拜他(她)为师!!!!!!!!!!!
------解决方案--------------------
- SQL code
--想到就随性， 随手写的，没考虑效率等，应该有更好的算法 create table T (ItemType varchar(3),Item varchar(3),Amount numeric(18,2))insert into Tselect 'A','A1',10 union allselect 'A','A2',30 union allselect 'A','A3',60 union allselect 'SUM',null,'100' union allselect 'B','B1',20 union allselect 'B','B2',80 union allselect 'SUM',null,'100' union allselect 'C','C1',40 union allselect 'C','C2',60 union allselect 'SUM',null,'100' union allselect 'D','D1',30 union allselect 'D','D2',70 union allselect 'SUM',null,'100'GOCreate function dbo.test(@a numeric(18,2),@b numeric(18,2),@c numeric(18,2),@d numeric(18,2))returns numeric(18,2)asbegin declare @re numeric(18,2) select @re=min(num) from ( select @a as num union select @b union select @c union select @d ) Areturn @reendGOCreate Proc dbo.get_test ASselect id=identity(int,1,1), Item,Amount, Amount as leaveinto #Afrom T where ItemType='A' order by Itemselect id=identity(int,1,1), Item,Amount, Amount as leaveinto #Bfrom T where ItemType='B' order by Itemselect id=identity(int,1,1), Item,Amount, Amount as leaveinto #Cfrom T where ItemType='C' order by Itemselect id=identity(int,1,1), Item,Amount, Amount as leaveinto #Dfrom T where ItemType='D' order by Itemdeclare @sum numeric(18,2)set @sum=(select top 1 Amount from T where ItemType='SUM')declare @table table( A varchar(03), B varchar(03), C varchar(03), D varchar(03), amount numeric(18,2))declare @ida int,@idb int,@idc int,@idd int , @min numeric(18,2)declare @itema varchar(03), @itemb varchar(03), @itemc varchar(03), @itemd varchar(03)declare @a numeric(18,2),@b numeric(18,2),@c numeric(18,2),@d numeric(18,2),@active numeric(18,2)set @active=0while @active<@sumbegin select @ida=id,@itema=item,@a=leave from #A where id=(select min(id) from #A where leave>0) select @idb=id,@itemb=item,@b=leave from #B where id=(select min(id) from #B where leave>0) select @idc=id,@itemc=item,@c=leave from #C where id=(select min(id) from #C where leave>0) select @idd=id,@itemd=item,@d=leave from #D where id=(select min(id) from #D where leave>0) select @min= dbo.test(@a,@b,@c,@d) Insert into @table select @itema,@itemb,@itemc,@itemd,@min Update #A set [email protected] where [email protected] Update #B set [email protected] where [email protected] Update #C set [email protected] where [email protected] Update #D set [email protected] where [email protected] select @active=sum(Amount) from @tableendselect * from @tabledrop table #A,#B,#C,#DGO--驗證結果Exec get_test/*A B C D amount ---- ---- ---- ---- -------------------- A1 B1 C1 D1 10.00A2 B1 C1 D1 10.00A2 B2 C1 D1 10.00A2 B2 C1 D2 10.00A3 B2 C2 D2 60.00*/GOdrop table Tdrop function dbo.testdrop proc get_test
------解决方案--------------------
- SQL code
create table t1(TYPE varchar(10),ITEM varchar(10),AMOUNT money)insert into T1 select 'A','A1',10 insert into T1 select 'A','A2',30 insert into T1 select 'A','A3',60 insert into T1 select 'B','B1',20 insert into T1 select 'B','B2',80 insert into T1 select 'C','C1',40 insert into T1 select 'C','C2',60 insert into T1 select 'D','D1',30 insert into T1 select 'D','D2',70 godeclare @t1 table(TYPE varchar(10),ITEM varchar(10),AMOUNT money,AMOUNT1 money)declare @t2 table(A varchar(10),B varchar(10),C varchar(10),D varchar(10),AMOUNT money)insert into @t1select a.*,(select sum(AMOUNT) from t1 where TYPE=a.TYPE and ITEM<=a.ITEM) as AMOUNT1 from t1 awhile @@rowcount>0begin insert into @t2 select top 1 (select top 1 ITEM from @t1 where TYPE='A' and AMOUNT1>b.AMOUNT order by AMOUNT1), (select top 1 ITEM from @t1 where TYPE='B' and AMOUNT1>b.AMOUNT order by AMOUNT1), (select top 1 ITEM from @t1 where TYPE='C' and AMOUNT1>b.AMOUNT order by AMOUNT1), (select top 1 ITEM from @t1 where TYPE='D' and AMOUNT1>b.AMOUNT order by AMOUNT1), a.AMOUNT1-b.AMOUNT from @t1 a,(select isnull(sum(AMOUNT),0) as AMOUNT from @t2) b where a.AMOUNT1>b.AMOUNT order by a.AMOUNT1endselect * from @t2/*A B C D AMOUNT ---------- ---------- ---------- ---------- --------------------- A1 B1 C1 D1 10.0000A2 B1 C1 D1 10.0000A2 B2 C1 D1 10.0000A2 B2 C1 D2 10.0000A3 B2 C2 D2 60.0000*/godrop table T1