update a set task_status = (select sum(decode(task_stats,2,1,0))/count(1) from b where b.task_id = a.task_id) where exists ( select 1 from b where b.task_id = a.task_id)
insert into a select task_id,sum(decode(task_stats,2,task_seq))/sum(task_seq) from b group by task_id
update a set task_status=(select sum(decode(task_stats,2,task_seq))/sum(task_seq) from b where a.task_id=b.task_id group by task_id)
update a set task_status=(select sum(decode(task_stats,2,task_seq))/sum(task_seq) from b where a.task_id=b.task_id group by task_id)
我的上贴排除了除零的情况,但不能更新旧值,可改为: update a set task_status = (select sum(decode(task_stats,2,1,0))/decode(count(1),0,1) from b where b.task_id = a.task_id)
select task_id,sum(decode(task,2,1,0))/count(1) from b
group by task_id
select task_id,sum(decode(task_stats,2,1,0))/count(1) from b
group by task_id
A表中已经存在task_id这条记录了,只是task_status为空,或者是一个旧值。
所以INSERT肯定不对的,只能是UPDATE
B表是A表的子表,所以肯定是先有A表中的记录,才有B表中的记录。
where exists ( select 1 from b where b.task_id = a.task_id)
update a set task_status =
(select sum(decode(task_stats,2,1,0))/decode(count(1),0,1)
from b where b.task_id = a.task_id)