34,576
社区成员
发帖
与我相关
我的任务
分享
IF OBJECT_ID('TAB1') IS NOT NULL
BEGIN
DROP TABLE TAB1;
END
GO
CREATE TABLE TAB1
(
BH VARCHAR(20),
SDYF VARCHAR(20),
YSJE DECIMAL(12,2)
)
INSERT INTO TAB1
SELECT 'A','2013-01',100
UNION ALL
SELECT 'B','2013-01',200
UNION ALL
SELECT 'A','2013-02',101
UNION ALL
SELECT 'B','2013-02',201
UNION ALL
SELECT 'A','2013-03',102
UNION ALL
SELECT 'B','2013-03',202
IF OBJECT_ID('TAB2') IS NOT NULL
BEGIN
DROP TABLE TAB2;
END
GO
CREATE TABLE TAB2
(
BH VARCHAR(20),
SDYF VARCHAR(20),
YSJE DECIMAL(12,2)
)
insert into TAB2
select 'A','2013-01',90
union all
select 'B','2013-01',200
union all
select 'A','2013-02',101
union all
select 'B','2013-02',200
union all
select 'A','2013-03',80
union all
select 'B','2013-03',180
;WITH T_SYWSJE AS (
SELECT A.BH AS BH,
A.SDYF,
SUM(a.YSJE-b.YSJE) AS SYWSJE FROM TAB1 a
INNER JOIN TAB2 b ON a.BH=b.BH AND a.SDYF=b.SDYF
GROUP BY a.SDYF,A.BH
)
SELECT A.BH,A.SDYF,
(
SELECT ISNULL(SUM(SYWSJE),0) FROM T_SYWSJE
WHERE BH=A.BH AND ((LEFT(SDYF,4)<LEFT(a.SDYF,4)) OR (LEFT(SDYF,4)=LEFT(a.SDYF,4) AND RIGHT(SDYF,2)<RIGHT(A.SDYF,2)))
)
AS SYWSJE,A.YSJE,B.YSJE AS BYSJE,
(
SELECT ISNULL(SUM(SYWSJE),0) FROM T_SYWSJE
WHERE BH=A.BH AND ((LEFT(SDYF,4)<LEFT(a.SDYF,4)) OR (LEFT(SDYF,4)=LEFT(a.SDYF,4) AND RIGHT(SDYF,2)<RIGHT(A.SDYF,2)))
)
+A.YSJE-B.YSJE AS BYWSJE FROM TAB1 a
INNER JOIN TAB2 b ON a.BH=b.BH AND a.SDYF=b.SDYF
LEFT JOIN T_SYWSJE C ON C.BH=A.BH AND C.SDYF=a.SDYF
--> 测试数据: @TAB1
declare @TAB1 table (编号 varchar(1),输单月份 varchar(7),应收金额 int)
insert into @TAB1
select 'A','2013-01',100 union all
select 'B','2013-01',200 union all
select 'A','2013-02',101 union all
select 'B','2013-02',201 union all
select 'A','2013-03',102 union all
select 'B','2013-03',202
--> 测试数据: @TAB2
declare @TAB2 table (编号 varchar(1),输单月份 varchar(7),实收金额 int)
insert into @TAB2
select 'A','2013-01',90 union all
select 'B','2013-01',200 union all
select 'A','2013-02',101 union all
select 'B','2013-02',200 union all
select 'A','2013-03',80 union all
select 'B','2013-03',180
;with maco as
(
select
a.*,b.实收金额,应收金额-实收金额 as 本月未收金额
from @TAB1 a left join @TAB2 b
on a.编号=b.编号 and a.输单月份=b.输单月份
)
,maco2 as
(
select
编号,输单月份,应收金额,实收金额,
(select sum(本月未收金额) from maco where 编号=t.编号 and 输单月份<=t.输单月份) as 本月未收金额
from maco t
)
select
编号,输单月份,isnull((select 本月未收金额 from maco2 where 编号=t.编号 and
cast(输单月份+'-01' as datetime)=dateadd(m,-1,cast(t.输单月份+'-01' as datetime) ) ),0) as 上月未收金额,
应收金额,实收金额,本月未收金额
from maco2 t
/*
编号 输单月份 上月未收金额 应收金额 实收金额 本月未收金额
---- ------- ----------- ----------- ----------- -----------
A 2013-01 0 100 90 10
B 2013-01 0 200 200 0
A 2013-02 10 101 101 10
B 2013-02 0 201 200 1
A 2013-03 10 102 80 32
B 2013-03 1 202 180 23
*/
你可以看一些思路,该代码有优化空间.