怎么不使用游标统计表内各项的值

如何不使用游标统计表内各项的值
比如我有一个表A，它包含两列，一个是编号Id，另一个是该编号的值Value：

SQL code

1    342    566    181    425    2743    251    56...

现在我要统计表里每个编号的总值，并放到一个新表B里，B也包含两列，一个是编号Id，另一个是该编号的总值Sum。
用游标的话大概是这个样子：

SQL code

DECLARE @A table (Type int, Id int, Value int);DECLARE @B table (Id int, Sum int);DECLARE @Cursor cursor, @Id int, @Value int;SET @Cursor = CURSOR FAST_FORWARD FOR SELECT Id, Value FROM @A;OPEN @Cursor;FETCH NEXT FROM @Cursor INTO @Id, @Value;WHILE @@FETCH_STATUS = 0    BEGIN    IF NOT EXISTS (SELECT * FROM @B WHERE Id = @Id)        INSERT @B (Id, Sum) VALUES (@Id, @Value);    ELSE        UPDATE @B SET Sum = Sum + @Value WHERE Id = @Id;    FETCH NEXT FROM @Cursor INTO @Id, @Value;    ENDCLOSE @Cursor;DEALLOCATE @Cursor;

遍历A表的每一行时，先检查B里有没有这个Id，有则更新，没有则插入。
请问如何不用游标做到？
如果除了检查有没有该Id以外，还有其他东西要检查，可不可能不用游标就能做到（比如加个Type检测，如果Type为0则加上@Value，为1则减去@Value）？

[解决办法]

SQL code

create table A(  id int,  value int)create table B(  id int,  [sum] int)insert into A select 1,34 union allselect 2,56 union allselect 6,18 union allselect 1,42 union allselect 5,274 union allselect 3,25 union allselect 1,56 --先删后插delete from B where id in(select distinct id from A)insert into B select id,SUM(value) from A group by id--select * from B
[解决办法]
SQL code
update b set b.[sum]=b.[sum]+a.ssumfrom tb b(select id,sum(value) as ssum from ta group by id)a on a.id=b.idinsert into tbselect id,sum(value) from ta  where not exists(select 1 from tb where id=ta.id)     group by id
[解决办法]
declare @T table(id int,v int)
insert into @T
select 1,34 union all
select 2,56 union all  
select 6,18 union all  
select 1,42 union all  
select 5,274 union all  
select 1,56 union all  
select 2,56 

  
declare @A table(id int,v int)
select id,sum(v) as sumV from @T group by id

insert into @A  select id,sum(v) as sumV from @T group by id
select * from @A 

 

查看更多 下一篇