WITH Z_BRANCH AS(
SELECT 'BR1001' BRID, 'BR1001.BR1002.BR1003' BR_LEVEL FROM DUAL
UNION ALL
SELECT 'BR1002' BRID, 'BR1002.BR1003' BR_LEVEL FROM DUAL
UNION ALL
SELECT 'BR1003' BRID, 'BR1001.BR1003' BR_LEVEL FROM DUAL
)
SELECT BRID,
LEVEL,
REGEXP_COUNT(BR_LEVEL, '\.') + 1 AS ROW_CNT,
REGEXP_SUBSTR(BR_LEVEL, '[^.]+', 1, LEVEL) AS S_BRID
FROM Z_BRANCH T
CONNECT BY LEVEL <= REGEXP_COUNT(BR_LEVEL, '\.') + 1
AND T.BRID = PRIOR T.BRID
AND PRIOR DBMS_RANDOM.VALUE > 0;
oracle拆分行
最新推荐文章于 2023-11-09 10:30:23 发布