데이터베이스 질문과 답변
Oracle 쿼리를 Mysql로 변경 중 실력이 부족해 해결하지 못한게 있어 질문 드립니다.
============================================
============================================
검색 결과 특정 자식컬럼의 CATEGORY_ID(자식컬럼) 값을 알면 해당하는 값의 계층 구조는 알 수 있지만
전체 자식컬럼을 기준으로 자식에서 부모로 계층 구성을 할 수 있는 방법은 찾지 못했습니다.
고수님들의 말씀 부탁 드립니다.
WITH TBL AS (
SELECT '1' C_ID, NULL R_ID FROM DUAL UNION ALL
SELECT '11' C_ID, '1' R_ID FROM DUAL UNION ALL
SELECT '111' C_ID, '11' R_ID FROM DUAL UNION ALL
SELECT '112' C_ID, '11' R_ID FROM DUAL UNION ALL
SELECT '12' C_ID, '1' R_ID FROM DUAL UNION ALL
SELECT '121' C_ID, '12' R_ID FROM DUAL UNION ALL
SELECT '122' C_ID, '12' R_ID FROM DUAL UNION ALL
SELECT '123' C_ID, '12' R_ID FROM DUAL UNION ALL
SELECT '13' C_ID, '1' R_ID FROM DUAL UNION ALL
SELECT '2' C_ID, NULL R_ID FROM DUAL UNION ALL
SELECT '21' C_ID, '2' R_ID FROM DUAL UNION ALL
SELECT '211' C_ID, '21' R_ID FROM DUAL UNION ALL
SELECT '22' C_ID, '2' R_ID FROM DUAL UNION ALL
SELECT '221' C_ID, '22' R_ID FROM DUAL UNION ALL
SELECT '222' C_ID, '22' R_ID FROM DUAL
)
-- 정전개
SELECT LEVEL
,C_ID
FROM TBL
START WITH R_ID IS NULL
CONNECT BY R_ID = PRIOR C_ID
ORDER SIBLINGS BY C_ID
-- 역전개
SELECT LEVEL
,C_ID
FROM TBL T
START WITH NOT EXISTS (SELECT 1 FROM TBL WHERE R_ID = T.C_ID)
CONNECT BY PRIOR R_ID = C_ID
ORDER SIBLINGS BY C_ID
Mysql 이라서 CONNECT BY 구문을 사용할 수 없습니다.
WITH TBL AS (
SELECT '1' C_ID, NULL R_ID FROM DUAL UNION ALL
SELECT '11' C_ID, '1' R_ID FROM DUAL UNION ALL
SELECT '111' C_ID, '11' R_ID FROM DUAL UNION ALL
SELECT '112' C_ID, '11' R_ID FROM DUAL UNION ALL
SELECT '12' C_ID, '1' R_ID FROM DUAL UNION ALL
SELECT '121' C_ID, '12' R_ID FROM DUAL UNION ALL
SELECT '122' C_ID, '12' R_ID FROM DUAL UNION ALL
SELECT '123' C_ID, '12' R_ID FROM DUAL UNION ALL
SELECT '13' C_ID, '1' R_ID FROM DUAL UNION ALL
SELECT '2' C_ID, NULL R_ID FROM DUAL UNION ALL
SELECT '21' C_ID, '2' R_ID FROM DUAL UNION ALL
SELECT '211' C_ID, '21' R_ID FROM DUAL UNION ALL
SELECT '22' C_ID, '2' R_ID FROM DUAL UNION ALL
SELECT '221' C_ID, '22' R_ID FROM DUAL UNION ALL
SELECT '222' C_ID, '22' R_ID FROM DUAL
)
-- 상방
SELECT 1 LVL
,T1.C_ID
,T1.C_ID CONN_ID
FROM TBL T1
WHERE NOT EXISTS (SELECT 1 FROM TBL WHERE R_ID = T1.C_ID)
UNION ALL
SELECT 2 LVL
,T2.C_ID
,T1.C_ID || '/' ||
T2.C_ID CONN_ID
FROM TBL T1
,TBL T2
WHERE NOT EXISTS (SELECT 1 FROM TBL WHERE R_ID = T1.C_ID)
AND T2.C_ID = T1.R_ID
UNION ALL
SELECT 3 LVL
,T3.C_ID
,T1.C_ID || '/' ||
T2.C_ID || '/' ||
T3.C_ID CONN_ID
FROM TBL T1
,TBL T2
,TBL T3
WHERE NOT EXISTS (SELECT 1 FROM TBL WHERE R_ID = T1.C_ID)
AND T2.C_ID = T1.R_ID
AND T3.C_ID = T2.R_ID
UNION ALL
SELECT 4 LVL
,T4.C_ID
,T1.C_ID || '/' ||
T2.C_ID || '/' ||
T3.C_ID || '/' ||
T4.C_ID CONN_ID
FROM TBL T1
,TBL T2
,TBL T3
,TBL T4
WHERE NOT EXISTS (SELECT 1 FROM TBL WHERE R_ID = T1.C_ID)
AND T2.C_ID = T1.R_ID
AND T3.C_ID = T2.R_ID
AND T4.C_ID = T3.R_ID
ORDER BY CONN_ID
말씀 주신대로 단계별로 구한다음 unoin 처리 했습니다.
감사합니다.
MS-SQL에 WITH 절에 펑션으로 재귀 호출 방식으로 사용가능합니다.
물론 오라클두 가능하구요..
MS-SQL WITH 재귀 쿼리라고 검색하심 찾을수 있을겁니다..
건승하세요
) 버튼을 클릭하여 작성 하시면 됩니다.