问：要求把表B按照CLM001分组求和计算score的值，然后加到表A中对应CLM001的score的值。如何写更新表A的SQL语句?
表A:
CLM001 SCORE
001 0
002 0
003 0
004 0
其中CLM001是主键。
表B:
ID CLM001 SCORE
0 001 12
1 001 24
2 002 110
3 004 220
4 004 20
答：
|
with r as { select CLM001 ,sum(score) as total from b group by CLM001 } update a set score = (select total from r where r.CLM001 =a.CLM001) where exists (select 1 from r where r.CLM001 =a.CLM001) |
点评:此方法需要写存储过程，否则在程序中没法用。
|
create or replace procedure p_test as v_clm001 a.clm001%type; cursor c_record is select distinct clm001 from b; begin open c_record; loop fetch c_record into v_clm001; exit when c_record%notfound; update a set score =(select sum(score) from b where clm001=v_clm001) where clm001=v_clm001; end loop; end; |
点评:这是在PL/SQL中的语句，此问题用PL/SQL解决也不失为一个简单的方法。
| update a set a.score = a.score + (select b.score from a a,
(select CLM001,sum(score) from b group by CLM001) b where a.CLM001 = b.CLM001) |
点评:非常好的答案，此类问题用视图解决很方便!
