데이터베이스 질문과 답변
안녕하세요 쿼리 짜는 도중 잘 안되서 고수님들 도움 요청 드립니다.
SELECT 'A' AS CD, '' AS UP_CD, '기준관리' AS NM, 'Y' AS YN FROM DUAL
UNION ALL
SELECT 'A-1' AS CD, 'A' AS UP_CD, '은행관리' AS NM, 'Y' AS YN FROM DUAL
UNION ALL
SELECT 'A-2' AS CD, 'A' AS UP_CD, '계좌관리' AS NM, 'N' AS YN FROM DUAL
UNION ALL
SELECT 'B' AS CD, '' AS UP_CD, '기준관리' AS NM, 'Y' AS YN FROM DUAL
UNION ALL
SELECT 'B-1' AS CD, 'B' AS UP_CD, '은행관리' AS NM, 'Y' AS YN FROM DUAL
UNION ALL
SELECT 'B-1-1' AS CD, 'B-1' AS UP_CD, '계좌관리' AS NM, 'N' AS YN FROM DUAL
UNION ALL
SELECT 'C' AS CD, '' AS UP_CD, '기준관리' AS NM, 'Y' AS YN FROM DUAL
UNION ALL
SELECT 'C-1' AS CD, 'C' AS UP_CD, '은행관리' AS NM, 'N' AS YN FROM DUAL
UNION ALL
SELECT 'C-2' AS CD, 'C' AS UP_CD, '계좌관리' AS NM, 'N' AS YN FROM DUAL
예시 DATA는 위와 같으며, 조회시 아래와 같이 조회됩니다.
| CD | UP_CD | NM | YN |
| A | 기준관리 | Y | |
| A-1 | A | 은행관리 | Y |
| A-2 | A | 계좌관리 | N |
| B | 기준관리 | Y | |
| B-1 | B | 은행관리 | Y |
| B-1-1 | B-1 | 계좌관리 | N |
| C | 기준관리 | Y | |
| C-1 | C | 은행관리 | N |
| C-2 | C | 계좌관리 | N |
이렇게 조회되는것을 아래 처럼 YN 값이 N 인경우는 조회를 안하며, 상위 값에서 하위 값이 존재하지 않을때 같이 조회되지 않았으면 합니다.
| CD | UP_CD | NM | YN |
| A | 기준관리 | Y | |
| A-1 | A | 은행관리 | Y |
위와 같이 조회가 되면 되는데 어렵네요 ㅜㅜ 도움 부탁 드립니다
혹시 자료 중에 부모는 N 인데 자식은 Y 인 경우는 없나요?
그런 경우가 있다면 결과가 어떻게 나와야 하나요?
예를 들어 다음과 같은 자료가 존재할 경우 결과는 어떻게 나와야 하나요?
D, _, Y
E, D, N
F, E, Y
부모는 N인데 자식이 Y이라면 자식까지 안보여야 합니다. 이게 메뉴 트리 형태 하는거라서요
다 N 상태처럼 되야 하는거죠
WITH t AS
(
SELECT 'A' cd, '' up_cd, '기준관리' nm, 'Y' yn FROM dual
UNION ALL SELECT 'A-1' , 'A' , '은행관리', 'Y' FROM dual
UNION ALL SELECT 'A-2' , 'A' , '계좌관리', 'N' FROM dual
UNION ALL SELECT 'B' , '' , '기준관리', 'Y' FROM dual
UNION ALL SELECT 'B-1' , 'B' , '은행관리', 'Y' FROM dual
UNION ALL SELECT 'B-1-1', 'B-1', '계좌관리', 'N' FROM dual
UNION ALL SELECT 'C' , '' , '기준관리', 'Y' FROM dual
UNION ALL SELECT 'C-1' , 'C' , '은행관리', 'N' FROM dual
UNION ALL SELECT 'C-2' , 'C' , '계좌관리', 'N' FROM dual
UNION ALL SELECT 'D' , '' , '기준관리', 'Y' FROM dual
UNION ALL SELECT 'D-1' , 'D' , '은행관리', 'N' FROM dual
UNION ALL SELECT 'D-1-1', 'D-1', '계좌관리', 'Y' FROM dual
)
SELECT cd, up_cd, nm, yn
FROM t m
WHERE yn = 'Y'
AND EXISTS (SELECT 1
FROM t s
WHERE CONNECT_BY_ISLEAF = 1
AND yn = 'Y'
START WITH cd = m.cd
CONNECT BY PRIOR cd = up_cd
AND PRIOR yn = 'Y'
)
START WITH up_cd IS NULL
CONNECT BY PRIOR cd = up_cd
AND PRIOR yn = 'Y'
;
감사합니다 ㅜㅜ
) 버튼을 클릭하여 작성 하시면 됩니다.