請教大家,我的資料結構如下

empno1 empno2 amount1     amount2     
------ ------ ----------- ----------- 
0010          0           0
0010   0011   0           10
0010   0012   0           15
0010   0011   0           20

我想整理資料成

empno1 empno2 amount1     amount2     
------ ------ ----------- ----------- 
0010          45          0
0010   0011   0           10
0010   0012   0           15
0010   0011   0           20

下面是我的實驗語法

declare @xtable table(empno1 char(4),empno2 char(4),amount1 integer,amount2 integer)    insert @xtable 
   select '0010','',0,0    insert @xtable 
   select '0010','0011',0,10    insert @xtable 
   select '0010','0012',0,15    insert @xtable 
   select '0010','0011',0,20    select * from  @xtable    
update @xtable set amount1=t.amount2
from (select empno1,Sum(amount2) as amount2 from @xtable group by empno1) as t
WHERE @xtable.empno1=t.empno1    

請問我的Update語法會出現 Server: Msg 137, Level 15, State 2, Line 19 Must declare the variable '@xtable'. 請教前輩,我要怎麼改,才能行得通? 謝謝您的熱心幫助... ps:ms-sqlserver2000 ..................... .楛耕傷稼,楛耘失歲. ..................... 發表人 - pedro 於 2005/11/24 10:25:35

declare @xtable table(empno1 char(4),empno2 char(4),amount1 integer,amount2 integer)    insert @xtable     select '0010','',0,0    insert @xtable     select '0010','0011',0,10    insert @xtable     select '0010','0012',0,15    insert @xtable     select '0010','0011',0,20    select * from  @xtable    update @xtable set amount1=t.amount2 from @xtable as a, (select empno1,Sum(amount2) as amount2 from @xtable group by empno1) as t WHERE a.empno1=t.empno1 select * from @xtable Ian

l630521大大,可以了,謝謝你 update @xtable set amount1=t.amount2 from @xtable as a, (select empno1,Sum(amount2) as amount2 from @xtable group by empno1) as t WHERE a.empno1=t.empno1 and rtrim(a.empno2)='' ..................... .楛耕傷稼,楛耘失歲. .....................