新手求教一个求和的有关问题,希望各位帮帮忙
新手求教一个求和的问题,希望各位帮帮忙
------解决方案--------------------
在嵌套一层
- SQL code
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的值。。。有人能跟我讲讲不。
------解决方案--------------------
在嵌套一层
- SQL code
INSERT @tempxsysb SELECT 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