데이터베이스 질문과 답변
안녕하세요.
큐브리드 START WITH쿼리를 마리아디비로 바꿔서 사용하려는데
서브쿼리로 되어있어서 그런지 c.lvl 필드를 찾을수가 없다고 나오는데 어떤식으로 수정하면될까요?
큐브리드
SELECT MU_NO,
ST_NO,
MU_PN_NO,
LEVEL AS LVL,
(SELECT COUNT(*) CNT
FROM TB_ROLE_REF
WHERE MU_NO = T.MU_NO)
AS GROUP_AUTH_CNT
FROM (SELECT A.*
FROM TB_MENU A
JOIN TB_SITE B
ON A.ST_NO = B.ST_NO
AND A.USE_YN = 'Y'
AND B.USE_YN = 'Y'
) T
START WITH T.MU_PN_NO = 0
CONNECT BY PRIOR T.MU_NO = T.MU_PN_NO
ORDER SIBLINGS BY T.ORD
마리아디비
WITH RECURSIVE cte AS
(
SELECT
1 AS LVL,
MU_NO,
MU_PN_NO,
MU_NM,
(SELECT COUNT(*) CNT
FROM TB_ROLE
WHERE MU_NO = T.MU_NO)
AS GROUP_AUTH_CNT
FROM (SELECT A.*
FROM TB_MENU A
JOIN TB_SITE B
ON A.ST_NO = B.ST_NO
AND A.USE_YN = 'Y'
AND B.USE_YN = 'Y'
AND A.MU_PN_NO = 0
) T
UNION ALL
SELECT
c.lvl + 1 AS LVL,
MU_NO,
MU_PN_NO,
MU_NM,
(SELECT COUNT(*) CNT
FROM TB_ROLE_REF
WHERE MU_NO = T.MU_NO)
AS GROUP_AUTH_CNT
FROM (SELECT A.*
FROM TB_MENU A
INNER JOIN cte c
ON A.MU_PN_NO = c.MU_NO
JOIN TB_SITE B
ON A.ST_NO = B.ST_NO
AND A.USE_YN = 'Y'
AND B.USE_YN = 'Y'
) T
)
SELECT *
FROM cte
;
by 마농
[2021.09.24 15:01:29]
오류 원인 : c 를 조인한 후 a.* 만 조회함. c.lvl 도 함께 조회해야 함
a.* 를 시용하지 말고 필요한 항목만 나열해야 함 (a.mu_no, a.st_no, a.mu_pn_no, a.ord)
불필요한 인라인뷰를 줄여야 함. T
WITH RECURSIVE cte AS
(
SELECT a.mu_no
, a.st_no
, a.mu_pn_no
, 1 lvl
, (SELECT COUNT(*) FROM tb_role_ref WHERE mu_no = a.mu_no) group_auth_cnt
, LPAD(a.ord, 3, '0') ord
FROM tb_menu a
INNER JOIN tb_site b
ON a.st_no = b.st_no
WHERE a.use_yn = 'Y'
AND b.use_yn = 'Y'
AND a.mu_pn_no = 0
UNION ALL
SELECT a.mu_no
, a.st_no
, a.mu_pn_no
, c.lvl + 1 lvl
, (SELECT COUNT(*) FROM tb_role_ref WHERE mu_no = a.mu_no) group_auth_cnt
, CONCAT(c.ord, LPAD(a.ord, 3, '0')) ord
FROM cte c
INNER JOIN tb_menu a
ON c.mu_no = a.mu_pn_no
INNER JOIN tb_site b
ON a.st_no = b.st_no
WHERE a.use_yn = 'Y'
AND b.use_yn = 'Y'
)
SELECT *
FROM cte
ORDER BY ord
;
by 밍밍밍밍
[2021.09.24 15:17:55]
감사합니다!
) 버튼을 클릭하여 작성 하시면 됩니다.