1. 业务要求: 数据库结构： create table WL(DH varchar(20),MCh varchar(20),GG varchar(20),DW varchar(20)) go insert into WL values('A10021040','WMS 电阻','50R','个') go create table SPD(DH varchar(20),MCh varchar(20),GG varchar(20),DW varchar(20),SQShL int,RQ datetime) go insert into SPD values('A10021040','WMS 电阻','50R','个',10,'2010-04-30') go create table RK(DH varchar(20),MCh varchar(20),GG varchar(20),DW varchar(20),RKShL int,RQ datetime) go 

insert into RK values('A10021040','WMS 电阻','50R','个',10,'2010-05-04') go insert into RK values('A10021040','WMS 电阻','50R','个',15,'2010-05-08') go create table CK(DH varchar(20),MCh varchar(20),GG varchar(20),DW varchar(20),CKShL int,RQ datetime) go insert into CK values('A10021040','WMS 电阻','50R','个',20,'2010-05-10') go 2. 数据库实现方式： WITH START AS ( SELECT WL.*, ISNULL(SPD.sqshl,0) qty, ISNULL(RQ,'2010-04-30') RQ FROM WL LEFT JOIN SPD ON WL.DH = SPD.DH ) ,DAILY AS ( SELECT C.DH,D.MCh,D.GG,D.DW,C.RKShL,C.CKShL,C.RQ,C.rn FROM ( SELECT ISNULL(A.DH,B.DH) DH, ISNULL(A.RKShL,0) RKShL,ISNULL(B.CKShL,0) CKShL, ISNULL(A.RQ,B.RQ) RQ, ROW_NUMBER() OVER(PARTITION BY ISNULL(A.DH,B.DH) ORDER BY ISNULL(A.RQ,B.RQ) ) rn FROM RK A FULL JOIN CK B ON A.DH=B.DH AND A.RQ=B.RQ ) C LEFT JOIN dbo.WL D ON C.DH=D.DH ) ,R AS ( SELECT DH, MCh, GG, DW, qty start_qty, 0 RKShL, 0 CKShL, qty end_qty, RQ, CONVERT(bigint,0) rn FROM START UNION ALL SELECT d.DH, d.MCh, d.GG, d.DW, r.end_qty start_qty, 

d.RKShL, d.CKShL, r.end_qty + d.RKShL - d.CKShL end_qty, d.RQ, d.rn FROM r JOIN DAILY d ON r.DH = d.DH AND r.rn + 1 = d.rn ) SELECT * FROM r-- ORDER BY DH, rn