求一条SQL

Question

TAB1
编号     输单月份        应收金额   
A      2013-01       100
B      2013-01       200
A      2013-02       101
B      2013-02       201
A      2013-03       102
B      2013-03       202
TAB2
编号     输单月份      实收金额
A         2013-01   90
B         2013-01   200
A         2013-02    101
B         2013-02   200
A         2013-03   80
B         2013-03    180我要的结果如下 
编号     输单月份    上月未收金额   应收金额   实收金额   本月未收金额      
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    202          1

Answer 1

--> 测试数据: @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
*/
你可以看一些思路,该代码有优化空间.

Answer 2

;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

Answer 3

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