达梦Oracle一行拆分多行

列：

SELECT TRIM(REGEXP_SUBSTR(dept_ids_str, '[^,]+', 1, LEVEL)) as dept_id
FROM (
SELECT COALESCE(sf.CONFIG_VALUE, NH_CONF.DEF_VALUE) as dept_ids_str
FROM XJX_CORE.SAAS_INNERSYSTEM_CONFIG NH_CONF
LEFT JOIN XJX_CORE.SAAS_HOS_CONFIG sf
ON NH_CONF.CONFIG_CODE = sf.CONFIG_CODE
WHERE NH_CONF.CONFIG_CODE = 'HIS_POEDIATRICS_DEPT_IDS'
) NH_config
CONNECT BY LEVEL <= REGEXP_COUNT(dept_ids_str, ',') + 1
AND REGEXP_SUBSTR(dept_ids_str, '[^,]+', 1, LEVEL) IS NOT NULL

• CONNECT BY LEVEL <= REGEXP_COUNT(dept_ids_str, ',') + 1 控制循环次数

  • 这个控制最多生成多少行：
  • 如果有3个逗号，就生成4行（LEVEL = 1,2,3,4） ：
  • 这是最大可能的行数

• AND REGEXP_SUBSTR(dept_ids_str, '[^,]+', 1, LEVEL) IS NOT NULL 过滤空值

• 这个确保只保留有效的拆分结果：
• 当LEVEL超过实际元素数量时，REGEXP_SUBSTR返回NULL
• NULL会被过滤掉，不生成对应的行

语法分解

1. CONNECT BY

这是Oracle/达梦的递归查询语法，用于生成层级数据。

• CONNECT BY 用于定义父子关系
• LEVEL 是伪列，表示递归的层级（从1开始）

2. LEVEL <= REGEXP_COUNT(dept_ids_str, ',') + 1

• REGEXP_COUNT(dept_ids_str, ',')：计算字符串中逗号的数量
• 1：因为如果有n个逗号，就有n+1个元素
• LEVEL <= ...：生成从1到n+1的数字序列

示例说明

假设配置字符串是："101,102,103,104"

dept_ids_str = '101,102,103,104'
REGEXP_COUNT('101,102,103,104', ',') = 3
3 + 1 = 4
LEVEL <= 4  // 生成1,2,3,4这4个数字

工作原理

-- 原始数据
SELECT '101,102,103,104' as dept_ids_str FROM DUAL;

-- CONNECT BY生成数字序列
SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 4;
-- 结果：
-- LEVEL
-- -----
-- 1
-- 2
-- 3
-- 4

-- 配合REGEXP_SUBSTR拆分
SELECT 
    REGEXP_SUBSTR('101,102,103,104', '[^,]+', 1, 1) as item1, -- 获取第1个
    REGEXP_SUBSTR('101,102,103,104', '[^,]+', 1, 2) as item2, -- 获取第2个
    REGEXP_SUBSTR('101,102,103,104', '[^,]+', 1, 3) as item3, -- 获取第3个
    REGEXP_SUBSTR('101,102,103,104', '[^,]+', 1, 4) as item4  -- 获取第4个
FROM DUAL;

完整拆分过程

SELECT 
    LEVEL as position,
    REGEXP_SUBSTR('101,102,103,104', '[^,]+', 1, LEVEL) as dept_id
FROM DUAL
CONNECT BY LEVEL <= REGEXP_COUNT('101,102,103,104', ',') + 1;

执行过程：

LEVEL	REGEXP_SUBSTR(字符串, 模式, 1, LEVEL)	结果
1	REGEXP_SUBSTR('101,102,103,104', '[^,]+', 1, 1)	101
2	REGEXP_SUBSTR('101,102,103,104', '[^,]+', 1, 2)	102
3	REGEXP_SUBSTR('101,102,103,104', '[^,]+', 1, 3)	103
4	REGEXP_SUBSTR('101,102,103,104', '[^,]+', 1, 4)	104

处理空值：

-- 需要添加空值检查
CONNECT BY LEVEL <= REGEXP_COUNT(dept_ids_str, ',') + 1
    AND dept_ids_str IS NOT NULL
    AND REGEXP_SUBSTR(dept_ids_str, '[^,]+', 1, LEVEL) IS NOT NULL

性能考虑：

• 对于很长的字符串，这种方法的性能可能下降
• 可以考虑限制最大拆分数量：

CONNECT BY LEVEL <= LEAST(REGEXP_COUNT(dept_ids_str, ',') + 1, 100)  -- 最多拆100个

替代写法（不使用CONNECT BY）：

-- 使用数字表（如果有的话）
SELECT REGEXP_SUBSTR(dept_ids_str, '[^,]+', 1, numbers.n) as dept_id
FROM your_table
JOIN (SELECT 1 as n FROM DUAL UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) numbers
    ON REGEXP_SUBSTR(dept_ids_str, '[^,]+', 1, numbers.n) IS NOT NULL;