通过pl/sql语句,一条一条的更新吧! 这样感觉还快一点。 DECLARE cursor wz_cursor IS select sum(nwzjf),b.njsy_sn from z_wzcf a,z_wzsj b,z_wzjsy c where a.nwzcf=b.nwzsj_sn and b.njsy_sn=c.njsy_sn group by b.njsy_sn; wzjf number(4); jsysn varchar2(10); BEGIN OPEN wz_cursor; LOOP FETCH wz_cursor INTO wzjf,jsysn; EXIT WHEN wz_cursor%NOTFOUND; update z_wzjsy set ndqjf=wzjf where njsy_sn=jsysn; END LOOP; CLOSE wz_cursor; END; /
update z_wzjsy d set d.ndqjf=(select sum(nwzjf) from z_wzcf a,z_wzsj b,z_wzjsy c where a.nwzcf=b.nwzsj_sn and b.njsy_sn=c.njsy_sn and b.njsy_sn=d.njsy_sn) -------------------------- 思路不对,这样是把所有的记录都更新了! 用临时表或游标。pl/sql逐行更新。注意一点:我个人认为是乎ORACLE里面不支持一个update语句一次只能更新一个驾驶员的属性值。所以循环更新不可以避免。
可以改成如下语句 update z_wzjsy d set d.ndqjf=(select sum(nwzjf) from z_wzcf a,z_wzsj b where a.nwzcf=b.nwzsj_sn and b.njsy_sn = d.njsy_sn) 然后在 z_wzsj的 njsy_sn上建索引,在nwzcf上建索引这样不会慢到那里去的
to: tyrone98(林林)你的写法必然导致全表更新,而且很危险容易把数据清空。正确写法: update z_wzjsy d set d.ndqjf = ( select sum(nwzjf) from z_wzcf a,z_wzsj b where a.nwzcf=b.nwzsj_sn and b.njsy_sn = d.njsy_sn ) where d.nwzsj_sn ( select nwzcf_sn from a )
创建一个临时表:create table a as select sum(nwzjf) from z_wzcf 然后再update,这样DML执行快
最近也碰到过类似问题,建议sql写法: UPDATE z_wzjsy d SET d.ndqjf = (SELECT SUM (a.nwzjf) FROM z_wzcf a, z_wzsj b WHERE a.nwzcf = b.nwzsj_sn AND b.njsy_sn = d.njsy_sn) WHERE EXISTS (SELECT 1 FROM z_wzcf a, z_wzsj b WHERE a.nwzcf = b.nwzsj_sn AND b.njsy_sn = d.njsy_sn) 如果速度还慢的话,看看执行计划,建些索引。写存储过程也可以搞定,但是没有一个sql简捷。
严重同意 rolandzhang() ( ) 的说法。 最正确的解决方法是: create table TempA as ( select sum(nwzjf) suma, njsy_sn bsn from z_wzcf a,z_wzsj b where a.nwzcf=b.nwzsj_sn ); create table TempB as ( select nwzcf_sn from z_wzcf
); update z_wzjsy d set d.ndqjf = ( select suma from TempA where bsn = d.njsy_sn ) where d.nwzsj_sn ( select * from TempB );
这样感觉还快一点。
DECLARE
cursor wz_cursor IS select sum(nwzjf),b.njsy_sn from z_wzcf a,z_wzsj b,z_wzjsy c where a.nwzcf=b.nwzsj_sn and b.njsy_sn=c.njsy_sn group by b.njsy_sn;
wzjf number(4);
jsysn varchar2(10);
BEGIN
OPEN wz_cursor;
LOOP
FETCH wz_cursor INTO wzjf,jsysn;
EXIT WHEN wz_cursor%NOTFOUND;
update z_wzjsy set ndqjf=wzjf where njsy_sn=jsysn;
END LOOP;
CLOSE wz_cursor;
END;
/
--------------------------
思路不对,这样是把所有的记录都更新了!
用临时表或游标。pl/sql逐行更新。注意一点:我个人认为是乎ORACLE里面不支持一个update语句一次只能更新一个驾驶员的属性值。所以循环更新不可以避免。
update z_wzjsy d set d.ndqjf=(select sum(nwzjf) from z_wzcf a,z_wzsj b where a.nwzcf=b.nwzsj_sn and b.njsy_sn = d.njsy_sn)
然后在 z_wzsj的 njsy_sn上建索引,在nwzcf上建索引这样不会慢到那里去的
update z_wzjsy d
set d.ndqjf =
(
select sum(nwzjf)
from z_wzcf a,z_wzsj b
where a.nwzcf=b.nwzsj_sn and b.njsy_sn = d.njsy_sn
)
where d.nwzsj_sn
(
select nwzcf_sn
from a
)
然后再update,这样DML执行快
UPDATE z_wzjsy d
SET d.ndqjf = (SELECT SUM (a.nwzjf)
FROM z_wzcf a, z_wzsj b
WHERE a.nwzcf = b.nwzsj_sn AND b.njsy_sn = d.njsy_sn)
WHERE EXISTS (SELECT 1
FROM z_wzcf a, z_wzsj b
WHERE a.nwzcf = b.nwzsj_sn AND b.njsy_sn = d.njsy_sn)
如果速度还慢的话,看看执行计划,建些索引。写存储过程也可以搞定,但是没有一个sql简捷。
最正确的解决方法是:
create table TempA
as
(
select sum(nwzjf) suma, njsy_sn bsn
from z_wzcf a,z_wzsj b
where a.nwzcf=b.nwzsj_sn
);
create table TempB
as
(
select nwzcf_sn
from z_wzcf
);
update z_wzjsy d
set d.ndqjf =
(
select suma from TempA where bsn = d.njsy_sn
)
where d.nwzsj_sn
(
select * from TempB
);