oracle小知识点

以你之姓@ 2022-07-14 07:58 128阅读 0赞

1. 根据已有的表，创建一个新表

CREATE TABLE table\_name AS SELECT \* FROMold\_table\_name;

2. 分组后获取每组数据的前2条数据

select 
        t.id,t.err_msg ,t.create_date, t.rn
    from 
        (select row_number() over(partition by to_char(create_date, 'yyyymm') order BY ID desc)rn, t_stkkh_idno_info_log.* FROM t_stkkh_idno_info_log) t
    where 
        t.rn<3;

语法解释：

select

列名1，列明2 ...

from

（select row\_number() over(partition by 分组字段 order by 排序字段) rn, 列名a, 列名b... from 表名） t

where

t.rn <= 需要的数量;

其中设置顺序有三种方式：

row\_number() 是没有重复值的排序(即使两条记录相等也是不重复的)，可以利用它来实现分页：1， 2， 3， 。。。  
dense\_rank() 是连续排序，两个第二名仍然跟着第三名：1， 2， 2， 3， 4， 。。。。  
rank()       是跳跃排序，两个第二名下来就是第四名：1， 2， 2， 4， 。。。。

mysql的实现方式：http://www.tuicool.com/articles/eQ3EVv

3. 删除字段中多于8位的数字;  导出客户三方存管失败原因， 部分错误原因包含有客户的银行卡号，但是也有6位的错误代码或者其他的代码信息.为了脱敏，在导出的数据中去除8位以上的数字

使用正则匹配8位数字

SELECT regexp_replace('xxxx12343242354ddd444yy8900987654', '\d{8,}', '') from dual t; --xxxxddd

regexp\_replace(1, 2, 3, 4, 5, 6)

regexp\_replace(字段 str， 被替换字符 a， 替换字符8， 从被替换字符第几次出现的位置开始 b，替换该字符第几次出现位置 c， 是否区分大小写 h)；

1：字段、字符串等（源数据）

2：源数据中需要被替换的字符

3：用什么字符替换源数据中的字符

4：从被替换字符出现的第几次开始替换，

5：以被替换字符第n次出现的位置开始， 替换此后第几次出现的字符。 0表示不限制位置

6：替换时， 是否区分大小写；小写字母"i"表示不区分大小写， 空字符""表示区分大小写

测试数据：

select regexp_replace('ABadAeafarAt','a','0',2,0,'i') from dual; -- 不区分大小写， AB0d0e0f0r0t
    select regexp_replace('ABadAeafarAt','a','0',2,0,'') from dual;  -- 区分大小写， AB0dAe0f0rAt
    
    select regexp_replace('ABadAeafarAt','a','0',2,1,'i') from dual;  -- 从第二个开始，替换之后的第一个 AB0dAeafarAt
    select regexp_replace('ABadAeafarAt','a','0',2,3,'i') from dual; -- 从第二个开始，替换之后的第三个ABadAe0farAt
    select regexp_replace('ABadAeafarAt','a','0',2,4,'i') from dual; -- 从第二个开始，替换之后的第四个ABadAeaf0rAt