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);
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;
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;
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)--二进制00\10\01\11转十进制 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;
