表：
a b c
1 10 ？
2 1 ？
3 2 ？
4 1 100
要求：
得到c（?）的结果.
比如：a列值为3的行(简化为a3)，a3.c=a4.c(100)+a4.b(1)=101
得到结果：
a b c
1 10 104
2 1 103
3 2 101
4 1 100
------解决方案--------------------
- SQL code
--> 测试数据：[tb]IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]GO CREATE TABLE [tb]([a] INT,[b] INT,[c] INT)INSERT [tb]SELECT 1,10,NULL UNION ALLSELECT 2,1,NULL UNION ALLSELECT 3,2,NULL UNION ALLSELECT 4,1,100--------------开始查询----------------------------1SELECT [a],[b],[c]=100+ISNULL((SELECT SUM([b]) FROM [tb] WHERE [a]>t.[a]),0) FROM [tb] AS t--2SELECT [a],[b],[c]=app.[c]+ISNULL((SELECT SUM([b]) FROM [tb] WHERE [a]>t.[a]),0) FROM [tb] AS tCROSS APPLY(SELECT TOP 1 ISNULL([c],0) AS [c] FROM [tb] ORDER BY [a] DESC ) app----------------结果----------------------------/* a b c1 10 1042 1 1033 2 1014 1 100*/
------解决方案--------------------
- SQL code
create table hb(a int,b int,c int)insert into hb select 1, 10, null union allselect 2, 1, null union allselect 3, 2, null union allselect 4, 1, 100 declare @sql varchar(6000) select @sql=' update t1 set t1.c= (select t2.c+t2.b from hb t2 where t2.a=t1.a+1) from hb t1 where t1.c is null' exec(@sql) while @@rowcount>0 exec(@sql)-- 结果select * from hb/*a b c----------- ----------- -----------1 10 1042 1 1033 2 1014 1 100(4 row(s) affected)*/