当前位置：首页 > news >正文

LISTAGG 用于将多行数据聚合为单行字符串（拼接），而与其功能相反的需求是将单行字符串按指定分隔符拆分为多行数据

news 2026/5/25 16:09:25

LISTAGG 用于将多行数据聚合为单行字符串（拼接），而与其功能相反的需求是 将单行字符串按指定分隔符拆分为多行数据。Oracle 中没有直接对应的内置内置函数**，但可以通过以下方法实现类似效果：

方法1：使用 `CONNECT BY` 层级查询（适用于 Oracle 11g+）

通过层级层级查询结合字符串函数（SUBSTR、INSTR），将字符串按分隔符拆分多行。

示例：

假设有一个包含拼接字符串的表 DEPT_EMP：

DEPTNO	EMPLOYEES
10	CLARK,KING,MILLER
20	SMITH,JONES

按逗号拆分 EMPLOYEES 列：

SELECT DEPTNO,TRIM(SUBSTR(',' || EMPLOYEES || ',', LEVEL + 1, INSTR(',' || EMPLOYEES || ',', ',', 1, LEVEL + 1) - LEVEL - 1)) AS EMPLOYEE
FROM DEPT_EMP
CONNECT BY LEVEL <= LENGTH(EMPLOYEES) - LENGTH(REPLACE(EMPLOYEES, ',', '')) + 1  -- 计算分隔符数量，确定行数AND PRIOR DEPTNO = DEPTNO  -- 按分组保持关联AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL;  -- 避免循环（关键）

结果：

DEPTNO	EMPLOYEE
10	CLARK
10	KING
10	MILLER
20	SMITH
20	JONES

方法2：使用 `REGEXP_SUBSTR` + 数字序列（适用于 Oracle 11g+）

通过生成数字序列（模拟行数），结合正则表达式 REGEXP_SUBSTR 拆分字符串。

示例：

-- 先生成足够的数字序列（如 1 到 100，可根据实际需求调整）
WITH NUM_SEQ AS (SELECT LEVEL AS NUM FROM DUAL CONNECT BY LEVEL <= 100
)
SELECT DEPTNO,TRIM(REGEXP_SUBSTR(EMPLOYEES, '[^,]+', 1, NUM)) AS EMPLOYEE
FROM DEPT_EMP, NUM_SEQ
WHERE NUM <= LENGTH(EMPLOYEES) - LENGTH(REPLACE(EMPLOYEES, ',', '')) + 1  -- 限制行数为分隔符数量+1
ORDER BY DEPTNO, NUM;

结果同上，此方法更简洁，通过 REGEXP_SUBSTR(字符串, '[^分隔符]+', 1, 序号) 提取第 N 个元素。

方法3：使用 Oracle 12c+ 的 `JSON_TABLE`（适合复杂场景）

若字符串格式规则，可借助 JSON 函数拆分（将字符串转为 JSON 数组，再解析为行）。

示例：

SELECT DEPTNO,EMPLOYEE
FROM DEPT_EMP,JSON_TABLE('["' || REPLACE(EMPLOYEES, ',', '","') || '"]',  -- 将逗号替换为 JSON 数组格式'$[*]' COLUMNS (EMPLOYEE VARCHAR2(50) PATH '$'));

说明：