oracle逗号分割列并转成多行

缺乏、安全感 2023-09-28 14:07 3阅读 0赞

### oracle逗号分割列并转成多行 ###

*   *  使用场景介绍：
     *  1、业务表A
     *  2、业务单元表B
     *  3、预期结果
     *  4、实现的sql
     *  5、sql分析
     *   *  5.1 函数 REGEXP\_SUBSTR
         *  5.2 案列分析
         *   *  5.2.1 案例1
             *  5.2.1 案例2
         *  5.3 关联REGEXP\_SUBSTR函数
         *   *  \`Length函数\`
             *  \`REGEXP\_REPLACE\` 函数
             *  \`REGEXP\_COUNT\`函数
     *  6、注意
     *  扩展连接

## 使用场景介绍： ##

业务表A中一个字段存放用逗号分割的多个业务单元，现在需要将数据转成一个业务单元对应一个数据。然后关联出业务单元表B，获取更加详细的业务单元信息。

## 1、业务表A ##

![在这里插入图片描述][279b872a12714584992f9947759f0acc.png]

## 2、业务单元表B ##

![在这里插入图片描述][ab6fec4f6edd42189d498a1b55718918.png]

## 3、预期结果 ##

1、按逗号分割后结果  
![在这里插入图片描述][7b1755393ca24b80812a70d41bde4a47.png]  
2、与业务单元表 B关联结果  
![在这里插入图片描述][eaa4cece99094ed8b7ee984aafddc6c3.png]

## 4、实现的sql ##

1、按逗号分割

-- 写法一
    SELECT
    	A.*,
    	REGEXP_SUBSTR( A.L2UNIT_NAME, '[^,]+', 1, L ) AS L2UNIT_NAME_B,
    	L 
    FROM
    	JYZB_DATA_PERM A,
    	( SELECT LEVEL L FROM DUAL CONNECT BY LEVEL <= 100 ) 
    WHERE
    	L ( + ) <= LENGTH( A.L2UNIT_NAME ) - LENGTH( REPLACE ( A.L2UNIT_NAME, ',' ) ) + 1 
    ORDER BY
    	id
    	
    	
    -- 写法二 
    SELECT
    	A.*,
    	REGEXP_SUBSTR( A.L2UNIT_NAME, '[^,]+', 1, L ) AS L2UNIT_NAME_B,
    	L 
    FROM
    	JYZB_DATA_PERM A,
    	( SELECT LEVEL L FROM DUAL CONNECT BY LEVEL <= 100 ) --限制一下level数量，提高查询速度。否则数据较多的情况下，会一直查询不出结果
    WHERE
    	L ( + ) <= regexp_count ( A.L2UNIT_NAME, ',' ) + 1 -- 计算需要分割后的数量
    ORDER BY
    	id

2、关联查询

SELECT
    	jj.*,
    	ij.* 
    FROM
    	(
    	SELECT
    		cp.*,
    		regexp_count ( CP.L2UNIT_NAME, ',' ) + 1 L2UNIT_NAME_count,
    		REGEXP_SUBSTR( CP.L2UNIT_NAME, '[^,]+', 1, L ) AS L2UNIT_NAME_B,
    		L 
    	FROM
    		JYZB_DATA_PERM CP,
    		( SELECT LEVEL L FROM DUAL CONNECT BY LEVEL <= 100 ) 
    	WHERE
    		L ( + ) <= LENGTH( CP.L2UNIT_NAME ) - LENGTH( REPLACE ( CP.L2UNIT_NAME, ',' ) ) + 1 
    	) jj
    	LEFT JOIN invalid_JYZB_BASE_L2UNIT ij ON jj.L2UNIT_NAME_B = ij.L2UNIT_NAME 
    ORDER BY
    	id

## 5、sql分析 ##

### 5.1 函数 REGEXP\_SUBSTR ###

REGEXP\_SUBSTR(String, pattern, position, occurrence, modifier)

`string:`需要进行正则处理的字符串  
`pattern：`进行匹配的正则表达式  
`position：`起始位置，从字符串的第几个字符开始正则表达式匹配（默认为1） 注意：字符串最初的位置是1而不是0  
`occurrence：`获取第几个分割出来的组（分割后最初的字符串会按分割的顺序排列成组）  
`modifier：`模式（‘i’不区分大小写进行检索；‘c’区分大小写进行检索。默认为’c’）针对的是正则表达式里字符大小写的匹配

### 5.2 案列分析 ###

#### 5.2.1 案例1 ####

SELECT REGEXP_SUBSTR('11a22A33a','[^A]+',1,1,'i') AS STR FROM DUAL;

说明：  
![在这里插入图片描述][ca7ee12e2ddc4e4884a39cf7232a8adb.png]

结果： `11`  
分析： 正则表达式是以`A`为标识进行分割，而`'i'`标识不区分大小写，所以结果是`11`，而不是`11a22`

#### 5.2.1 案例2 ####

SELECT REGEXP_SUBSTR('11a22A33a','[^A]+',1,1,'c') AS STR FROM DUAL;

说明：![在这里插入图片描述][71287aee0d6c45d28fd25a6be5b24b4a.png]

结果： `11a22`  
分析： 正则表达式是以`A`为标识进行分割，而`'c'`标识区分大小写，所以结果是`11a22`，而不是`11`

### 5.3 关联REGEXP\_SUBSTR函数 ###

SELECT REGEXP_SUBSTR('11a22A33a','[^A]+',1,LEVEL,'c') AS STR FROM DUAL CONNECT BY LEVEL<=5;

结果：![在这里插入图片描述][e95b7ac2d12b47ce9a25338677b5f883.png]

分析： 把要输出来的第几个子串，通过一个变量level转换成输出多少个子串。level<=5代表的是输出5个，没有的为null。这样显然会输出多余null值，这不是我们想要的。  
优化：

SELECT
    	REGEXP_SUBSTR( '11a22A33a', '[^A]+', 1, LEVEL, 'c' ) AS STR
    FROM
    	DUAL CONNECT BY LEVEL <= length( '11a22A33a' ) - length( REGEXP_REPLACE( '11a22A33a','A','' ) ) + 1;

或者：

SELECT
    	REGEXP_SUBSTR( '11a22A33a', '[^A]+', 1, LEVEL, 'c' ) AS STR 
    FROM
    	DUAL CONNECT BY LEVEL <= ( regexp_count ( '11a22A33a', 'A' ) + 1 )

结果![在这里插入图片描述][401642938d454a3dbd321612b99bb996.png]

[level扩展][level]

#### `Length函数` ####

返回字符的个数

Select length('你好') from dual;  2

#### `REGEXP_REPLACE` 函数 ####

-- 在字符串中搜索正则表达式模式并将该模式的每个匹配项替换为指定字符串
    VARCHAR REGEXP_REPLACE(VARCHAR str, VARCHAR pattern, VARCHAR replacement)

入参  
![在这里插入图片描述][0a84148285394d8c9532367dbf517f6c.png]  
示例

Select REGEXP_REPLACE( '11a22A33a','A','' ) from dual;

结果 — 11a2233a  
![在这里插入图片描述][e1362afe255344298262d39a63145c55.png]

#### `REGEXP_COUNT`函数 ####

`REGEXP_COUNT`用于为正则表达式搜索字符串，且返回正则表达式出现次数

INTEGER REGEXP_COUNT
    (
      srcstr    TEXT,
      pattern   TEXT,
      position  DEFAULT 1
      modifier  DEFAULT NULL
    )

参数  
![在这里插入图片描述][d4689db5e28949ecaafc5b8ddb03a320.png]

示例

-- regexp_count函数计算指定字符串在字符串中出现的个数
    SELECT
    	regexp_count ( '11a22A33a', 'A' ) AS count 
    FROM
    	dual;
    -- 结果 1

## 6、注意 ##

如果粗暴的使用逗号分割数据量比较大的情况下可能会出现查询时间很长。

SELECT
    	A.*,
    	REGEXP_SUBSTR( A.L2UNIT_NAME, '[^,]+', 1, LEVEL ) AS L2UNIT_NAME_B 
    FROM
    	JYZB_DATA_PERM A CONNECT BY LEVEL <= regexp_count ( A.L2UNIT_NAME, ',' ) + 1

在实际使用时，使用了此方式导致sql一直在执行，查询不出结果。  
正确方式

SELECT
    	A.*,
    	REGEXP_SUBSTR( A.L2UNIT_NAME, '[^,]+', 1, L ) AS L2UNIT_NAME_B,
    	L 
    FROM
    	JYZB_DATA_PERM A,
    	( SELECT LEVEL L FROM DUAL CONNECT BY LEVEL <= 100 ) --限制一下level数量，提高查询速度。否则数据较多的情况下，会一直查询不出结果
    WHERE
    	L ( + ) <= regexp_count ( A.L2UNIT_NAME, ',' ) + 1  -- 计算需要分割后的数量
    ORDER BY
    	id

## 扩展连接 ##

[1、Oracle数据库中某个表中的某个字段的值是用逗号隔开的多个值，根据逗号拆分并从关联表中查出数据返回][1_Oracle]  
[2、mysql数据库中某个表中的某个字段的值是用逗号隔开的多个值，根据逗号拆分并从另一个表中查出数据返回][2_mysql]

[279b872a12714584992f9947759f0acc.png]: https://img-blog.csdnimg.cn/279b872a12714584992f9947759f0acc.png
[ab6fec4f6edd42189d498a1b55718918.png]: https://img-blog.csdnimg.cn/ab6fec4f6edd42189d498a1b55718918.png
[7b1755393ca24b80812a70d41bde4a47.png]: https://img-blog.csdnimg.cn/7b1755393ca24b80812a70d41bde4a47.png
[eaa4cece99094ed8b7ee984aafddc6c3.png]: https://img-blog.csdnimg.cn/eaa4cece99094ed8b7ee984aafddc6c3.png
[ca7ee12e2ddc4e4884a39cf7232a8adb.png]: https://img-blog.csdnimg.cn/ca7ee12e2ddc4e4884a39cf7232a8adb.png
[71287aee0d6c45d28fd25a6be5b24b4a.png]: https://img-blog.csdnimg.cn/71287aee0d6c45d28fd25a6be5b24b4a.png
[e95b7ac2d12b47ce9a25338677b5f883.png]: https://img-blog.csdnimg.cn/e95b7ac2d12b47ce9a25338677b5f883.png
[401642938d454a3dbd321612b99bb996.png]: https://img-blog.csdnimg.cn/401642938d454a3dbd321612b99bb996.png
[level]: https://blog.csdn.net/heroicpoem/article/details/123271311
[0a84148285394d8c9532367dbf517f6c.png]: https://img-blog.csdnimg.cn/0a84148285394d8c9532367dbf517f6c.png
[e1362afe255344298262d39a63145c55.png]: https://img-blog.csdnimg.cn/e1362afe255344298262d39a63145c55.png
[d4689db5e28949ecaafc5b8ddb03a320.png]: https://img-blog.csdnimg.cn/d4689db5e28949ecaafc5b8ddb03a320.png
[1_Oracle]: https://blog.csdn.net/qq_31126879/article/details/84861305
[2_mysql]: https://blog.csdn.net/myme95/article/details/82661661