oracle常用函数记录

--字符串挨个输出：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,
       regexp_replace(t.login_name, '[^0-9]', '') 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, '[^.]+', 1, 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, level, 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 后七个月,
       to_char(add_months(sysdate, -7), 'yyyy-MM-dd') as 前七个月       

  from dual;

select sysdate,
       to_char(sysdate, 'hh24') as 时,
       to_char(sysdate, 'mi') as 分,
       to_char(sysdate, 'ss') as 秒,
       to_char(sysdate, 'dd') as 日,
       to_char(sysdate, 'mm') as 月,
       to_char(sysdate, 'yyyy') as 年,
       to_char(sysdate, 'ddd') as 年内第几天,
       trunc(sysdate, 'dd') as 一天之始,
       trunc(sysdate, 'day') as 周初,
       trunc(sysdate, 'mm') as 月初,
       last_day(sysdate) as 月末,
       to_char(sysdate, 'day') as 周几,
       to_char(sysdate, '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 t
 where t.tran_amt > 0
 group by rollup(t.merchant_id, t.terminal_id)
 order by t.merchant_id, t.terminal_id;