Question

oracle sql语句问题，怎么将update语句和select查出来的数据关联上。是同一张表

sql语句：--年累计合格率
select round(avg(dyhgl),3),jcdid from mw_app.mwt_ud_dy_hgl where dyhgl is not null group by jcdid
update mw_app.mwt_ud_dy_hgl set ljhgl =.....
--同比
select
round((bn_hgl - sn_hgl),4) as tb
from (
select b.dyhgl as bn_hgl,n.dyhgl as sn_hgl from
(select dyhgl,jcdid from mw_app.mwt_ud_dy_hgl where tjyf = to_date(to_char(tjyf,'yyyy-mm'),'yyyy-mm')) b,
(select dyhgl,jcdid from mw_app.mwt_ud_dy_hgl where tjyf = add_months(to_date(to_char(tjyf,'yyyy-mm'),'yyyy-mm'),-12)) n
where b.jcdid = n.jcdid
)

update mw_app.mwt_ud_dy_hgl set tb=.....
查出来的数据是根据jcdid这个字段关联更新的，这个问题忙会一下午了。希望高手老解决

Answer 1

不看你的了，给你一个格式吧
update 表 别名1 from set 字段=(select 字段2 from 表 别名2 where 别名1.主键=别名2.主键)
就是给同一个表两个别名，就可以用不同的别名进行关联

update mw_app.mwt_ud_dy_hgl a set ljhgl =(select round(avg(dyhgl),3),jcdid from mw_app.mwt_ud_dy_hgl b where dyhgl is not null and a.主键=b.主键 group by jcdid)

Answer 2

new.fmcode,
case :new.fmtype when 1 then '01' when 2 then '02' when 3 then '03' else '01' end
from dual