oracle常用函数记录

发布时间：2020-12-12 14:03:08 所属栏目：百科来源：网络整理

导读：--字符串挨个输出：substr、connect by level select t.login_name,substr(t.login_name,level,1) from (select t.login_name from sys_user t where t.login_name = 'liliangguo') t connect by level = length(t.login_name); --去除字符串中某个字符：reg

--字符串挨个输出：substr、connect by level
select t.login_name,substr(t.login_name,level,1)
from (select t.login_name
from sys_user t
where t.login_name = 'liliangguo') t

connect by level <= length(t.login_name);

--去除字符串中某个字符：regexp_replace、translate
select t.id,regexp_replace(t.login_name,'[l]')
from sys_user t
where t.login_name = 'liliangguo';

select t.password,regexp_replace(t.password,'[^0-9]','') as a
from sys_user t
where t.login_name = 'liliangguo';

select t.password,
translate(t.password,'0123456789abcdefg','0123456789') as a
from sys_user t
where t.login_name = 'liliangguo';

--通过正则表达式提取字符和数字：regexp_replace
select t.id,
regexp_replace(t.login_name,'[0-9]','') as name,'') as no

from sys_user t;

--分组求合计的同时提取信息：listagg(t.login_name,',') within group(order by t.login_name)
select t.office_id,
sum(t.login_fail_num) as login_fail_num_sum,
listagg(t.login_name,') within group(order by t.login_name) as login_totoal
from sys_user t
group by t.office_id;

select regexp_substr(login_totoal,'[^,]+',1,2)
from (select t.office_id,
sum(t.login_fail_num) as login_fail_num_sum,
listagg(t.login_name,') within group(order by t.login_name) as login_totoal
from sys_user t
group by t.office_id) v;

select regexp_substr(t.login_ip,'[^.]+',4) from sys_user t;

--字符串挨个提取，然后排序合并：listagg(v.a) within group(order by v.a)
select v.password,listagg(v.a) within group(order by v.a) as login_totoal
from (select t.password,substr(t.password,1) as a
from (select t.password
from sys_user t
where t.login_name = 'liliangguo') t
connect by level <= length(t.password)) v
group by v.password;

--行后新增累计值列：sum(t.login_fail_num) over(order by t.login_name)
select t.login_name,
t.login_fail_num,
sum(t.login_fail_num) over(order by t.login_name)
from sys_user t

order by t.login_name;

--排序后获取上下行的值
select t.login_name,
t.create_date,
lead(t.create_date) over(order by t.create_date) as lead_create_date,
lag(t.create_date) over(order by t.create_date) as lag_create_date from sys_user t;

--日期相关

select to_char(sysdate,'yyyy-MM-dd') as nowdate,
to_char(sysdate + 7,'yyyy-MM-dd') as 后七天,
to_char(sysdate - 7,'yyyy-MM-dd') as 前七天,
to_char(add_months(sysdate,7),'yyyy-MM-dd') as 后七个月,-7),'yyyy-MM-dd') as 前七个月

from dual;

select sysdate,
to_char(sysdate,'hh24') as 时,'mi') as 分,'ss') as 秒,'dd') as 日,'mm') as 月,'yyyy') as 年,'ddd') as 年内第几天,
trunc(sysdate,'dd') as 一天之始,'day') as 周初,'mm') as 月初,
last_day(sysdate) as 月末,'day') as 周几,'month') as 月份

from dual;

--小计加小计的合计
select nvl(t.office_id,'total') as office_id,
sum(t.login_fail_num) as n,
sum(t.is_reset_pwd) as m
from sys_user t
group by rollup(t.office_id)

order by t.office_id;

--按商户、终端分组求交易金额和手续费的小计跟合计
select case
when (grouping(t.merchant_id) = 1 and grouping(t.terminal_id) = 1) then
'总计'
when (grouping(t.merchant_id) = 0 and grouping(t.terminal_id) = 1) then
'小计'
else
t.merchant_id
end as merchant_id,
t.terminal_id,
sum(t.tran_amt) as n,
sum(t.void_amt) as m
from cur_tran_ls t
where t.tran_amt > 0
group by rollup(t.merchant_id,t.terminal_id)

order by t.merchant_id,t.terminal_id;

或者加入grouping_id也一样的结果：

--按商户、终端分组求交易金额和手续费的小计跟合计select case grouping_id(t.merchant_id,t.terminal_id)--二进制0010111转十进制 when 3 then '总计' when 1 then '小计' else t.merchant_id end as merchant_id,t.terminal_id,sum(t.tran_amt) as n,sum(t.void_amt) as m from cur_tran_ls twhere t.tran_amt > 0group by rollup(t.merchant_id,t.terminal_id)order by t.merchant_id,t.terminal_id;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!