数据库中一些用到函数记录

例如：拼接个in的条件
select '(' || chr(39) || REPLACE('a,b,c,d',',',chr(39) || ',' || chr(39)) || chr(39) || ')' from dual;
复制代码

SELECT GROUP_CONCAT(DISTINCT cm.CHART_ACCEPT_CODE SEPARATOR ';') FROM CHAT_MSG cm where cm.CHART_CMD='3' and cm.CHART_FROM_CODE='fhx' group by cm.CHART_FROM_CODE;
SEPARATOR 分隔符
复制代码

select rn,bank_acc,bal,avail_bal
  from (select ROW_NUMBER() OVER(PARTITION BY bank_acc ORDER BY bal_date DESC) rn,bank_acc,bal,avail_bal
              from bis_acc_bal)
 where rn = 1
复制代码

select listagg (fp.item_name, ',') WITHIN GROUP (ORDER BY fp.item_name) item_name
复制代码

SELECT TO_CHAR(SYSDATE - LEVEL + 1, 'YYYY-MM-DD') TODAY
  FROM DUAL
CONNECT BY LEVEL <= 7;
select level from dual connect by level <= 7
复制代码

WITH RECURSIVE cte (TODAY) AS (
 SELECT DATE_SUB(CURDATE(), INTERVAL 5 DAY) FROM DUAL
 UNION ALL
 SELECT DATE_ADD(TODAY, INTERVAL 1 DAY) FROM cte
 WHERE TODAY <= DATE_FORMAT(NOW(), '%Y-%m-%d')
) SELECT * FROM cte
复制代码

例如 
ID in  select '(' || chr(39) || REPLACE('a,b,c,d',',',chr(39) || ',' || chr(39)) || chr(39) || ')' from dual;
复制代码

REGEXP_SUBSTR(String, pattern, position, occurrence, modifier)
string 是字段
pattern 正则表达是
position 起始位置 默认1
occurrence 标识第几个匹配组，默认为1
modifier i不区分大小写 c区分大小写 默认c
复制代码

SELECT REGEXP_SUBSTR('a,b,c,d', '[^,]+', 1, rownum)
  from dual
connect by rownum <= LENGTH('a,b,c,d') -
           LENGTH(regexp_replace('a,b,c,d', ',', '')) + 1;
1是开始的位置,如果不写 connect by rownum 默认只有个a        
复制代码

select sc.id,feb.corpids from FILL_EXCEL_BATCH feb
 left join sys_corp sc on sc.id in (SELECT REGEXP_SUBSTR (feb.corpids, '[^,]+', 1,rownum)
           from dual connect by rownum<=LENGTH (feb.corpids) - LENGTH (regexp_replace(feb.corpids, ',', ''))+1);
复制代码