Oracle相关

1，写的第一个触发器

CREATE OR REPLACE TRIGGER TRIG_HNDX_YXDM --床位信息表更新时，更新xsxxb的yxdm字段（取楼栋信息表xg_gygl_new_ldxxb的xqdm）

  after update on xg_gygl_new_cwxxb

  for each row

declare

  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