如何按级别连接与两个不同的长度工作?

Question

我试图理解下面的查询

select
name,
country,
completePath,
regex_substr(completePath, '[^|]+', 1, lvl) parentOf
from Table
CROSS JOIN lateral
(
select LEVEL lvl from dual connect by LEVEL <= length(completePath) - length(replace(completePath, '|')) + 1)

completePath值类似于|ABC|EFG|HIG|JKL|LMN

很难理解这个

谁能帮我理解任何小的例子?

感谢

Answer 1

• length(completePath)计数completePath
中的字符数
• length(replace(completePath, '|'))在去掉|字符后计算completePath中的字符数。
• 因此，length(completePath) - length(replace(completePath, '|'))给出了|字符的个数。

它所做的就是给你一个|分隔符字符的数量，这些字符在你的分隔字符串中分隔术语，然后通过添加1，你会得到该分隔字符串中的最大术语数。

例如，如果您有样例数据:

CREATE TABLE table_name (completePath) AS
SELECT 'AB' FROM DUAL UNION ALL
SELECT 'A|B' FROM DUAL UNION ALL
SELECT 'A|B|C' FROM DUAL UNION ALL
SELECT 'ABC|DEF|GHI' FROM DUAL UNION ALL
SELECT '|ABC|DEF|G|HI' FROM DUAL;

:

SELECT completePath,
length(completePath) AS num_chars,
length(replace(completePath, '|')) AS num_chars_no_pipe,
length(completePath) - length(replace(completePath, '|')) + 1 AS max_num_terms,
REGEXP_COUNT(completePath, '[^|]+') AS actual_num_terms
FROM   Table_Name

输出:

ACTUAL_NUM_TERMSA|B|C33|ABC|DEF|G|HI