新手求教一个求和的问题，希望各位帮帮忙
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER FUNCTION [dbo].[FW_XSTTDB](@YF CHAR(4)) RETURNS @tempxsysb TABLE(kkers char(10),eeggd CHAR(30),fsd char(30),sydyszk numeric(18,2), A1 NUMERIC(18,2),A4 NUMERIC(18,2),B1 NUMERIC(18,2),B4 NUMERIC(18,2), C1 NUMERIC(18,2),C4 NUMERIC(18,2),D1 NUMERIC(18,2),D4 NUMERIC(18,2), E1 NUMERIC(18,2),E4 NUMERIC(18,2),F1 NUMERIC(18,2),F4 NUMERIC(18,2), G1 NUMERIC(18,2),G4 NUMERIC(18,2),H1 NUMERIC(18,2),H4 NUMERIC(18,2), I1 NUMERIC(18,2),I4 NUMERIC(18,2),J1 NUMERIC(18,2),J4 NUMERIC(18,2), K1 NUMERIC(18,2),K4 NUMERIC(18,2),L1 NUMERIC(18,2),L4 NUMERIC(18,2), M1 NUMERIC(18,2),M4 NUMERIC(18,2)) AS BEGIN INSERT @tempxsysb SELECT MA001,MA002,MV002,(SELECT ISNULL(SUM(LB013),0) FROM ACRLB WHERE LB005=MA001 AND LB004<@YF+'0101' ) AS sydyszk, (SELECT ISNULL(SUM(TC029+TC030),0) FROM COPTC WHERE TC004=MA001 AND LEFT(TC003,6)=@YF+'01') AS A1, (SELECT ISNULL(SUM(LB013),0) FROM ACRLB WHERE LB005=MA001 AND LEFT(LB003,6)=@YF+'01' AND LB011=-1) AS A4, (SELECT ISNULL(SUM(TC029+TC030),0) FROM COPTC WHERE TC004=MA001 AND LEFT(TC003,6)=@YF+'02') AS B1, (SELECT ISNULL(SUM(LB013),0) FROM ACRLB WHERE LB005=MA001 AND LEFT(LB003,6)=@YF+'02' AND LB011=-1) AS B4, (SELECT ISNULL(SUM(TC029+TC030),0) FROM COPTC WHERE TC004=MA001 AND LEFT(TC003,6)=@YF+'03') AS C1, (SELECT ISNULL(SUM(LB013),0) FROM ACRLB WHERE LB005=MA001 AND LEFT(LB003,6)=@YF+'03' AND LB011=-1) AS C4, (SELECT ISNULL(SUM(TC029+TC030),0) FROM COPTC WHERE TC004=MA001 AND LEFT(TC003,6)=@YF+'04') AS D1, (SELECT ISNULL(SUM(LB013),0) FROM ACRLB WHERE LB005=MA001 AND LEFT(LB003,6)=@YF+'04' AND LB011=-1) AS D4, (SELECT ISNULL(SUM(TC029+TC030),0) FROM COPTC WHERE TC004=MA001 AND LEFT(TC003,6)=@YF+'05') AS E1, (SELECT ISNULL(SUM(LB013),0) FROM ACRLB WHERE LB005=MA001 AND LEFT(LB003,6)=@YF+'05' AND LB011=-1) AS E4, (SELECT ISNULL(SUM(TC029+TC030),0) FROM COPTC WHERE TC004=MA001 AND LEFT(TC003,6)=@YF+'06') AS F1, (SELECT ISNULL(SUM(LB013),0) FROM ACRLB WHERE LB005=MA001 AND LEFT(LB003,6)=@YF+'06' AND LB011=-1) AS F4, (SELECT ISNULL(SUM(TC029+TC030),0) FROM COPTC WHERE TC004=MA001 AND LEFT(TC003,6)=@YF+'07') AS G1, (SELECT ISNULL(SUM(LB013),0) FROM ACRLB WHERE LB005=MA001 AND LEFT(LB003,6)=@YF+'07' AND LB011=-1) AS G4, (SELECT ISNULL(SUM(TC029+TC030),0) FROM COPTC WHERE TC004=MA001 AND LEFT(TC003,6)=@YF+'08') AS H1,, (SELECT ISNULL(SUM(LB013),0) FROM ACRLB WHERE LB005=MA001 AND LEFT(LB003,6)=@YF+'08' AND LB011=-1) AS H4, (SELECT ISNULL(SUM(TC029+TC030),0) FROM COPTC WHERE TC004=MA001 AND LEFT(TC003,6)=@YF+'09') AS I1, (SELECT ISNULL(SUM(LB013),0) FROM ACRLB WHERE LB005=MA001 AND LEFT(LB003,6)=@YF+'09' AND LB011=-1) AS I4, (SELECT ISNULL(SUM(TC029+TC030),0) FROM COPTC WHERE TC004=MA001 AND LEFT(TC003,6)=@YF+'10') AS J1, (SELECT ISNULL(SUM(LB013),0) FROM ACRLB WHERE LB005=MA001 AND LEFT(LB003,6)=@YF+'10' AND LB011=-1) AS J4, (SELECT ISNULL(SUM(TC029+TC030),0) FROM COPTC WHERE TC004=MA001 AND LEFT(TC003,6)=@YF+'11') AS K1, (SELECT ISNULL(SUM(LB013),0) FROM ACRLB WHERE LB005=MA001 AND LEFT(LB003,6)=@YF+'11' AND LB011=-1) AS K4, (SELECT ISNULL(SUM(TC029+TC030),0) FROM COPTC WHERE TC004=MA001 AND LEFT(TC003,6)=@YF+'12') AS L1, (SELECT ISNULL(SUM(LB013),0) FROM ACRLB WHERE LB005=MA001 AND LEFT(LB003,6)=@YF+'12' AND LB011=-1) AS L4 FROM COPMA LEFT JOIN CMSMV ON MA016=MV001 return END 现在想让M1的值=A1+B1……+L1的值 ,M4=A4+B4……+L4的值。。。有人能跟我讲讲不。
INSERT @tempxsysbSELECT 8,m1=A1+B1....L1,m2=FROM( SELECT MA001,MA002,MV002,(SELECT ISNULL(SUM(LB013),0) FROM ACRLB WHERE LB005=MA001 AND LB004<@YF+'0101' ) AS sydyszk, (SELECT ISNULL(SUM(TC029+TC030),0) FROM COPTC WHERE TC004=MA001 AND LEFT(TC003,6)=@YF+'01') AS A1, (SELECT ISNULL(SUM(LB013),0) FROM ACRLB WHERE LB005=MA001 AND LEFT(LB003,6)=@YF+'01' AND LB011=-1) AS A4, (SELECT ISNULL(SUM(TC029+TC030),0) FROM COPTC WHERE TC004=MA001 AND LEFT(TC003,6)=@YF+'02') AS B1, (SELECT ISNULL(SUM(LB013),0) FROM ACRLB WHERE LB005=MA001 AND LEFT(LB003,6)=@YF+'02' AND LB011=-1) AS B4, (SELECT ISNULL(SUM(TC029+TC030),0) FROM COPTC WHERE TC004=MA001 AND LEFT(TC003,6)=@YF+'03') AS C1, (SELECT ISNULL(SUM(LB013),0) FROM ACRLB WHERE LB005=MA001 AND LEFT(LB003,6)=@YF+'03' AND LB011=-1) AS C4, (SELECT ISNULL(SUM(TC029+TC030),0) FROM COPTC WHERE TC004=MA001 AND LEFT(TC003,6)=@YF+'04') AS D1, (SELECT ISNULL(SUM(LB013),0) FROM ACRLB WHERE LB005=MA001 AND LEFT(LB003,6)=@YF+'04' AND LB011=-1) AS D4, (SELECT ISNULL(SUM(TC029+TC030),0) FROM COPTC WHERE TC004=MA001 AND LEFT(TC003,6)=@YF+'05') AS E1, (SELECT ISNULL(SUM(LB013),0) FROM ACRLB WHERE LB005=MA001 AND LEFT(LB003,6)=@YF+'05' AND LB011=-1) AS E4, (SELECT ISNULL(SUM(TC029+TC030),0) FROM COPTC WHERE TC004=MA001 AND LEFT(TC003,6)=@YF+'06') AS F1, (SELECT ISNULL(SUM(LB013),0) FROM ACRLB WHERE LB005=MA001 AND LEFT(LB003,6)=@YF+'06' AND LB011=-1) AS F4, (SELECT ISNULL(SUM(TC029+TC030),0) FROM COPTC WHERE TC004=MA001 AND LEFT(TC003,6)=@YF+'07') AS G1, (SELECT ISNULL(SUM(LB013),0) FROM ACRLB WHERE LB005=MA001 AND LEFT(LB003,6)=@YF+'07' AND LB011=-1) AS G4, (SELECT ISNULL(SUM(TC029+TC030),0) FROM COPTC WHERE TC004=MA001 AND LEFT(TC003,6)=@YF+'08') AS H1,, (SELECT ISNULL(SUM(LB013),0) FROM ACRLB WHERE LB005=MA001 AND LEFT(LB003,6)=@YF+'08' AND LB011=-1) AS H4, (SELECT ISNULL(SUM(TC029+TC030),0) FROM COPTC WHERE TC004=MA001 AND LEFT(TC003,6)=@YF+'09') AS I1, (SELECT ISNULL(SUM(LB013),0) FROM ACRLB WHERE LB005=MA001 AND LEFT(LB003,6)=@YF+'09' AND LB011=-1) AS I4, (SELECT ISNULL(SUM(TC029+TC030),0) FROM COPTC WHERE TC004=MA001 AND LEFT(TC003,6)=@YF+'10') AS J1, (SELECT ISNULL(SUM(LB013),0) FROM ACRLB WHERE LB005=MA001 AND LEFT(LB003,6)=@YF+'10' AND LB011=-1) AS J4, (SELECT ISNULL(SUM(TC029+TC030),0) FROM COPTC WHERE TC004=MA001 AND LEFT(TC003,6)=@YF+'11') AS K1, (SELECT ISNULL(SUM(LB013),0) FROM ACRLB WHERE LB005=MA001 AND LEFT(LB003,6)=@YF+'11' AND LB011=-1) AS K4, (SELECT ISNULL(SUM(TC029+TC030),0) FROM COPTC WHERE TC004=MA001 AND LEFT(TC003,6)=@YF+'12') AS L1, (SELECT ISNULL(SUM(LB013),0) FROM ACRLB WHERE LB005=MA001 AND LEFT(LB003,6)=@YF+'12' AND LB011=-1) AS L4 FROM COPMA LEFT JOIN CMSMV ON MA016=MV001 ) t
[解决办法]
--TRYALTER FUNCTION [dbo].[FW_XSTTDB](@YF CHAR(4)) RETURNS @tempxsysb TABLE(kkers char(10),eeggd CHAR(30),fsd char(30),sydyszk numeric(18,2), A1 NUMERIC(18,2),A4 NUMERIC(18,2),B1 NUMERIC(18,2),B4 NUMERIC(18,2), C1 NUMERIC(18,2),C4 NUMERIC(18,2),D1 NUMERIC(18,2),D4 NUMERIC(18,2), E1 NUMERIC(18,2),E4 NUMERIC(18,2),F1 NUMERIC(18,2),F4 NUMERIC(18,2), G1 NUMERIC(18,2),G4 NUMERIC(18,2),H1 NUMERIC(18,2),H4 NUMERIC(18,2), I1 NUMERIC(18,2),I4 NUMERIC(18,2),J1 NUMERIC(18,2),J4 NUMERIC(18,2), K1 NUMERIC(18,2),K4 NUMERIC(18,2),L1 NUMERIC(18,2),L4 NUMERIC(18,2), M1 as (ISNULL(A1,0)+ISNULL(B1,0)+ISNULL(C1,0)+ISNULL(D1,0)+ISNULL(E1,0)+ ISNULL(F1,0)+ISNULL(G1,0)+ISNULL(H1,0)+ISNULL(I1,0)+ISNULL(J1,0)+ ISNULL(K1,0)+ISNULL(L1,0)), M4 as (ISNULL(A4,0)+ISNULL(B4,0)+ISNULL(C4,0)+ISNULL(D4,0)+ISNULL(E4,0)+ ISNULL(F4,0)+ISNULL(G4,0)+ISNULL(H4,0)+ISNULL(I4,0)+ISNULL(J4,0)+ ISNULL(K4,0)+ISNULL(L4,0)) ) AS BEGIN INSERT @tempxsysb(kkers,eeggd,fsd,sydyszk,A1,A4,B1,B4,C1,C4,D1,D4,E1,E4,F1,F4,G1,G4,H1,H4,I1,I4,J1,J4,K1,K4,L1,L4) SELECT MA001,MA002,MV002,(SELECT ISNULL(SUM(LB013),0) FROM ACRLB WHERE LB005=MA001 AND LB004<@YF+'0101' ) AS sydyszk, (SELECT ISNULL(SUM(TC029+TC030),0) FROM COPTC WHERE TC004=MA001 AND LEFT(TC003,6)=@YF+'01') AS A1, (SELECT ISNULL(SUM(LB013),0) FROM ACRLB WHERE LB005=MA001 AND LEFT(LB003,6)=@YF+'01' AND LB011=-1) AS A4, (SELECT ISNULL(SUM(TC029+TC030),0) FROM COPTC WHERE TC004=MA001 AND LEFT(TC003,6)=@YF+'02') AS B1, (SELECT ISNULL(SUM(LB013),0) FROM ACRLB WHERE LB005=MA001 AND LEFT(LB003,6)=@YF+'02' AND LB011=-1) AS B4, (SELECT ISNULL(SUM(TC029+TC030),0) FROM COPTC WHERE TC004=MA001 AND LEFT(TC003,6)=@YF+'03') AS C1, (SELECT ISNULL(SUM(LB013),0) FROM ACRLB WHERE LB005=MA001 AND LEFT(LB003,6)=@YF+'03' AND LB011=-1) AS C4, (SELECT ISNULL(SUM(TC029+TC030),0) FROM COPTC WHERE TC004=MA001 AND LEFT(TC003,6)=@YF+'04') AS D1, (SELECT ISNULL(SUM(LB013),0) FROM ACRLB WHERE LB005=MA001 AND LEFT(LB003,6)=@YF+'04' AND LB011=-1) AS D4, (SELECT ISNULL(SUM(TC029+TC030),0) FROM COPTC WHERE TC004=MA001 AND LEFT(TC003,6)=@YF+'05') AS E1, (SELECT ISNULL(SUM(LB013),0) FROM ACRLB WHERE LB005=MA001 AND LEFT(LB003,6)=@YF+'05' AND LB011=-1) AS E4, (SELECT ISNULL(SUM(TC029+TC030),0) FROM COPTC WHERE TC004=MA001 AND LEFT(TC003,6)=@YF+'06') AS F1, (SELECT ISNULL(SUM(LB013),0) FROM ACRLB WHERE LB005=MA001 AND LEFT(LB003,6)=@YF+'06' AND LB011=-1) AS F4, (SELECT ISNULL(SUM(TC029+TC030),0) FROM COPTC WHERE TC004=MA001 AND LEFT(TC003,6)=@YF+'07') AS G1, (SELECT ISNULL(SUM(LB013),0) FROM ACRLB WHERE LB005=MA001 AND LEFT(LB003,6)=@YF+'07' AND LB011=-1) AS G4, (SELECT ISNULL(SUM(TC029+TC030),0) FROM COPTC WHERE TC004=MA001 AND LEFT(TC003,6)=@YF+'08') AS H1, (SELECT ISNULL(SUM(LB013),0) FROM ACRLB WHERE LB005=MA001 AND LEFT(LB003,6)=@YF+'08' AND LB011=-1) AS H4, (SELECT ISNULL(SUM(TC029+TC030),0) FROM COPTC WHERE TC004=MA001 AND LEFT(TC003,6)=@YF+'09') AS I1, (SELECT ISNULL(SUM(LB013),0) FROM ACRLB WHERE LB005=MA001 AND LEFT(LB003,6)=@YF+'09' AND LB011=-1) AS I4, (SELECT ISNULL(SUM(TC029+TC030),0) FROM COPTC WHERE TC004=MA001 AND LEFT(TC003,6)=@YF+'10') AS J1, (SELECT ISNULL(SUM(LB013),0) FROM ACRLB WHERE LB005=MA001 AND LEFT(LB003,6)=@YF+'10' AND LB011=-1) AS J4, (SELECT ISNULL(SUM(TC029+TC030),0) FROM COPTC WHERE TC004=MA001 AND LEFT(TC003,6)=@YF+'11') AS K1, (SELECT ISNULL(SUM(LB013),0) FROM ACRLB WHERE LB005=MA001 AND LEFT(LB003,6)=@YF+'11' AND LB011=-1) AS K4, (SELECT ISNULL(SUM(TC029+TC030),0) FROM COPTC WHERE TC004=MA001 AND LEFT(TC003,6)=@YF+'12') AS L1, (SELECT ISNULL(SUM(LB013),0) FROM ACRLB WHERE LB005=MA001 AND LEFT(LB003,6)=@YF+'12' AND LB011=-1) AS L4 FROM COPMA LEFT JOIN CMSMV ON MA016=MV001 return END
[解决办法]
--试试这个ALTER FUNCTION [dbo].[FW_XSTTDB](@YF CHAR(4)) RETURNS @tempxsysb TABLE(kkers char(10),eeggd CHAR(30),fsd char(30),sydyszk numeric(18,2), A1 NUMERIC(18,2),A4 NUMERIC(18,2),B1 NUMERIC(18,2),B4 NUMERIC(18,2), C1 NUMERIC(18,2),C4 NUMERIC(18,2),D1 NUMERIC(18,2),D4 NUMERIC(18,2), E1 NUMERIC(18,2),E4 NUMERIC(18,2),F1 NUMERIC(18,2),F4 NUMERIC(18,2), G1 NUMERIC(18,2),G4 NUMERIC(18,2),H1 NUMERIC(18,2),H4 NUMERIC(18,2), I1 NUMERIC(18,2),I4 NUMERIC(18,2),J1 NUMERIC(18,2),J4 NUMERIC(18,2), K1 NUMERIC(18,2),K4 NUMERIC(18,2),L1 NUMERIC(18,2),L4 NUMERIC(18,2), M1 as (ISNULL(A1,0)+ISNULL(B1,0)+ISNULL(C1,0)+ISNULL(D1,0)+ISNULL(E1,0)+ ISNULL(F1,0)+ISNULL(G1,0)+ISNULL(H1,0)+ISNULL(I1,0)+ISNULL(J1,0)+ ISNULL(K1,0)+ISNULL(L1,0)), M4 as (ISNULL(A4,0)+ISNULL(B4,0)+ISNULL(C4,0)+ISNULL(D4,0)+ISNULL(E4,0)+ ISNULL(F4,0)+ISNULL(G4,0)+ISNULL(H4,0)+ISNULL(I4,0)+ISNULL(J4,0)+ ISNULL(K4,0)+ISNULL(L4,0)) ) AS BEGIN INSERT @tempxsysb(kkers,eeggd,fsd,sydyszk,A1,A4,B1,B4,C1,C4,D1,D4,E1,E4,F1,F4,G1,G4,H1,H4,I1,I4,J1,J4,K1,K4,L1,L4) SELECT MA001,MA002,MV002,(SELECT ISNULL(SUM(LB013),0) FROM ACRLB WHERE LB005=MA001 AND LB004<@YF+'0101' ) AS sydyszk, (SELECT ISNULL(SUM(TC029+TC030),0) FROM COPTC WHERE TC004=MA001 AND LEFT(TC003,6)=@YF+'01') AS A1, (SELECT ISNULL(SUM(LB013),0) FROM ACRLB WHERE LB005=MA001 AND LEFT(LB003,6)=@YF+'01' AND LB011=-1) AS A4, (SELECT ISNULL(SUM(TC029+TC030),0) FROM COPTC WHERE TC004=MA001 AND LEFT(TC003,6)=@YF+'02') AS B1, (SELECT ISNULL(SUM(LB013),0) FROM ACRLB WHERE LB005=MA001 AND LEFT(LB003,6)=@YF+'02' AND LB011=-1) AS B4, (SELECT ISNULL(SUM(TC029+TC030),0) FROM COPTC WHERE TC004=MA001 AND LEFT(TC003,6)=@YF+'03') AS C1, (SELECT ISNULL(SUM(LB013),0) FROM ACRLB WHERE LB005=MA001 AND LEFT(LB003,6)=@YF+'03' AND LB011=-1) AS C4, (SELECT ISNULL(SUM(TC029+TC030),0) FROM COPTC WHERE TC004=MA001 AND LEFT(TC003,6)=@YF+'04') AS D1, (SELECT ISNULL(SUM(LB013),0) FROM ACRLB WHERE LB005=MA001 AND LEFT(LB003,6)=@YF+'04' AND LB011=-1) AS D4, (SELECT ISNULL(SUM(TC029+TC030),0) FROM COPTC WHERE TC004=MA001 AND LEFT(TC003,6)=@YF+'05') AS E1, (SELECT ISNULL(SUM(LB013),0) FROM ACRLB WHERE LB005=MA001 AND LEFT(LB003,6)=@YF+'05' AND LB011=-1) AS E4, (SELECT ISNULL(SUM(TC029+TC030),0) FROM COPTC WHERE TC004=MA001 AND LEFT(TC003,6)=@YF+'06') AS F1, (SELECT ISNULL(SUM(LB013),0) FROM ACRLB WHERE LB005=MA001 AND LEFT(LB003,6)=@YF+'06' AND LB011=-1) AS F4, (SELECT ISNULL(SUM(TC029+TC030),0) FROM COPTC WHERE TC004=MA001 AND LEFT(TC003,6)=@YF+'07') AS G1, (SELECT ISNULL(SUM(LB013),0) FROM ACRLB WHERE LB005=MA001 AND LEFT(LB003,6)=@YF+'07' AND LB011=-1) AS G4, (SELECT ISNULL(SUM(TC029+TC030),0) FROM COPTC WHERE TC004=MA001 AND LEFT(TC003,6)=@YF+'08') AS H1, (SELECT ISNULL(SUM(LB013),0) FROM ACRLB WHERE LB005=MA001 AND LEFT(LB003,6)=@YF+'08' AND LB011=-1) AS H4, (SELECT ISNULL(SUM(TC029+TC030),0) FROM COPTC WHERE TC004=MA001 AND LEFT(TC003,6)=@YF+'09') AS I1, (SELECT ISNULL(SUM(LB013),0) FROM ACRLB WHERE LB005=MA001 AND LEFT(LB003,6)=@YF+'09' AND LB011=-1) AS I4, (SELECT ISNULL(SUM(TC029+TC030),0) FROM COPTC WHERE TC004=MA001 AND LEFT(TC003,6)=@YF+'10') AS J1, (SELECT ISNULL(SUM(LB013),0) FROM ACRLB WHERE LB005=MA001 AND LEFT(LB003,6)=@YF+'10' AND LB011=-1) AS J4, (SELECT ISNULL(SUM(TC029+TC030),0) FROM COPTC WHERE TC004=MA001 AND LEFT(TC003,6)=@YF+'11') AS K1, (SELECT ISNULL(SUM(LB013),0) FROM ACRLB WHERE LB005=MA001 AND LEFT(LB003,6)=@YF+'11' AND LB011=-1) AS K4, (SELECT ISNULL(SUM(TC029+TC030),0) FROM COPTC WHERE TC004=MA001 AND LEFT(TC003,6)=@YF+'12') AS L1, (SELECT ISNULL(SUM(LB013),0) FROM ACRLB WHERE LB005=MA001 AND LEFT(LB003,6)=@YF+'12' AND LB011=-1) AS L4 FROM COPMA LEFT JOIN CMSMV ON MA016=MV001 return END
