oracle/mysql sql随记

字符串分割 并 行专列

方式1 
select REGEXP_SUBSTR('01#02#03#04', '[^#]+', 1, rownum) as newport 
  from dual connect by rownum <= REGEXP_COUNT('01#02#03#04', '[^#]+');

方式2
 select aa.name
    from (with a as (select '常规检查,主动检查,专项检查,其他检查' name
    from dual)
    select regexp_substr(name, '[^,]+', 1, rownum) name
    from a
  connect by rownum <= REGEXP_COUNT(name, '[^,]+')) aa;

获取当前时间所在周的起始时间

select trunc(sysdate,'WW')  from dual;

当日分段

/*3 为每3个小时为一次分割,一天分割为8次 24/3=8 */
with tempTable as
 (
   select trunc(sysdate) + 60 * (rownum - 1) * 3 / 1440 startDate,
      trunc(sysdate) + 60 * (rownum) * 3 / 1440 endDate
     from dual
   connect by level <= 8
)
 select * from temptable;

往前推15天

oracle
  select trunc(sysdate - 1- rownum) startDate,trunc(sysdate - rownum) endDate ,rownum from dual connect by level <= 15;
mysql
  select 
  date_sub(CURRENT_DATE, interval (@start_rn:=@start_rn +1)+1 day) startDate,
  date_sub(CURRENT_DATE, interval (@end_rn:=@end_rn+1)  day) endDate
  from (select 1 from mysql.help_topic) a,(select @start_rn:=0,@end_rn:=0) b limit 15

往前推7个周

   select trunc(sysdate - (to_number(to_char(sysdate - 1, 'd')) - 1) -
     (rownum - 1) * 7 ) as startDate,
      trunc(sysdate + (7 - to_number(to_char(sysdate - 1, 'd'))) -
     (rownum - 1) * 7 )as endDate,
     to_number(to_char(sysdate, 'iw')) - rownum + 1 as weekIndex
from dual
connect by level <= 7;

--先闭后开
select 
  trunc(sysdate - (to_number(to_char(sysdate - 1, 'd')) - 1) -
 (rownum - 1) * 7 ) as startDate,
 trunc(sysdate + (7 - to_number(to_char(sysdate - 1, 'd'))) -
 (rownum - 1) * 7+1) as endDate,
 to_number(to_char(sysdate, 'iw')) - rownum + 1 as weekIndex
  from dual
connect by level<= 7;

-- 例子：近7周每周个类型数量统计
with  temp_a  as
( 
    select trunc(sysdate - (to_number(to_char(sysdate - 1, 'd')) - 1) -
     (rownum - 1) * 7 ) as startDate,
      trunc(sysdate + (7 - to_number(to_char(sysdate - 1, 'd'))) -
     (rownum - 1) * 7 )as endDate,
     to_number(to_char(sysdate, 'iw')) - rownum + 1 as weekIndex
from dual
connect by level <= 7
),
temp_a2 as(
   select min(startDate) s1,max(endDate) s2 from temp_a
),
temp_b as (
  select  trunc(a.a_date) as sj,a.aydm_root ay
     from t_zhzx a
     left join temp_a2  a2 on 1=1
    where  a.a_date between a2.s1 and a2.s2
)
select a.startdate,a.enddate,
/*c_200000*/count(decode(b.ay,'200000',1,null)) c_200000,
/*c_210000*/count(decode(b.ay,'210000',1,null)) c_210000,
/*c_220000*/count(decode(b.ay,'220000',1,null)) c_220000
 from temp_a a
left join temp_b b on b.sj between a.startdate and a.enddate
group by a.startdate,a.enddate
order by  a.startdate

往前推15个月

 select 
    add_months(trunc(sysdate,'mm'),0-rownum),
    add_months(trunc(sysdate,'mm'),0-(rownum-1)) -1
  from dual connect by rownum<=15

 select add_months(trunc(sysdate, 'mm'), 1 - rownum) startDate,
        add_months(trunc(sysdate, 'mm'), 2 - rownum) endDate,
        rownum rnIndex
   from dual connect by rownum <=15;

往前推7年

 select  trunc(trunc(sysdate, 'y')-365*(rownum -1) ,'y') startDate,
        trunc(trunc(sysdate, 'y')-365*(rownum-2) ,'y')  endDate,
        rownum rnIndex
   from dual
 connect by rownum <= 7;

统计求和(总计)

select d.dm,
   decode(grouping(d.dm),1,'合计',max(d.mc)) mc,
  nvl(sum(p2.product), 0) "productTotal"
from t_dic_pcs d
left join (  select '1' comp_dm,1 product from dual  ) p2  on p2.comp_dm = d.dm
 where d.fjdm = '320582' 
 group by rollup (d.dm)
 order by 1;

先查看数据文件存放路径，统一路径

select * from dba_data_files;
比如路径为F:\app\oradata\xxx.dbf
把路径中的xxx.dbf换成jnga_01.dbf 然后填入下面datafile ''的引号中

创建表空间

create tablespace jnga
datafile ''
size 50m
autoextend on next 20m
maxsize unlimited;
--创建用户
create user jnga identified by jnga
default tablespace jnga;
--用户授权
grant connect,resource,unlimited tablespace,dba to jnga;

获取所有叶子节点

select t.code,t.name,level lvl,connect_by_isleaf from tableA  t
where connect_by_isleaf =1
start with t.code='xxxx'
connect by prior t.id=t.parent_id;

select t.code,t.name,level lvl,connect_by_isleaf from tableA  t
start with t.code='xxxx' connect by prior t.id=t.parent_id;

select t.code,t.name,level lvl,connect_by_isleaf from tableA  t
start with t.code='xxxx' connect by prior t.parent_id=t.id;

分析性函数 partition by

--row_number() 顺序排序
 select * from (
       select deptid ,row_number() over(partition by deptid order by salary) my_rank ,deptid,USERID,salary       
   from tsaler;
 ) where my_rank=1;

--rank() （跳跃排序，如果有两个第一级别时，接下来是第三级别）
select deptid ,rank() over(partition by deptid order by salary) my_rank,deptid,USERID,salary from tsaler;

--dense_rank（）（连续排序，如果有两个第一级别时，接下来是第二级）
select dense_rank() over(partition by deptid order by salary) my_rank,deptid,USERID,salary from tsaler;

delete from t_qy where rowid in (
select rowid from (select id,rowid rn1,row_number() over(partition by id order by id) rn from t_qy t )
where rn !=1)