字符串分割 并 行专列
方式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-1
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
----- mysql-2
select
@start_rn,
date_sub(str_to_date('2022-05-10','%Y-%m-%d') , interval (@start_rn:=@start_rn +1)-1 day) startDate
from (select 1 from mysql.help_topic) a,(select @start_rn:=0) b
where @start_rn<=datediff(str_to_date('2022-05-10','%Y-%m-%d'),str_to_date('2022-05-01','%Y-%m-%d'))
往前推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)
