经典SQL语句-ORACLE

merger into的用法-插入、更新前先判断、 传入参数

    merge into ad_accessrec s
    using
    (
       select 20885 id,to_date('2008-2-27','yyyy-mm-dd') time, 127367 placeid,31 a,3 b from dual) t
     --  on (t.adid=20885  and t.accesstime=to_date('2008-2-27','yyyy-mm-dd') and t.placeid=127367 and t.kindtechid=31  and t.status=3)
      on (s.adid=t.id and s.placeid=t.time)
      when matched
       then update set s.ipcnt=200
       when not matched
       then   insert(ordid,adid,placeid,accesstime,pointid,kindtechid,ipcnt,status)
          values(ad_accessrec_seq.nextval,20885,127367,to_date(2008-2-27,'yyyy-mm-dd'),13928,31,100,3);        

1、取符合条件的记录前5条
select * from
(
select adid,arrid,row_number() over(partition by arrid order by arrid) total  from  ad_adset a
)
where total<=5；

合并同一ID的其他列的内容
 select videoid, wm_concat(c_name) tagname
                  from pmh_video_category t6, pmh_category_config t7
                 where t6.cid = t7.cid
                 group by videoid;