CONNECT BY LEVEL

database/ORACLE 2016. 11. 25. 12:49
반응형

DEPTH가 결과값이라고 보면 됨.

 

쿼리1) DEPTH가 2단계

SELECT LEVEL
FROM DUAL
CONNECT BY LEVEL <= 2;

 

쿼리1-결과) - 2개의 값이 나옴

--------------------------------------

1
2

--------------------------------------

 

쿼리2)

SELECT LEVEL
FROM DUAL
CONNECT BY LEVEL <= 3;

쿼리2-결과)

--------------------------------------

1
2
3

--------------------------------------

 

궈리3) TREE의 갯수가 1이고 DEPTH가 2단계

SELECT LEVEL
FROM (
    SELECT LEVEL
    FROM DUAL
    CONNECT BY LEVEL <= 1
)
CONNECT BY LEVEL <= 2;

 

쿼리3-결과)

--------------------------------------

1
2

--------------------------------------

 

쿼리4)TREE의 갯수가 1이고 DEPTH가 3단계

SELECT LEVEL
FROM (
    SELECT LEVEL
    FROM DUAL
    CONNECT BY LEVEL <= 1
)
CONNECT BY LEVEL <= 3;

쿼리4-결과)

--------------------------------------

1
2
3

--------------------------------------

 

쿼리5)TREE의 갯수가 2이고 DEPTH가 1단계

SELECT LEVEL
FROM (
    SELECT LEVEL
    FROM DUAL
    CONNECT BY LEVEL <= 2
)
CONNECT BY LEVEL <= 1;

 

쿼리5-결과)

--------------------------------------

1
1

--------------------------------------

 

쿼리6) TREE의 갯수가 3이고 DEPTH가 1단계

SELECT LEVEL
FROM (
    SELECT LEVEL
    FROM DUAL
    CONNECT BY LEVEL <= 3
)
CONNECT BY LEVEL <= 1;

 

쿼리6-결과)

--------------------------------------

1
1
1

--------------------------------------

 

쿼리7) TREE의 갯수가 2이고 DEPTH가 2단계

SELECT LEVEL
FROM (
    SELECT LEVEL
    FROM DUAL
    CONNECT BY LEVEL <= 2
)
CONNECT BY LEVEL <= 2;

쿼리7-결과)

--------------------------------------

1
2
2
1
2
2

--------------------------------------

쿼리8) TREE의 갯수가 2이고 DEPTH가 3단계

SELECT LEVEL
FROM (
    SELECT LEVEL
    FROM DUAL
    CONNECT BY LEVEL <= 2
)
CONNECT BY LEVEL <= 3;

쿼리8-결과)

--------------------------------------

1
2
3
3
2
3
3
1
2
3
3
2
3
3

--------------------------------------

쿼리9)TREE의 갯수가 2이고 DEPTH가 4단계

SELECT LEVEL
FROM (
    SELECT LEVEL
    FROM DUAL
    CONNECT BY LEVEL <= 2
)
CONNECT BY LEVEL <= 4;

 

쿼리9-결과)

--------------------------------------

1
2
3
4
4
3
4
4
2
3
4
4
3
4
4
1
2
3
4
4
3
4
4
2
3
4
4
3
4
4

--------------------------------------

 

쿼리11) TREE의 갯수가 3이고 DEPTH가 2단계

SELECT LEVEL
FROM (
    SELECT LEVEL
    FROM DUAL
    CONNECT BY LEVEL <= 3
)
CONNECT BY LEVEL <= 2;

 

쿼리11-결과)

--------------------------------------

1
2
2
2
1
2
2
2
1
2
2
2

--------------------------------------

 

쿼리12)TREE의 갯수가 4이고 DEPTH가 2단계

SELECT LEVEL
FROM (
    SELECT LEVEL
    FROM DUAL
    CONNECT BY LEVEL <= 4
)
CONNECT BY LEVEL <= 2;

 

쿼리12-결과)

--------------------------------------

1
2
2
2
2
1
2
2
2
2
1
2
2
2
2
1
2
2
2
2

--------------------------------------

 

참고)

http://donatm.blog.me/20133117593

http://blog.naver.com/ooabc/20207191941

 

 

반응형
Posted by 공간사랑
,