데이터베이스 질문과 답변
안녕하세요 현재 팀프로젝트에서 oracle에서 mysql 맡은 부분 공부중에 있어
혼자 해결해볼려고 했지만 구글링에도 한계가 있어 질문드립니다..
현재 oracle start with , connect cycle, distinct 관련 해결방법을 찾고있습니다
WITH
ORG AS
(
SELECT
DISTINCT
A.O_ID,
A.O_NM ,
A.U_O_ID ,
A.O_G_CD
FROM
(
SELECT * FROM C
WHERE O_G_CD = #{oGCd}
START WITH UPPER(O_NM) LIKE UPPER('%' || #{oNm} || '%')
AND USE_YN = 'Y'
CONNECT BY O_ID = PRIOR U_O_ID
) a
WHERE
(
#{oDC} <> '01' OR a.o_d_c = #{oDC}
)
START WITH O_ID in ( ''
, #{item}
)
AND A.O_G_CD = #{oGCd}
CONNECT BY NOCYCLE PRIOR O_ID = U_O_ID
)
현재 이부분에서 mysql 전환할때 recursive안에서는 distinct 사용이 불가해서
disticnt 수정한 부분을 바깥에다가 해도되는지..
그리고 conncect cycle or connect no cycle 은 어떤 방식으로 mysql로 녹여내는지 궁금해서 선배님들에게 여쭤봅니다.
->>>mysql
with org as
( with recursive recv as(
SELECT
O_ID,
O_NM ,
U_O_ID ,
O_G_CD
from (
with recursive rec as(
SELECT * FROM C
where UPPER(O_NM) LIKE UPPER(concat('%' , #{oNm} , '%'))
AND O_G_CD = #{oGCd}
union all
SELECT A.* FROM C A ,rec
WHERE rec.O_ID = A.U_O_ID
AND USE_YN = 'Y' )
select * from rec
) a
WHERE ORG_ID in ( ''
, #{item}
)
AND A.O_G_CD = #{oGCd}
UNION ALL
SELECT
b.O_ID,
b.O_NM ,
b.U_O_ID ,
b.O_G_CD
from (
with recursive rec as(
SELECT * FROM C
where UPPER(O_NM) LIKE UPPER(concat('%' , #{oNm} , '%'))
AND O_G_CD = #{oGCd}
union all
SELECT A.* FROM C A ,rec
WHERE rec.O_ID = A.U_O_ID
AND USE_YN = 'Y' )
select * from rec
) b , recv
where rec.org_id = a.upr_org_id
)
select distinct
O_ID,
O_NM ,
U_O_ID ,
O_G_CD
from rec
) o
감사합니다.
원본 오라클 쿼리를 보면 DISTINCT 의 위치가 잘못되었습니다.
DISTINCT 를 서브쿼리 안쪽으로 옮기는게 효율적입니다.
NOCYCLE 은 CONCAT 을 이용해 ID 를 이어붙여 SYS_CONNECT_BY_PATH 를 구현하고
해당 항목과 비교(INSTR 이용)하여 같은 ID 가 반복되지 않도록 하면 됩니다.
MySQL 에서는 대소문자 구별 안하고 비교가 되니 UPPER 는 빼도 됩니다.
제가 실수가 있던거 같습니다. 도움주셔서
감사합니다
WITH org AS
(
SELECT *
FROM (
WITH RECURSIVE rec2 AS
(
WITH RECURSIVE rec1 AS
(
SELECT o_id
, o_nm
, u_o_id
, o_g_cd
, o_d_c
FROM c
WHERE o_g_cd = #{oGCd}
AND INSTR(o_nm, #{oNm}) > 0
AND use_yn = 'Y'
UNION -- Distinct
SELECT c.o_id
, c.o_nm
, c.u_o_id
, c.o_g_cd
, c.o_d_c
FROM rec1 p
INNER JOIN c c
ON c.o_g_cd = p.o_g_cd
AND c.o_id = p.u_o_id
)
SELECT o_id
, o_nm
, u_o_id
, o_g_cd
, o_d_c
, CAST(o_id AS VARCHAR(99)) o_id_path
FROM rec1
WHERE o_id IN ('', #{item}) > 0
UNION ALL
SELECT c.o_id
, c.o_nm
, c.u_o_id
, c.o_g_cd
, c.o_d_c
, CONCAT(p.o_id_path, ',', c.o_id) o_id_path -- Sys_connect_by_path
FROM rec2 p
INNER JOIN rec1 c
ON p.o_id = c.u_o_id
WHERE FIND_IN_SET(c.o_id, p.o_id_path) = 0 -- NoCycle
)
SELECT *
FROM rec2
WHERE (#{oDC} <> '01' OR o_d_c = #{oDC})
) a
)
SELECT *
FROM org
;
너무 어려워서.. 구글 검색하다가 포기했었는데 도움주셔서
감사합니다. 공부열심히하겠습니다.!
혹 부모 자식 navigation역할로 SYS_CONNECT_BY_PATH (ORACLE) 을 function 없이
구현하는게 가능한가요? 제가 concat 으로 해봤을땐 recurive 안에서 돌렸을때 바로 앞 뒤만
가능하고 postgrel 에는 array 구현가능해보여서 msyql 찾아봤는데 지원을 안하는것 같았습니다
msyql에서는 전체적인 경로 1>2>3>4 이런식으로는 function없인 안될 같아서 여쭤봅니다.
다시한번 감사드립니다!!
위에 CONCAT 으로 SYS_CONNECT_BY_PATH 구현 했는데요?
) 버튼을 클릭하여 작성 하시면 됩니다.