sql集锦

sql汇总
表：
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)*/