在sql server 2005中不使用while循环更新表数据 [英] Update table Data without using while loop in sql server 2005

问题描述

在sql中我有如下表数据

in sql i have table data like below

id      type     amount       
1      type1       2000    
2      type1       1000     
3      type2        500    
4      type3       3000    
5      type1       2000   
6      type2        500        
7      type3       5000    
8      type1       1000    

并且我想在如下所示的 select 语句中获取数据

and i want to get datas in select statement like below

id      type     amount      current   
1      type1       2000         2000                
2      type1       1000         1000                 
3      type2        500          500                 
4      type3       3000         3000                 
5      type1       2000         3000                  
6      type2       -500            0                 
7      type3       5000         2000
8      type1      -1000         4000 

等等这意味着每种类型必须具有基于金额类型的当前总金额并且不需要while循环，因为执行需要很长时间

and so on that means each type must have its current total amount based on amount type and its need to be dont have while loop because it takes long time to execute

for eg:

in type 1

ID      Amount      current 
1      2000-add       2000                   
2      1000-sub       1000                  
3      2000-add       3000                   
4      1000-add       4000                   

怎么做

推荐答案

我认为这个查询会起作用:

I think this query will work:

select id,type,amount,(select sum(amount) from mytable t1 where t1.type=t2.type and t1.id<=t2.id) currenttotal from mytable t2

select id,type,amount,(select sum(amount) from mytable t1 where t1.type=t2.type and t1.id<=t2.id) currenttotal from mytable t2

这篇关于在sql server 2005中不使用while循环更新表数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！