계층쿼리에서 계층과 연관없는 컬럼으로 나누는 SQL 질문 0 3 104

by 안떠니 [SQL Query] [2018.08.18 03:44:53]


지난 번에 배운 계층 쿼리를 활용해서, 이번에는 사용자별 Role 에 따른 접근 목록을 가져오는 쿼리를 작성 중입니다.

일주일 넘게 삽질하고 있지만, 원하는 결과를 못만들고 있어, 질문 올려봅니다.

원하는 결과는, 사용자마다 한개 이상의 Role 이 지정될 수 있기 때문에,

계층 접근 목록을 Role 마다 나눠서 보여주려고 합니다.

현재 제 쿼리는 계층 중간 중간에 섞여서 나오고 있습니다.

 


WITH usr(usr_id, usr_kr, usr_sts) AS
    (
        SELECT 115, 'John', 1 FROM dual UNION ALL
        SELECT 150, 'Kole', 1 FROM dual UNION ALL
        SELECT 200, 'Tony', 0 FROM dual
    ),
     test_elm(test_elm_id, test_parent_elm_id) AS
    (
        SELECT 1,  NULL FROM dual UNION ALL
        SELECT 100,  1 FROM dual UNION ALL
        SELECT 101,  1 FROM dual UNION ALL
        SELECT 102,  1 FROM dual UNION ALL
        SELECT 2,  NULL FROM dual UNION ALL
        SELECT 200,  2 FROM dual UNION ALL
        SELECT 201,  2 FROM dual UNION ALL
        SELECT 202,  2 FROM dual 
    ),
    usr_rl(usr_id, rl_id) AS
    (
        SELECT 115, 8 FROM dual UNION ALL
        SELECT 115, 9 FROM dual UNION ALL
        SELECT 150, 9 FROM dual UNION ALL
        SELECT 200, 7 FROM dual
    ),
    test_elm_rl(test_elm_id, rl_id, sel_checked, ins_checked, updt_checked, dlt_checked) AS
    (
        SELECT   1, 8, 1, 1, 1, 1 FROM dual UNION ALL   --parent
        SELECT   2, 8, 1, 1, 1, 1 FROM dual UNION ALL   --parent
        SELECT 102, 8, 0, 0, 0, 0 FROM dual UNION ALL   --child
        SELECT 200, 8, 0, 0, 0, 0 FROM dual UNION ALL   --child
        SELECT   1, 9, 1, 1, 1, 1 FROM dual             --parent
    )
SELECT *
    FROM (SELECT a.usr_id, a.usr_kr, b.test_parent_elm_id, b.test_elm_id
            , c.rl_id
            , SIGN(d.sel_checked + d.ins_checked + d.updt_checked + d.dlt_checked) AS checked
            , 'Role' AS Per_Grp
            FROM 
                usr a CROSS JOIN test_elm b     -- returns 24 rows (= 3 users x 8 elements )
                LEFT OUTER JOIN usr_rl c
                    ON a.usr_id = c.usr_id      -- returns 32 rows (= 16 elements from one user and 16 elements from two users )
                LEFT OUTER JOIN test_elm_rl d
                    ON c.rl_id = d.rl_id
                        AND b.test_elm_id = d.test_elm_id
                WHERE a.usr_sts = 1 
                ORDER BY a.usr_id, b.test_elm_id
)
START WITH test_parent_elm_id IS NULL
            AND checked = 1
CONNECT BY  prior usr_id = usr_id
        AND prior test_elm_id = test_parent_elm_id
        AND NVL(checked, 1) != 0
ORDER SIBLINGS BY usr_id, test_elm_id

 

 

 

by 안떠니 [2018.08.18 04:03:37]
CONNECT BY  prior usr_id = usr_id
        AND prior test_elm_id = test_parent_elm_id
        AND NVL(checked, 1) != 0

 

이 부분에서 특히 prior usr_id = usr_id 를 이해를 못했는데..
역시나 이해 못한 부분이 문제이네요.

prior rl_id = rl_id 를 추가해 보니 원하는 모습대로 결과 나왔지만,...........
 

prior usr_id  = usr_id 와 prior rl_id = rl_id 
어떤 역할과 의미인지 설명해 주시면 감사하겠습니다.

이해가 잘 안되네요. 같은 값을 비교하라??

CONNECT BY  prior usr_id = usr_id
        AND prior test_elm_id = test_parent_elm_id
        AND prior rl_id = rl_id
        AND NVL(checked, 1) != 0

 


by chrome [2018.08.18 09:53:25]

prior은 앞의 ,이전의 와 같은뜻을 가지고 있습니다. 


 

START WITH test_parent_elm_id IS NULL

            AND checked = 1

/* test_parent_elm_id 가 null 이고 checked =1 인 조건의 row가 [시작row]  */ 

CONNECT BY  prior usr_id = usr_id   

        AND prior test_elm_id = test_parent_elm_id

        AND NVL(checked, 1) != 0

처리순서를 보죠.

1.start with 구문에서 "시작row" 를 찾아냅니다. 1개 일 수도 있고 여러개 일 수도 있습니다. 

2.그 다음 CONNECT BY  prior usr_id = usr_id   조건을 찾습니다. 

prior usr_id 는 "시작row"의 usr_id 컬럼입니다. 이 값과 같은 usr_id 를 가진 row를 찾습니다. 

찾았습니까? 찾았으면 해당 row는 자식 node로 기억됩니다. 다음으로 갑니다.

3. 이번엔 prior usr_id 가 2 에서 찾은 row의 usr_id 컬럼값입니다. 

이 값을 기준으로 역시 조건에 맞는 usr_id 를 가진 컬럼을 찾습니다. 

역시 이번 usr_id는 자식Node로 기억됩니다.

이런식으로 현재 row의 이전 row를 끊임없이 찾아내는 문장입니다. 

 

좀더 설명이 잘되어있거나 예시를 동반한 책등을 찾아보시길 권장합니다. 

 


by 마농 [2018.08.20 16:08:15]

흔히들 계층 조건이 1개 뿐이라고 착각합니다. (PRIOR 자식코드 = 부모코드)
하지만 조건은 복합 조건이 올 수 있습니다.
사용자별로, 롤별로 계층 전개가 별개로 이루어져야 하기 때문에 해당 조건이 추가되는 것입니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입