超难的SQL算法,做梦都在想怎么办?拜师!!!!
TABEL 1
类别TYPE项目ITEM金额AMOUNT
AA110
AA230
AA360
SUM100
BB120
BB280
SUM100
CC140
CC260
SUM100
DD130
DD270
SUM100
TABLE 1 每个类别的汇总是相等的,都是100.每个类别所属项目数量不限,金额可能是小数.
求:
TABLE 2
ABCD金额AMOUNT
A1B1C1D110
A2B1C1D110
A2B2C1D110
A2B2C1D210
A3B2C2D260
SUM100
TABEL 2中的ABCD四列,共用金额AMOUNT列,金额AMOUNT列汇总为任何一个类别的汇总100
类别只会有ABCD四个,不会增加.
各位兄弟姐妹救命啊!!!!!!!
如果有谁可以解出来,给全部的分,拜他(她)为师!!!!!!!!!!!
[解决办法]
--想到就随性， 随手写的，没考虑效率等，应该有更好的算法 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 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 @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
[解决办法]
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
[解决办法]
DECLARE @TB TABLE([Type] VARCHAR(1), [Item] VARCHAR(2), [Amount] INT, ID INT IDENTITY(1,1))INSERT @TB SELECT 'A', 'A1', 10 UNION ALL SELECT 'A', 'A2', 30 UNION ALL SELECT 'A', 'A3', 60 UNION ALL SELECT 'B', 'B1', 20 UNION ALL SELECT 'B', 'B2', 80 UNION ALL SELECT 'C', 'C1', 40 UNION ALL SELECT 'C', 'C2', 60 UNION ALL SELECT 'D', 'D1', 30 UNION ALL SELECT 'D', 'D2', 70DECLARE @MAXVAL INT, @MINVAL INT, @RC INT, @TEMP INT, @TOTAL INT, @SEQ INTDECLARE @T TABLE(A VARCHAR(3),B VARCHAR(2),C VARCHAR(2),D VARCHAR(2),[Amount] INT,ID INT,GRP INT)SET @MAXVAL=(SELECT MIN ([Amount]) FROM (SELECT MAX([Amount]) AS [Amount] FROM @TB GROUP BY [Type]) T)SET @MINVAL=(SELECT MIN ([Amount]) FROM (SELECT MIN([Amount]) AS [Amount] FROM @TB GROUP BY [Type]) T)SET @TOTAL=(SELECT TOP 1 SUM([Amount]) FROM @TB GROUP BY [Type])SET @SEQ=1SET @RC=(SELECT (@MAXVAL-@MINVAL)/@MINVAL)SET @TEMP=@RCWHILE @RC>1BEGIN INSERT @T SELECT CASE WHEN [Type]='A' THEN [Item] ELSE '' END AS A, CASE WHEN [Type]='B' THEN [Item] ELSE '' END AS B, CASE WHEN [Type]='C' THEN [Item] ELSE '' END AS C, CASE WHEN [Type]='D' THEN [Item] ELSE '' END AS D, @MINVAL, B.ID, @SEQ FROM ( SELECT MIN(T2.ID) AS ID FROM (SELECT ID,SUM([Amount]) AS [Amount] FROM @T GROUP BY ID) AS T1 RIGHT JOIN @TB AS T2 ON T1.ID=T2.ID WHERE T2.[Amount]-ISNULL(T1.[Amount],0)>0 GROUP BY [Type]) AS A JOIN @TB AS B ON A.ID=B.ID SET @RC=@RC-1 SET @SEQ=@SEQ+1ENDINSERT @TSELECT MAX(CASE WHEN [Type]='A' THEN [Item] ELSE '' END) AS A, MAX(CASE WHEN [Type]='B' THEN [Item] ELSE '' END) AS B, MAX(CASE WHEN [Type]='C' THEN [Item] ELSE '' END) AS C, MAX(CASE WHEN [Type]='D' THEN [Item] ELSE '' END) AS D, @MAXVAL, 0, @TEMPFROM @TB GROUP BY [Type]UNION ALLSELECT 'SUM','','','',@TOTAL,0,@TEMP+1SELECT MAX(A) AS A,MAX(B) AS B,MAX(C) AS C,MAX(D) AS D,MAX([Amount]) AS [Amount]FROM @TGROUP BY GRPORDER BY GRP/*A B C D Amount---- ---- ---- ---- -----------A1 B1 C1 D1 10A2 B1 C1 D1 10A2 B2 C1 D1 10A2 B2 C1 D2 10A3 B2 C2 D2 60SUM 100*/
[解决办法]
忘记格式了
create table #tmpTestA(fType varchar(10),fItem varchar(10),fAccount decimal(10,2))create table #tmpTestB(A varchar(10),B varchar(10),C varchar(10),D varchar(10),Amount decimal(10,2))declare @itema varchar(10),@itemb varchar(10),@itemc varchar(10),@itemd varchar(10),@gongyueshu decimal(10,2)--公约数,随便几，越小越好，1最好insert into #tmpTestAselect 'A','A1',10 union allselect 'A','A2',30 union allselect 'A','A3',60 union allselect 'B','B1',20 union allselect 'B','B2',80 union allselect 'C','C1',40 union allselect 'C','C2',60 union allselect 'D','D1',30 union allselect 'D','D2',70 --select * from #tmpTestAset @gongyueshu=1while exists(select top 1 fType from #tmpTestA)Begin select @itema=min(fitem) from #tmpTestA where ftype='A' select @itemb=min(fitem) from #tmpTestA where ftype='B' select @itemc=min(fitem) from #tmpTestA where ftype='C' select @itemd=min(fitem) from #tmpTestA where ftype='D' insert into #tmpTestB select @itema,@itemb,@itemc,@itemd,@gongyueshu update #tmpTestA set fAccount=fAccount-@gongyueshu where fitem in(@itema,@itemb,@itemc,@itemd) delete from #tmpTestA where fAccount=0 select @itema=null,@itemb=null,@itemc=null,@itemd=nullEndselect A,B,C,D,sum(Amount) as Amount from #tmpTestBgroup by A,B,C,Ddrop table #tmpTestAdrop table #tmpTestB
[解决办法]
DECLARE @TB TABLE([Type] VARCHAR(1), [Item] VARCHAR(2), [Amount] INT, ID INT IDENTITY(1,1))
INSERT @TB
SELECT 'A', 'A1', 10 UNION ALL
SELECT 'A', 'A2', 30 UNION ALL
SELECT 'A', 'A3', 60 UNION ALL
SELECT 'B', 'B1', 20 UNION ALL
SELECT 'B', 'B2', 80 UNION ALL
SELECT 'C', 'C1', 40 UNION ALL
SELECT 'C', 'C2', 60 UNION ALL
SELECT 'D', 'D1', 30 UNION ALL
SELECT 'D', 'D2', 70
DECLARE @MAXVAL INT, @MINVAL INT, @RC INT, @TEMP INT, @TOTAL INT, @SEQ INT
DECLARE @T TABLE(A VARCHAR(3),B VARCHAR(2),C VARCHAR(2),D VARCHAR(2),[Amount] INT,ID INT,GRP INT)
SET @MAXVAL=(SELECT MIN ([Amount]) FROM (SELECT MAX([Amount]) AS [Amount] FROM @TB GROUP BY [Type]) T)
SET @MINVAL=(SELECT MIN ([Amount]) FROM (SELECT MIN([Amount]) AS [Amount] FROM @TB GROUP BY [Type]) T)
SET @TOTAL=(SELECT TOP 1 SUM([Amount]) FROM @TB GROUP BY [Type])
SET @SEQ=1
SET @RC=(SELECT (@MAXVAL-@MINVAL)/@MINVAL)
SET @TEMP=@RC
WHILE @RC>1
BEGIN
INSERT @T
SELECT CASE WHEN [Type]='A' THEN [Item] ELSE '' END AS A,
CASE WHEN [Type]='B' THEN [Item] ELSE '' END AS B,
CASE WHEN [Type]='C' THEN [Item] ELSE '' END AS C,
CASE WHEN [Type]='D' THEN [Item] ELSE '' END AS D,
@MINVAL,
B.ID,
@SEQ
FROM (
SELECT MIN(T2.ID) AS ID
FROM (SELECT ID,SUM([Amount]) AS [Amount] FROM @T GROUP BY ID) AS T1 RIGHT JOIN @TB AS T2
ON T1.ID=T2.ID
WHERE T2.[Amount]-ISNULL(T1.[Amount],0)>0
GROUP BY [Type]) AS A
JOIN @TB AS B ON A.ID=B.ID
SET @RC=@RC-1
SET @SEQ=@SEQ+1
END
INSERT @T
SELECT MAX(CASE WHEN [Type]='A' THEN [Item] ELSE '' END) AS A,
MAX(CASE WHEN [Type]='B' THEN [Item] ELSE '' END) AS B,
MAX(CASE WHEN [Type]='C' THEN [Item] ELSE '' END) AS C,
MAX(CASE WHEN [Type]='D' THEN [Item] ELSE '' END) AS D,
@MAXVAL,
0,
@TEMP
FROM @TB GROUP BY [Type]
UNION ALL
SELECT 'SUM','','','',@TOTAL,0,@TEMP+1
SELECT MAX(A) AS A,MAX(B) AS B,MAX(C) AS C,MAX(D) AS D,MAX([Amount]) AS [Amount]
FROM @T
GROUP BY GRP
ORDER BY GRP
/*
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
*/
