데이터베이스 질문과 답변
사용자의 개별 권한을 2주 전에 도움을 받아서 작성하고,
사용자의 Role 에 의한 권한을 따로 작성해서 성공하고,
이제 두개의 계층 쿼리를 사용자별로 모아서 출력하려고 UNION ALL 을
하고, CONNECT BY 를 작성하려고 하니....어떻게 해야 할지 막혔습니다.
이 부분도 도움 주시면 감사하겠습니다.
사용자의 개별 권한의 계층 쿼리:
SELECT *
FROM (SELECT a.usr_id
, a.usr_kr
, b.test_parent_elm_id
, b.test_elm_id
, NULL AS rl_id
, SIGN(c.sel_checked + c.ins_checked + c.updt_checked + c.dlt_checked) checked
, 'Individual' AS Perm_Grp
FROM usr a
CROSS JOIN test_elm b
LEFT OUTER JOIN test_elm_usr c
ON a.usr_id = c.usr_id
AND b.test_elm_id = c.test_elm_id
WHERE a.usr_sts = 1
)
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
AND LEVEL <= 2
ORDER SIBLINGS BY usr_id, test_elm_id
사용자마다 지정된 Role :
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
LEFT OUTER JOIN usr_rl c
ON a.usr_id = c.usr_id
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 prior rl_id = rl_id
AND NVL(checked, 1) != 0
ORDER SIBLINGS BY usr_id, test_elm_id
by 마농
[2018.08.20 10:11:06]
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 SELECT 2, 8, 1, 1, 1, 1 FROM dual
UNION ALL SELECT 102, 8, 0, 0, 0, 0 FROM dual
UNION ALL SELECT 200, 8, 0, 0, 0, 0 FROM dual
UNION ALL SELECT 1, 9, 1, 1, 1, 1 FROM dual
)
, test_elm_usr(usr_id, test_elm_id, sel_checked, ins_checked, updt_checked, dlt_checked) AS
(
SELECT 115, 1, 1, 1, 1, 1 FROM dual
UNION ALL SELECT 115, 2, 1, 1, 1, 1 FROM dual
UNION ALL SELECT 150, 1, 1, 0, 0, 0 FROM dual
UNION ALL SELECT 150, 100, 0, 0, 0, 0 FROM dual
UNION ALL SELECT 150, 2, 1, 0, 0, 0 FROM dual
UNION ALL SELECT 150, 200, 0, 1, 0, 0 FROM dual
UNION ALL SELECT 200, 2, 1, 0, 0, 0 FROM dual
)
SELECT *
FROM (SELECT usr_id, usr_kr, test_parent_elm_id, test_elm_id
, MIN(rl_id) rl_id
, LISTAGG(Perm_Grp, ',') WITHIN GROUP(ORDER BY Perm_Grp) Perm_Grp
FROM (SELECT *
FROM (SELECT a.usr_id
, a.usr_kr
, b.test_parent_elm_id
, b.test_elm_id
, NULL AS rl_id
, SIGN(c.sel_checked + c.ins_checked + c.updt_checked + c.dlt_checked) checked
, 'Individual' AS Perm_Grp
FROM usr a
CROSS JOIN test_elm b
LEFT OUTER JOIN test_elm_usr c
ON a.usr_id = c.usr_id
AND b.test_elm_id = c.test_elm_id
WHERE a.usr_sts = 1
)
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
AND LEVEL <= 2
UNION ALL
SELECT usr_id, usr_kr, test_parent_elm_id, test_elm_id
, LISTAGG(rl_id, ',') WITHIN GROUP(ORDER BY rl_id) rl_id
, MIN(checked) checked
, Per_Grp
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
LEFT OUTER JOIN usr_rl c
ON a.usr_id = c.usr_id
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
)
START WITH test_parent_elm_id IS NULL
AND checked = 1
CONNECT BY PRIOR usr_id = usr_id
AND PRIOR rl_id = rl_id
AND PRIOR test_elm_id = test_parent_elm_id
AND NVL(checked, 1) != 0
AND LEVEL <= 2
GROUP BY usr_id, usr_kr, test_parent_elm_id, test_elm_id, Per_Grp
)
GROUP BY usr_id, usr_kr, test_parent_elm_id, test_elm_id
)
START WITH test_parent_elm_id IS NULL
CONNECT BY PRIOR usr_id = usr_id
AND PRIOR test_elm_id = test_parent_elm_id
ORDER SIBLINGS BY usr_id, test_elm_id
;
by 안떠니
[2018.08.20 13:42:42]
LISTAGG 함수를 Role 를 위한 계층쿼리에만 사용하고,
전체 쿼리에만 사용하셨는데요,
Role 에 사용하신 이유가 무엇인지요.
LISTAGG 함수를 검색해 보면, 블로그만 519건이 리턴되는 걸루 봐서 많이 사용되는 것 같고,
몇개를 둘러봤지만,
http://blog.naver.com/tgmuseum/220664126752
열 데이터를 그룹 지어서 행으로 돌리는 기능을 하는 것 같은데, 위의 쿼리에는 어떤 영향을 주는지
아직 이해를 못하고 있습니다.
전체 쿼리와 두번째 쿼리에만 LISTAGG 함수와 MIN 함수를 사용하신 목적을 알려주시면 감사하겠습니다.
by 마농
[2018.08.20 14:47:46]
1. ListAgg 는 Group By 에서 문자열 합쳐서 보여주는 함수입니다. - 쿼리만 보지 마시고 결과데이터도 함께 보시면 이해하기 어렵지 않을 듯 합니다. - http://gurubee.net/article/55512 2. ListAgg 를 사용한 이유 - 그룹바이로 묶게되면 rl_id 나 Perm_Grp 에 대한 표시를 할 수 없으므로 - ListAgg 를 통해 표현해 주려고 한 것입니다. - 해당 노드가 어떤 경로를 통해 출력되었는지를 표시하려는 의도입니다. - 1번쿼리에서 나온 것인지? Individual - 2번쿼리에서 나온 것인지? Role - 양쪽 모두에서 나온것인지? Individual,Role - 2번쿼리에서 나온것이라면? 어떤 롤로부터 나온 것인지? 8,9 3. ListAgg 를 2단계에 걸쳐서 사용한 이유는? - wm_concat 함수를 이용한다면? Distinct 와 함께 사용이 가능해 매우 유용합니다. - 마지막 단계에 한번만 wm_concat 사용해도 됩니다. - 다만 wm_concat 은 비공식 함수이므로 공식함수인 ListAgg 를 사용했습니다. - ListAgg 에는 Distinct 기능이 없어 미리 중복 제거하기 위해 2단계로 실행한 것입니다.
by 안떠니
[2018.08.22 12:32:49]
데이터를 간단하게 추려서 질문하려고, Role_Desc 참조하는 부분을 제거했었습니다.
LISTAGG 함수로 8,9,10 이렇게 나열하실 줄 모르고.. 또 제가 본의 아니게 실수를 했네요.
제가 목표했던 것은
유저의 individual 에서 나온 목록을 나열하고, 그 다음 role 에서 나온 목록을 나열하는 것이였습니다.
2주전, 처음에 제가 작성해서 보여드렸던 중첩 FOR 문을 기억하실지 모르겠네요.
그렇게 한 이유가 바로 FOR 문으로 사용자를 하나씩 뽑아와서 Individual 목록 출력하고, Role 목록 출력하려고 했던것이죠
질문을 쉽게 하려다가 본의 아니게 잘못 질문을 드렸습니다.
혹시 제가 중첩 FOR 문으로 출력했던 방식도 계층 쿼리로 가능할까요?
감사합니다.
by 마농
[2018.08.22 13:05:29]
저는 유저별, 아이템별로 한행으로 묶어서 출력한 것이구요.
묶어서 출력할 필요 없이 개별 출력하는 거라면?
그냥 UNION ALL 만 하면 원하는 결과가 나오는 것 아닌지요?
by 마농
[2018.08.22 13:19:40]
SELECT *
FROM (SELECT a.*
, ROWNUM rn -- 정렬용 컬럼
FROM (SELECT a.usr_id
, a.usr_kr
, b.test_parent_elm_id
, b.test_elm_id
, NULL AS rl_id
, SIGN(c.sel_checked + c.ins_checked + c.updt_checked + c.dlt_checked) checked
, 'Individual' AS Perm_Grp
FROM usr a
CROSS JOIN test_elm b
LEFT OUTER JOIN test_elm_usr c
ON a.usr_id = c.usr_id
AND b.test_elm_id = c.test_elm_id
WHERE a.usr_sts = 1
) a
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
AND LEVEL <= 2
ORDER SIBLINGS BY usr_id, test_elm_id
)
UNION ALL
SELECT *
FROM (SELECT a.*
, ROWNUM rn -- 정렬용 컬럼
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 Perm_Grp
FROM usr a
CROSS JOIN test_elm b
LEFT OUTER JOIN usr_rl c
ON a.usr_id = c.usr_id
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
) a
START WITH test_parent_elm_id IS NULL
AND checked = 1
CONNECT BY PRIOR usr_id = usr_id
AND PRIOR rl_id = rl_id
AND PRIOR test_elm_id = test_parent_elm_id
AND NVL(checked, 1) != 0
AND LEVEL <= 2
ORDER SIBLINGS BY usr_id, test_elm_id
)
ORDER BY 1, 7, 8 -- usr_id, Perm_Grp, rn
;
by 안떠니
[2018.08.24 22:58:46]
그냥 UNION ALL 로 묶어서는 안되었습니다.
보여주신 코드에서, ROWNUM 이 키인 것으로 보입니다.
이것을 지우고 ORDER BY 에서 제외하고 실행해 보니, 정렬이 안되어서 나왔구요.
이것도 하나의 팁이죠? UNION ALL 로 두 집합을 모아두고,
그 둘을 섞어서(?) 정렬하려고 할 때 쓸 수 있는?
정말 많이 배우고 있습니다.
감사합니다.
) 버튼을 클릭하여 작성 하시면 됩니다.