declare @table table (A varchar(10),B int,C INT,D FLOAT)
insert into @table values ('A',100,1,1.02)
insert into @table values ('A',100,2,1.12)
insert into @table values ('A',100,3,1.33)
insert into @table values ('A',100,4,1.14)
insert into @table values ('A',100,5,1.35)
-- @table1 也需要个顺序列，没有就ROW_NUMBER加一个
declare @table1 table (A varchar(10),B INT,C INT)
insert into @table1 values ('A',125,1)
insert into @table1 values ('A',10,2)
insert into @table1 values ('A',20,3)
insert into @table1 values ('A',30,4)
DECLARE @RESULT TABLE(A varchar(10),B INT,C1 INT,D FLOAT,T1_ROW INT,B2 INT,C2 INT)
;WITH CTE AS(
SELECT
T1.A
,CASE WHEN T1.B>T2.B THEN T1.B-T2.B ELSE 0 END B
,CASE WHEN T1.B>T2.B THEN T2.B ELSE T1.B END B2
,T1.C
,T2.B-T1.B DIFF
,T1.D D
,T1.C C1
,T2.C C2
,1 T1_ROW
,1 T2_ROW
FROM @table T1
JOIN @table1 T2 ON T1.A=T2.A
WHERE T1.C=1 AND T2.C=1
UNION ALL
SELECT
T1.A
,CASE WHEN T1.B>T2.DIFF THEN T1.B-T2.DIFF ELSE 0 END
,CASE WHEN T1.B>T2.DIFF THEN T2.DIFF ELSE T1.B END
,T1.C
,T2.DIFF-T1.B
,T1.D
,T1.C
,T2.C2
,1
,T2.T2_ROW+1
FROM @table T1
JOIN CTE T2 ON T1.A=T2.A AND T1.C=T2.C1+1 AND T2.DIFF>0
UNION ALL
SELECT
T2.A
,T2.B-T1.B
,CASE WHEN T1.B>T2.B THEN T2.B ELSE T1.B END
,T2.C
,T2.DIFF+T1.B
,T2.D
,T2.C1
,T1.C
,T2.T1_ROW+1
,1
FROM @table1 T1
JOIN CTE T2 ON T1.A=T2.A AND T1.C=T2.C2+1 AND T2.DIFF<0
UNION ALL
SELECT
T1.A
,CASE WHEN T1.B>T2.B THEN T1.B-T2.B ELSE 0 END
,CASE WHEN T1.B>T2.B THEN T2.B ELSE T1.B END
,T1.C
,T2.B-T1.B
,T1.D
,T1.C
,T2.C
,1
,1
FROM CTE T0
JOIN @table T1 ON T0.C1+1=T1.C
JOIN @table1 T2 ON T0.C2+1=T2.C
WHERE T0.DIFF=0
)
--SELECT * FROM CTE
INSERT INTO @RESULT
SELECT A,B,C1,D,T1_ROW,B2,C2 FROM CTE
-- @table 结果
SELECT T1.A,ISNULL(T2.B,T1.B)B,T1.C,T1.D FROM @table T1
LEFT JOIN @RESULT T2 ON T2.T1_ROW=1 AND T1.C=T2.C1
-- @table1 结果
SELECT T1.A,T2.B2 B,ISNULL(T2.D,0)D FROM @table1 T1
LEFT JOIN @RESULT T2 ON T1.C=T2.C2
--如果你需要根据A分组，则序号还要另外处理
