如何理解不同长度下CONNECT BY LEVEL的工作机制及SQL查询示例

这个SQL的核心作用是：把completePath字段中用|分隔的每个非空节点单独拆成一行，同时保留原表的name、country字段。下面分步拆解：

1. 子查询：生成连续数字序列（LEVEL值） #

子查询代码：

select LEVEL lvl from dual connect by LEVEL <= length(completePath) - length(replace(completePath, '|')) + 1

它的作用是为每个completePath字符串生成一组连续整数，整数的数量等于按|分割后的所有元素个数（包括空字符串）。

计算逻辑示例（以|ABC|EFG|HIG|JKL|LMN为例）： #

• length(completePath)：原字符串的总长度（示例中是20）
• length(replace(completePath, '|', ''))：去掉所有|后的字符串长度（示例中是15）
• 两者相减得到字符串中|的数量：20-15=5
• 加1后得到按|分割的总元素数：5+1=6 → 子查询会生成lvl值：1、2、3、4、5、6

2. REGEX_SUBSTR：提取第N个非空节点 #

函数代码：

regex_substr(completePath, '[^|]+', 1, lvl) parentOf

参数说明：

• [^|]+：正则表达式，匹配一个或多个不是|的字符（即非空的节点）
• 第3个参数1：从字符串的第1个字符开始匹配
• 第4个参数lvl：取第lvl个匹配到的结果

示例对应结果： #

对于|ABC|EFG|HIG|JKL|LMN：

• lvl=1 → 第一个匹配的非空节点：ABC
• lvl=2 → 第二个匹配的非空节点：EFG
• lvl=3 → HIG
• lvl=4 → JKL
• lvl=5 → LMN
• lvl=6 → 没有匹配到非空节点，返回null

3. CROSS JOIN LATERAL：关联原表与拆分结果 #

CROSS JOIN LATERAL的作用是：原表的每一行，都会和子查询生成的所有lvl值做关联，把一行拆成多行，每行对应一个节点。

最终效果示例 #

假设原表有一行数据：

查询返回的结果会是：

（如果不需要null的行，可以在查询末尾加WHERE parentOf IS NOT NULL过滤）

内容的提问来源于stack exchange，提问作者Oxana Grey