我们日常维护数据库时，通常会有各种各样的需求。

就在前几天，刚好有一位朋友请教sql问题，正好温习一下。

这儿有这么一段数据（id，数据），转换之前如下：

转换之后：

那么如何通过一个sql来完成呢？

首先不着急写sql，我们先想想实现的思路

1、数据查询，【id+原始数据】查出来

2、数据展开，就是把数据拆分出来

3、准备好关联数据，进行关联查询出中文名称

4、查询出来的中文数据再按照原来的格式进行拼接合并

5、最后就是更新回原来的字段或其他字段

上面已经有了查询数据，接下来我们进行数据拆分

可以看到上面已经把每一项的数据都拆分出来，每个id对应了三条数据，sql如下：

SELECT
NOTICE_ID,
REGEXP_SUBSTR(NOTICE_TYPE, '[^,]+', 1, LEVEL) AS NOTICE_TYPE_SPLIT
FROM
SYS_NOTICE
CONNECT BY
REGEXP_SUBSTR(NOTICE_TYPE, '[^,]+', 1, LEVEL) IS NOT NULL
AND PRIOR NOTICE_ID = NOTICE_ID
AND PRIOR SYS_GUID() IS NOT NULL

下面是准备好的关联中文的数据，此时就可以进行关联查询。

关联查询，可以看到每个数据对应的中文已经查询出来

上图sql如下：

WITH project AS (
	SELECT
	NOTICE_ID,
	REGEXP_SUBSTR(NOTICE_TYPE, '[^,]+', 1, LEVEL) AS NOTICE_TYPE_SPLIT
	FROM
	SYS_NOTICE
	CONNECT BY
	REGEXP_SUBSTR(NOTICE_TYPE, '[^,]+', 1, LEVEL) IS NOT NULL
	AND PRIOR NOTICE_ID = NOTICE_ID
	AND PRIOR SYS_GUID() IS NOT NULL
	
)
,projec2 AS (
	
	SELECT a.*,NVL(b.DICT_LABEL,a.NOTICE_TYPE_SPLIT) AS DICT_LABEL
	FROM project a
	LEFT JOIN SYS_DICT_DATA b ON a.NOTICE_TYPE_SPLIT = b.DICT_VALUE  AND b.DICT_TYPE ='sys_oper_type'

)
SELECT * FROM projec2

数据已经转换成功，接下来就是合并拼接中文了，sql如下：

WITH project AS (
	SELECT
	NOTICE_ID,
	REGEXP_SUBSTR(NOTICE_TYPE, '[^,]+', 1, LEVEL) AS NOTICE_TYPE_SPLIT
	FROM
	SYS_NOTICE
	CONNECT BY
	REGEXP_SUBSTR(NOTICE_TYPE, '[^,]+', 1, LEVEL) IS NOT NULL
	AND PRIOR NOTICE_ID = NOTICE_ID
	AND PRIOR SYS_GUID() IS NOT NULL
	
)
,projec2 AS (
	
	SELECT a.*,NVL(b.DICT_LABEL,a.NOTICE_TYPE_SPLIT) AS DICT_LABEL
	FROM project a
	LEFT JOIN SYS_DICT_DATA b ON a.NOTICE_TYPE_SPLIT = b.DICT_VALUE  AND b.DICT_TYPE ='sys_oper_type'

)
,projec3 AS (

	SELECT a.NOTICE_ID,LISTAGG(a.DICT_LABEL,',') zhongwen FROM projec2 a GROUP BY a.NOTICE_ID
	
)
SELECT * FROM projec3

效果：

最后一步就是把转换后的数据进行关联查询更新

终极sql，至此，整个更新已经完成

整个过程主要用到两个关键函数

1、拆分函数：REGEXP_SUBSTR

2、合并函数：LISTAGG

UPDATE SYS_NOTICE SET NOTICE_TYPE = (

SELECT ZHONGWEN FROM 
(
	 	WITH project AS (
			SELECT
		    NOTICE_ID,
		    REGEXP_SUBSTR(NOTICE_TYPE, '[^,]+', 1, LEVEL) AS NOTICE_TYPE_SPLIT
			FROM
		    SYS_NOTICE
			CONNECT BY
		    REGEXP_SUBSTR(NOTICE_TYPE, '[^,]+', 1, LEVEL) IS NOT NULL
		    AND PRIOR NOTICE_ID = NOTICE_ID
		    AND PRIOR SYS_GUID() IS NOT NULL
		    
		)
		,projec2 AS (
			
			SELECT a.*,NVL(b.DICT_LABEL,a.NOTICE_TYPE_SPLIT) AS DICT_LABEL
			FROM project a
			LEFT JOIN SYS_DICT_DATA b ON a.NOTICE_TYPE_SPLIT = b.DICT_VALUE  AND b.DICT_TYPE ='sys_oper_type'
		
		)
		,projec3 AS (
		
			SELECT a.NOTICE_ID,LISTAGG(a.DICT_LABEL,',') zhongwen FROM projec2 a GROUP BY a.NOTICE_ID
			
		)SELECT * FROM projec3
) b
WHERE SYS_NOTICE.NOTICE_ID = b.NOTICE_ID 

)