怎么不使用游标统计表内各项的值
如何不使用游标统计表内各项的值
比如我有一个表A,它包含两列,一个是编号Id,另一个是该编号的值Value:
现在我要统计表里每个编号的总值,并放到一个新表B里,B也包含两列,一个是编号Id,另一个是该编号的总值Sum。
用游标的话大概是这个样子:
遍历A表的每一行时,先检查B里有没有这个Id,有则更新,没有则插入。
请问如何不用游标做到?
如果除了检查有没有该Id以外,还有其他东西要检查,可不可能不用游标就能做到(比如加个Type检测,如果Type为0则加上@Value,为1则减去@Value)?
------解决方案--------------------
比如我有一个表A,它包含两列,一个是编号Id,另一个是该编号的值Value:
- SQL code
1 34 2 56 6 18 1 42 5 274 3 25 1 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; END CLOSE @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 all select 2,56 union all select 6,18 union all select 1,42 union all select 5,274 union all select 3,25 union all select 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.ssum from tb b (select id,sum(value) as ssum from ta group by id)a on a.id=b.id insert into tb select 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