日期:2014-05-17  浏览次数:20365 次

新手求教一个求和的问题,希望各位帮帮忙
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 ACR