데이터베이스 질문과 답변
오라클 가계도 조회 쿼리 문의
0
6
548
by 쫀득이 [2020.08.22 16:05:59]
안녕하세요. 쿼리 고수님들께 자문을 구하고자 질문 드립니다.
with문 쿼리는 망이 분리되어 사진으로 올리는점 양해 부탁 드립니다
가계도를 조회 하는 쿼리구요
테이블은 with 문 형태로 생겼습니다.
하고자 하는건 나를 기준으로 2대에서 3대정도 부모의 정보가 매핑이 되는지 안되는지를 체크하고 싶은데요
아웃풋은
리스트로 캡처 처럼 나오면 좋겠습니다
혹시 쿼리로 가능할까요?! 안되면 프로그램에서 루프 돌려야 하는데 그닥 좋은방법은 아닐거 같아서 혹시 방법이 있을까 싶어서 문의 합니다.
답변 부탁 드립니다.
by 우리집아찌
[2020.08.22 20:51:17]
by 쫀득이
[2020.08.23 09:18:28]
답변감사 합니다
근데 테이블에 부모컬럼이 2개 존재하는데요 connect by가 되나요?!
조건을 or로 묶을수 있나요?!
by 쫀득이
[2020.08.23 09:36:55]
아 connect by 로 되는군요 부모컬럼이 1개일때만 쓸수 있는걸로 잘못 알고 있었는데 덕분에 다시 확인할수 있었네요
답변 감사합니다. 간단한걸 프로그램에서 재귀호출했네요..
by 마농
[2020.08.24 08:54:01]
WITH t1 AS ( SELECT '나' my, '부' fa, '모' mo FROM dual UNION ALL SELECT '부', '부부', '부모' FROM dual UNION ALL SELECT '모', '모부', '모모' FROM dual UNION ALL SELECT '부부', '부부부', '부부모' FROM dual UNION ALL SELECT '부모', '부모부', '부모모' FROM dual UNION ALL SELECT '모부', '모부부', '모부모' FROM dual UNION ALL SELECT '모모', '모모부', '모모모' FROM dual UNION ALL SELECT '부부부', '부부부부', '부부부모' FROM dual UNION ALL SELECT '부부모', '부부모부', '부부모모' FROM dual UNION ALL SELECT '부모부', '부모부부', '부모부모' FROM dual UNION ALL SELECT '부모모', '부모모부', '부모모모' FROM dual UNION ALL SELECT '모부부', '모부부부', '모부부모' FROM dual UNION ALL SELECT '모부모', '모부모부', '모부모모' FROM dual UNION ALL SELECT '모모부', '모모부부', '모모부모' FROM dual UNION ALL SELECT '모모모', '모모모부', '모모모모' FROM dual ) SELECT * FROM t1 START WITH my = '나' CONNECT BY my IN (PRIOR fa, PRIOR mo) ORDER BY LEVEL ;
by 우리집아찌
[2020.08.24 11:07:07]
역시~!
by 우리집아찌
[2020.08.24 11:06:58]
WITH T AS (
SELECT '나' AS MY , '부' AS FA , '모' AS MO FROM DUAL UNION ALL
SELECT '부' AS MY , '부부' AS FA , '부모' AS MO FROM DUAL UNION ALL
SELECT '모' AS MY , '모부' AS FA , '모모' AS MO FROM DUAL UNION ALL
SELECT '부부' AS MY , '부부부' AS FA , '부부모' AS MO FROM DUAL UNION ALL
SELECT '부모' AS MY , '부모부' AS FA , '부모모' AS MO FROM DUAL UNION ALL
SELECT '모부' AS MY , '모부부' AS FA , '모부모' AS MO FROM DUAL UNION ALL
SELECT '모모' AS MY , '모모부' AS FA , '모모모' AS MO FROM DUAL UNION ALL
SELECT '부부부' AS MY , '부부부부' AS FA , '부부부모' AS MO FROM DUAL UNION ALL
SELECT '부부모' AS MY , '부부모부' AS FA , '부부모모' AS MO FROM DUAL UNION ALL
SELECT '부모부' AS MY , '부모부부' AS FA , '부모부모' AS MO FROM DUAL UNION ALL
SELECT '부모모' AS MY , '부모모부' AS FA , '부모모모' AS MO FROM DUAL UNION ALL
SELECT '모부부' AS MY , '모부부부' AS FA , '모부부모' AS MO FROM DUAL UNION ALL
SELECT '모부모' AS MY , '모부모부' AS FA , '모부모모' AS MO FROM DUAL UNION ALL
SELECT '모모모' AS MY , '모모모부' AS FA , '모모모부' AS MO FROM DUAL
)
SELECT MY
, MAX(CASE WHEN GB = 1 THEN FA END ) FA
, MAX(CASE WHEN GB = 2 THEN FA END ) MO
, MIN(RN) RN
, MIN(LV) LV
FROM (SELECT A.* , ROWNUM RN , LEVEL LV
FROM ( SELECT 1 GB , MY , FA FROM T
UNION ALL
SELECT 2 GB , MY , MO FROM T ) A
START WITH MY = '나'
CONNECT BY MY = PRIOR FA
ORDER SIBLINGS BY GB
)
GROUP BY MY
ORDER BY LV , RN
) 버튼을 클릭하여 작성 하시면 됩니다.