안녕하세요.
큐브리드 쿼리를 마리아DB로 수정중 문의가 있습니다.
START WITH ~ CONNECT BY PRIOR 구문이 서브쿼리 안에 있는경우에도
마리아디비의 WITH RECURSIVE ~ 구문으로 수정이 가능한가요?
SELECT A.* FROM (SELECT ROWNUM AS ROWNUMBER, T.POST_NO, T.BBS_NO, B.MU_NM, T.USE_YN FROM (SELECT A.* FROM TB_BOARD_POST A WHERE A.DEL_YN != 'Y' START WITH A.POST_PN_NO = 0 CONNECT BY PRIOR A.POST_NO = A.POST_PN_NO ORDER BY A.POST_GP_NO ASC ) T JOIN TB_MENU B ON T.MU_NO = B.MU_NO AND B.DEL_YN != 'Y' WHERE 1=1 ) A ORDER BY ROWNUMBER desc
WITH RECURSIVE t AS ( SELECT a.* FROM tb_board_post a WHERE a.post_pn_no = 0 UNION ALL SELECT a.* FROM t p INNER JOIN tb_board_post a ON p.post_no = a.post_pn_no ) SELECT ROW_NUMBER() OVER(ORDER BY a.post_gp_no) rn , a.post_no , a.bbs_no , b.mu_nm , a.use_yn FROM t a INNER JOIN tb_menu b ON a.mu_no = b.mu_no WHERE a.del_yn != 'Y' AND b.del_yn != 'Y' ORDER BY rn DESC ;