level oracle 字段名_Oracle 伪列Level的应用

目录导航：

WITH TB AS

(SELECT 'U101' USER_ID, DATE '2016-11-02' START_DT, DATE '2016-11-06' END_DT

FROM DUAL

UNION ALL

SELECT 'U102', DATE '2015-06-01', DATE '2015-06-03' FROM DUAL)

SELECT TB.USER_ID, TB.START_DT + LEVEL - 1

FROM TB

CONNECT BY TB.USER_ID = PRIOR TB.USER_ID

AND LEVEL <= (TB.END_DT - TB.START_DT + 1)

AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL;

WITH TB AS

(SELECT 'U101' USER_ID, '12345' MOBILE, 3 CNT

FROM DUAL

UNION ALL

SELECT 'U102', '33563', 4 FROM DUAL)

SELECT A.USER_ID, A.MOBILE, B.LV

FROM TB A

LEFT JOIN (SELECT TB.USER_ID, LEVEL LV

FROM TB

CONNECT BY TB.USER_ID = PRIOR TB.USER_ID

AND LEVEL <= CNT

AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL) B

ON A.USER_ID = B.USER_ID

SELECT DBMS_RANDOM.VALUE

FROM DUAL

CONNECT BY LEVEL <= 100;

SELECT SYSDATE - LEVEL

FROM DUAL

CONNECT BY LEVEL <= 100;

SELECT TO_DATE('2020/01/02 08:00:00', 'yyyy-MM-dd HH24:mi:ss') +

(LEVEL - 1) DT

FROM DUAL

CONNECT BY LEVEL <= 7;

SELECT SUBSTR('大家好', LEVEL, 1)

FROM DUAL

CONNECT BY LEVEL <= LENGTH('大家好');

WITH TB AS

(SELECT 'U1' USER_ID, '哈喽,世界' STR

FROM DUAL

UNION ALL

SELECT 'U2', '你好啊,地球' FROM DUAL)

SELECT TB.USER_ID, SUBSTR(STR, LEVEL, 1)

FROM TB

CONNECT BY TB.USER_ID = PRIOR TB.USER_ID

AND LEVEL <= LENGTH(STR)

AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL;

WITH tb AS

(SELECT 'a' rn, '1,2,3' i_name

FROM dual

UNION ALL

SELECT 'b', '4,5,6' FROM dual)

SELECT rn, regexp_substr(i_name, '[^,]+', 1, LEVEL)

FROM tb

CONNECT BY PRIOR dbms_random.value IS NOT NULL

AND PRIOR rn = rn

AND LEVEL <= length(i_name) - length(REPLACE(i_name, ',', '')) + 1;

标签：Level,伪列,USER,DUAL,Oracle,LEVEL,TB,ID,SELECT

来源： https://www.cnblogs.com/LuckyZLi/p/12194924.html