1，寫的第一個觸發器 CREATE OR REPLACE TRIGGER TRIG_HNDX_YXDM --床位信息表更新時，更新xsxxb的yxdm欄位（取樓棟信息表xg_gygl_new_ldxxb的xqdm） after update on xg_gygl_new_cwxxb for each ...
1，寫的第一個觸發器CREATE OR REPLACE TRIGGER TRIG_HNDX_YXDM --床位信息表更新時，更新xsxxb的yxdm欄位（取樓棟信息表xg_gygl_new_ldxxb的xqdm） after update on xg_gygl_new_cwxxb for each rowdeclare v_xh varchar2(10); v_xqdm varchar2(10);begin if :new.xh is not null then --如果床位有人入住 v_xh := :new.xh; select xqdm into v_xqdm from xg_gygl_new_ldxxb where lddm = :new.lddm; elsif :new.xh is null then --如果床位清空，有人退宿之類的 v_xh := :old.xh; v_xqdm := ''; end if; update xsxxb set yxdm = v_xqdm where xh = v_xh;end;
2，一張表裡的欄位knlxdm是 01,02,03這種逗號分隔的dm，需要關聯另一張表取對應的mc
tsxs_knlxb: knlxdm knlxmc 對應表select * from VIEW_XLZX_TSXSXX a left join(
with split_data as ( select key, rn, substr(str, instr(str, ',', 1, rn)+1, instr(str, ',', 1, rn+1) - instr(str, ',', 1, rn) - 1) str from (select knlxdm key, ','||knlxdm||',' str from VIEW_XLZX_TSXSXX) a, (select rownum rn from dual connect by rownum < 10) b where instr(str, ',', 1, rn+1) > 0 ) select key, substr(max(sys_connect_by_path(knlxmc, ',')), 2) knlxmc from split_data a, tsxs_knlxb b where a.str = b.knlxdm start with rn = 1 connect by key = prior key and rn-1 = prior rn group by key) b on a.knlxdm=b.key 3， 根據某個欄位分組，然後根據時間取最新數據select xh, zdz, xgsj from (select xh, zdz, xgsj, row_number() over(partition by xh order by xgsj desc) rn from (select a."SQID", a."XH", a."XGSJ", b.zd, b.zdz, b.xgqz from xg_xsxx_xxxgsqb a left join xg_xsxx_xgzdb b on a.sqid = b.sqid where b.zd = 'zzmm' and a.xgsj > '2016-01-01 00:00:00')) t1 where rn = 1
