楼主你算错了;WITH CTE AS( SELECT TOP 1 T1.A ,CASE WHEN T1.B>T2.B THEN T1.B-T2.B ELSE 0 END B ,T1.C ,CASE WHEN T1.B>=T2.B THEN 0 ELSE T2.B-T1.B END DIFF FROM @table T1 JOIN @table1 T2 ON T1.A=T2.A ORDER BY T1.C UNION ALL SELECT T1.A ,CASE WHEN T1.B>T2.DIFF THEN T1.B-T2.DIFF ELSE 0 END ,T1.C ,CASE WHEN T1.B>=T2.DIFF THEN 0 ELSE T2.DIFF-T1.B END DIFF FROM @table T1 JOIN CTE T2 ON T1.A=T2.A AND T1.C=T2.C+1 ) SELECT A,B,C FROM CTE
这样 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)declare @table1 table (A varchar(10),B int) insert into @table1 values ('A',125) insert into @table1 values ('A',10) insert into @table1 values ('A',20) insert into @table1 values ('A',30)declare @table2 table (A varchar(10),B int,C float)-- @table 结果 --------------------------------------- A 0 1 1.02 A 15 2 1.12 A 100 3 1.33 A 100 4 1.14 A 100 5 1.35-- @table2 结果 --------------------------------------- A 100 1.02 A 25 1.12 A 10 1.12 A 20 1.12 A 30 1.12
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)declare @table1 table (A varchar(10),B int) insert into @table1 values ('A',125) insert into @table1 values ('A',10) insert into @table1 values ('A',20) insert into @table1 values ('A',30)declare @table2 table (A varchar(10),B int,C float);WITH CTE AS( SELECT TOP 1 T1.A ,CASE WHEN T1.B>T2.B THEN T1.B-T2.B ELSE 0 END B ,T1.C ,CASE WHEN T1.B>=T2.B THEN 0 ELSE T2.B-T1.B END DIFF FROM @table T1 JOIN @table1 T2 ON T1.A=T2.A ORDER BY T1.C UNION ALL SELECT T1.A ,CASE WHEN T1.B>T2.DIFF THEN T1.B-T2.DIFF ELSE 0 END ,T1.C ,CASE WHEN T1.B>=T2.DIFF THEN 0 ELSE T2.DIFF-T1.B END DIFF FROM @table T1 JOIN CTE T2 ON T1.A=T2.A AND T1.C=T2.C+1 ) SELECT A,B,C FROM CTE--- 好像结果不对 A 0 1 A 75 2 A 100 3 A 100 4 A 100 5
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分组,则序号还要另外处理
SELECT TOP 1 T1.A
,CASE WHEN T1.B>T2.B THEN T1.B-T2.B ELSE 0 END B
,T1.C
,CASE WHEN T1.B>=T2.B THEN 0 ELSE T2.B-T1.B END DIFF
FROM @table T1
JOIN @table1 T2 ON T1.A=T2.A
ORDER BY T1.C
UNION ALL
SELECT
T1.A
,CASE WHEN T1.B>T2.DIFF THEN T1.B-T2.DIFF ELSE 0 END
,T1.C
,CASE WHEN T1.B>=T2.DIFF THEN 0 ELSE T2.DIFF-T1.B END DIFF
FROM @table T1
JOIN CTE T2 ON T1.A=T2.A AND T1.C=T2.C+1
)
SELECT A,B,C FROM CTE
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)declare @table1 table (A varchar(10),B int)
insert into @table1 values ('A',125)
insert into @table1 values ('A',10)
insert into @table1 values ('A',20)
insert into @table1 values ('A',30)declare @table2 table (A varchar(10),B int,C float)-- @table 结果
---------------------------------------
A 0 1 1.02
A 15 2 1.12
A 100 3 1.33
A 100 4 1.14
A 100 5 1.35-- @table2 结果
---------------------------------------
A 100 1.02
A 25 1.12
A 10 1.12
A 20 1.12
A 30 1.12
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)declare @table1 table (A varchar(10),B int)
insert into @table1 values ('A',125)
insert into @table1 values ('A',10)
insert into @table1 values ('A',20)
insert into @table1 values ('A',30)declare @table2 table (A varchar(10),B int,C float);WITH CTE AS(
SELECT TOP 1 T1.A
,CASE WHEN T1.B>T2.B THEN T1.B-T2.B ELSE 0 END B
,T1.C
,CASE WHEN T1.B>=T2.B THEN 0 ELSE T2.B-T1.B END DIFF
FROM @table T1
JOIN @table1 T2 ON T1.A=T2.A
ORDER BY T1.C
UNION ALL
SELECT
T1.A
,CASE WHEN T1.B>T2.DIFF THEN T1.B-T2.DIFF ELSE 0 END
,T1.C
,CASE WHEN T1.B>=T2.DIFF THEN 0 ELSE T2.DIFF-T1.B END DIFF
FROM @table T1
JOIN CTE T2 ON T1.A=T2.A AND T1.C=T2.C+1
)
SELECT A,B,C FROM CTE--- 好像结果不对
A 0 1
A 75 2
A 100 3
A 100 4
A 100 5
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分组,则序号还要另外处理