SQL 语句

[azsoft]

declare @table table (A varchar(10),B int,C int)
insert into @table values ('A',100,1)
insert into @table values ('A',100,2)
insert into @table values ('A',100,3)
insert into @table values ('A',100,4)
insert into @table values ('A',100,5)
insert into @table values ('A',100,6)
insert into @table values ('A',100,7)
insert into @table values ('A',100,8)

declare @table1 table (A varchar(10),B int)
insert into @table1 values ('A',625)

-- 结果（结合@table1）
select * from @table
A B C
--------------------------------------
A 0 1
A 0 2
A 0 3
A 0 4
A 0 5
A 75 6
A 100 7
A 100 8

[还在加载中灬]

楼主你算错了

;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

[azsoft]

递归的方法好，以前没有用过，不过，现在如果递归的同时，把轮询的值写入到另外一个表中，该如何处理呢？谢谢

[还在加载中灬]

没明白，把结果写入到一个表中?

[azsoft]

这样 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

[azsoft]

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分组，则序号还要另外处理

[azsoft]

非常感谢，学到了，CTE确实好用。但是，实际业务要更复杂些，我在想用函数来实现，会不会更好些。结贴了