日期:2014-05-18  浏览次数:20686 次

超难的SQL算法,做梦都在想怎么办?拜师!!!!
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 T
select 'A','A1',10 union all
select 'A','A2',30 union all
select 'A','A3',60 union all
select 'SUM',null,'100' union all
select 'B','B1',20 union all
select 'B','B2',80 union all
select 'SUM',null,'100' union all
select 'C','C1',40 union all
select 'C','C2',60 union all
select 'SUM',null,'100' union all
select 'D','D1',30 union all
select 'D','D2',70 union all
select 'SUM',null,'100'


GO

Create function dbo.test(@a numeric(18,2),@b numeric(18,2),@c numeric(18,2),@d numeric(18,2))
returns  numeric(18,2)
as
begin
 declare @re numeric(18,2)
 select @re=min(num)
 from ( 
         select @a as num
         union 
         select @b
         union 
         select @c
         union 
         select @d
            ) A

return @re
end

GO
Create Proc  dbo.get_test 
AS

select  id=identity(int,1,1), Item,Amount, Amount as leave
into #A
from T where ItemType='A' order by Item

select  id=identity(int,1,1), Item,Amount, Amount  as leave
into #B
from T where ItemType='B' order by Item

select  id=identity(int,1,1), Item,Amount, Amount  as leave
into #C
from T where ItemType='C' order by Item

select  id=identity(int,1,1), Item,Amount, Amount as leave
into #D
from T where ItemType='D' order by Item

declare @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=0

while @active<@sum
begin
    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 leave=leave-@min  where id=@ida
    Update #B set leave=leave-@min  where id=@idb
    Update #C set leave=leave-@min where id=@idc
    Update #D set leave=leave-@min where id=@idd

    select @active=sum(Amount) from @table
end

select * from @table

drop table #A,#B,#C,#D

GO

--驗證結果
Exec    get_test
/*
A    B    C    D    amount               
---- ---- ---- ---- -------------------- 
A1   B1   C1   D1   10.00
A2   B1   C1   D1   10.00
A2   B2   C1   D1   10.00
A2   B2   C1   D2   10.00
A3   B2   C2   D2   60.00
*/

GO
drop table T

drop function dbo.test

drop 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 sele