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

一起养成写作习惯！这是我参与「掘金日新计划 · 4 月更文挑战」的第2天，点击查看活动详情。

1.oracle、mysql 字符串拼接

oracle:字符串拼接用||或者CONCAT,最好用||

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

效果

mysql:concat、GROUP_CONCAT

查询 fhx发过消息的用户

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 分隔符

2.oracle 获取最新数据

2.1获取最新数据的方式最基本的方式，一般是按照时间desc 然后通过rownum获取第一条，但是如果需要分组函数的话还是麻烦的

2.2这里用函数 ROW_NUMBER() OVER(PARTITION BY 分组取数列 ORDER BY 排序列 DESC) 可以做到分组排序结果

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

3.oracle 分组后合并

3.1 wm_concat 可以做到分组后列的合并，在新版的oracle中已经被弃用了（因为我本地用了这个函数，结果测试环境跑不通才发现新版本已经没有了）

3.2 listagg

select listagg (fp.item_name, ',') WITHIN GROUP (ORDER BY fp.item_name) item_name

4.oracle 获取连续日期，或数字

SELECT TO_CHAR(SYSDATE - LEVEL + 1, 'YYYY-MM-DD') TODAY
  FROM DUAL
CONNECT BY LEVEL <= 7;

select level from dual connect by level <= 7

5.mysql 获取连续日期

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

6.oracle in 中带运算符的处理

刚开始的时候我想用字符串拼接和replace处理 in 的但是没效果,如下面的例子

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

没效果

后续采用的方法 REGEXP_SUBSTR

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);