oracle常用到的一些SQL

--最近30天
select to_char(sysdate-31+rownum,'yyyymmdd') as date_time from dual

       connect by rownum <=to_date(to_char(sysdate,'yyyymmdd'),'yyyymmdd')-to_date(to_char(sysdate-30,'yyyymmdd'),'yyyymmdd');

--上月每一天      
SELECT to_char(LAST_DAY(ADD_MONTHS(SYSDATE,-2))+ROWNUM,'yyyymmdd') as date_time FROM DUAL

CONNECT BY ROWNUM <=trunc(LAST_DAY(ADD_MONTHS(SYSDATE, -1))-(LAST_DAY(ADD_MONTHS(SYSDATE, -2)) + 1))+1;

--连续的数字

select to_char(rownum-1) as hour_time from dual connect by rownum<=24;

--增量表：向table3中插入table1有table2没有的记录（比较的是所有字段）
insert into table3
  (result_id, redis_key, vod_id, rec_val, create_time, result_type)
  select result_id, redis_key, vod_id, rec_val, create_time, '0'
    from (select result_id, redis_key, vod_id, rec_val, create_time
            from table1
          minus
          select result_id, redis_key, vod_id, rec_val, create_time
            from table2);
--增量表：向table3中插入table1有table2没有的记录（比较的是选择性的字段）  
insert into table3
  (result_id, redis_key, vod_id, rec_val, create_time, result_type)
  select result_id, redis_key, vod_id, rec_val, create_time, '0'
    from (select a.result_id,
                 a.redis_key,
                 a.vod_id,
                 a.rec_val,
                 a.create_time
            from table1 a
           where not exists (select 1
                    from table2 b
                   where a.redis_key = b.redis_key
                     and a.vod_id = b.vod_id

                     and a.rec_val = b.rec_val));

--递归：获取所属组织和下属组织
select so.org_id,so.org_name,so.parent_org_id,so.ileaf
  from SYS_ORG so where so.UIDVALIDITY=1

  start with so.org_id=2522 connect by prior org_id=parent_org_id;

--行转列
select * from (select mo.operation_name,moo.p_day,moo.rec_exp_times
 from mg_operation mo left join MIGU_ONLINE_STATS_ORACLE moo on mo.operation_id=moo.loc_id)
 pivot (sum(rec_exp_times) for operation_name in ('猜你喜欢' 猜你喜欢,'短视频' 短视频,'为我推荐' 为我推荐,'小伙伴正在看' 小伙伴正在看)) order by p_day desc;
--多行转多列
select * from MIGU_APP_ONLINE_EVAL_PLAN ma;
select sorted,regexp_substr(exposure,'[^|]+',1,1,'i') AS exp_num,regexp_substr(exposure,'[^|]+',1,2,'i') AS clk_num,regexp_substr(exposure,'[^|]+',1,3,'i') AS rate from (
 select sorted,exposure from (
  select nvl(sum(ma.exp_times),0)||'|'||nvl(sum(ma.clk_times),0)||'|'||decode(nvl(sum(ma.exp_times),0),0,'0%',to_char(round(nvl(sum(ma.clk_times),0)/sum(ma.exp_times),4)*100,'fm9999990.9999')||'%') as 总数,
  nvl(sum(ma.exp_bill_cnt),0)||'|'||nvl(sum(ma.clk_bill_cnt),0)||'|'||decode(nvl(sum(ma.exp_bill_cnt),0),0,'0%',to_char(round(nvl(sum(ma.clk_bill_cnt),0)/sum(ma.exp_bill_cnt),4)*100,'fm9999990.9999')||'%') as 人次,
  nvl(sum(ma.mv_exp_times),0)||'|'|| nvl(sum(ma.mv_clk_times),0)||'|'||decode(nvl(sum(ma.mv_exp_times),0),0,'0%',to_char(round(nvl(sum(ma.mv_clk_times),0)/sum(ma.mv_exp_times),4)*100,'fm9999990.9999')||'%') as 电影,
  nvl(sum(ma.tv_exp_times),0)||'|'|| nvl(sum(ma.tv_clk_times),0)||'|'||decode(nvl(sum(ma.tv_exp_times),0),0,'0%',to_char(round(nvl(sum(ma.tv_clk_times),0)/sum(ma.tv_exp_times),4)*100,'fm9999990.9999')||'%') as 电视剧,
  nvl(sum(ma.com_exp_times),0)||'|'||nvl(sum(ma.com_clk_times),0)||'|'||decode(nvl(sum(ma.com_exp_times),0),0,'0%',to_char(round(nvl(sum(ma.com_clk_times),0)/sum(ma.com_exp_times),4)*100,'fm9999990.9999')||'%') as 动漫,
  nvl(sum(ma.rs_exp_times),0)||'|'||nvl(sum(ma.rs_clk_times),0)||'|'||decode(nvl(sum(ma.rs_exp_times),0),0,'0%',to_char(round(nvl(sum(ma.rs_clk_times),0)/sum(ma.rs_exp_times),4)*100,'fm9999990.9999')||'%') as 综艺 
  from MIGU_APP_ONLINE_EVAL_PLAN ma where ma.loc_id='1' and to_date(ma.updateday,'yyyymmdd')>=to_date('2018-01-01','yyyy-mm-dd')
 ) unpivot (exposure for sorted in (总数,人次,电影,电视剧,动漫,综艺))

) tt;

--wm_concat函数,该函数可以把列值以","号分隔起来,并显示成一行
select wm_concat(m.operation_name) as names,wm_concat(m.operation_id) as ids from mg_operation m;--结果时CLOB类型
--猜你喜欢,短视频,小伙伴正在看,为我推荐    1,2,3,4

select replace(wm_concat(m.operation_name),',','|') as names,replace(wm_concat(m.operation_id),',','|') as ids from mg_operation m;
--猜你喜欢|短视频|小伙伴正在看|为我推荐   1|2|3|4