A B C 1 10 20 1 10 20 2 20 30 2 10 30 3 40 100 3 50 100 3 10 100
C는 비어있는 상태고,.. A그룹의 B값들을 모두 SUM한 값을 C에 넣으려면.. SQL로 . 고민고민 하다 올립니다.
create table xyz ( a int, b int, c int ) go
insert into xyz values (1, 10, 0 ) insert into xyz values (1, 10, 0) insert into xyz values (2, 20, 0) insert into xyz values (2, 10, 0 ) insert into xyz values (3, 40, 0) insert into xyz values (3, 50, 0) insert into xyz values (3, 10, 0) go
---------------------------- --TEMP TABLE 쓰실경우 ----------------------------
create table #lim ( a int, c int )
insert into #lim select a, sum (b) as c from xyz group by a update xyz set a.c = b.c from xyz a, #lim b where a.a = b.a select * from xyz drop table #lim go
----------------------------- -- CURSOR 쓰실경우 -----------------------------
DECLARE xyz_cursor CURSOR FOR select a from xyz order by a go
declare @x int, @y int OPEN xyz_cursor FETCH xyz_cursor INTO @x
WHILE @@SQLSTATUS = 0 BEGIN
select @y = sum (b) from xyz where a = @x group by a
update xyz set c = @y from xyz where a = @x FETCH xyz_cursor INTO @x
END
CLOSE xyz_cursor go DEALLOCATE cursor xyz_cursor go select * from xyz go