Oracle SQL正则拆分字符串出现重复记录问题排查

嘿，我懂你遇到的这个坑——用CONNECT BY拆分字符串时跑出一堆重复数据，这其实是Oracle递归查询的常见问题，我给你拆解下原因和解决办法：

问题根源 #

你写的查询里，CONNECT BY子句只判断了拆分结果不为空，但没限制递归只针对当前行执行。Oracle的递归查询默认会把所有行的迭代结果做笛卡尔积组合，比如你的两行数据，第一行拆出3个值，第二行拆出3个值，最终就会生成3×3=9条记录，其中大量是重复的id和拆分值。

修正方案 #

方案一：给递归加上行关联限制 #

在CONNECT BY里加PRIOR id = id确保只对当前行的id递归，同时加PRIOR SYS_GUID() IS NOT NULL打破循环引用（不然Oracle会报ORA-01436循环错误）：

SELECT id, regexp_substr(liked, '[^,]+', 1, level) AS liked_item
FROM temp
CONNECT BY PRIOR id = id 
       AND PRIOR SYS_GUID() IS NOT NULL
       AND regexp_substr(liked, '[^,]+', 1, level) IS NOT NULL
ORDER BY id;

方案二：用REGEXP_COUNT控制层级数（更高效） #

先通过REGEXP_COUNT算出每行的拆分数量，再限制递归层级不超过这个数，同样加上行关联和防循环条件：

SELECT id, regexp_substr(liked, '[^,]+', 1, level) AS liked_item
FROM temp
CONNECT BY level <= REGEXP_COUNT(liked, '[^,]+')
       AND PRIOR id = id
       AND PRIOR SYS_GUID() IS NOT NULL
ORDER BY id;

方案三：Oracle 12c+用CROSS APPLY（更直观） #

如果你的Oracle版本是12c及以上，推荐用CROSS APPLY，逻辑更清晰，不用处理递归循环的问题：

SELECT t.id, s.liked_item
FROM temp t
CROSS APPLY (
    SELECT regexp_substr(t.liked, '[^,]+', 1, level) AS liked_item
    FROM dual
    CONNECT BY level <= REGEXP_COUNT(t.liked, '[^,]+')
) s
ORDER BY t.id;

验证结果 #

执行上面任意一个修正后的语句，都会得到你期望的结果：

1 | java
1 | c
1 | .net
2 | oracle
2 | hadoop
2 | ruby

内容的提问来源于stack exchange，提问作者Learn Hadoop