WITH CODE AS ( SELECT 'A' NM_1, '*' NM_2, '*' NM_3, 'A01' CD FROM DUAL UNION ALL SELECT 'B' NM_1, '*' NM_2, 'C' NM_3, 'A02' CD FROM DUAL UNION ALL SELECT '*' NM_1, '*' NM_2, 'B' NM_3, 'A03' CD FROM DUAL UNION ALL SELECT 'C' NM_1, '*' NM_2, '*' NM_3, 'A05' CD FROM DUAL ), DATA AS ( SELECT 'AAAAA' KEY, 'A' NM_1, 'T' NM_2, 'D' NM_3 FROM DUAL UNION ALL SELECT 'BBBBB' KEY, 'B' NM_1, 'T' NM_2, 'C' NM_3 FROM DUAL UNION ALL SELECT 'CCCCC' KEY, 'C' NM_1, 'T' NM_2, 'D' NM_3 FROM DUAL UNION ALL SELECT 'DDDDD' KEY, 'C' NM_1, 'T' NM_2, 'B' NM_3 FROM DUAL ) SELECT KEY, NM_1, NM_2, NM_3, CD FROM ( SELECT AA.* , ROW_NUMBER() OVER(PARTITION BY CD ORDER BY RN1) RN FROM ( SELECT DATA.*, CODE.CD FROM CODE, ( SELECT ROW_NUMBER() OVER(ORDER BY NM_3 DESC) AS RN1, A.* FROM DATA A ) DATA WHERE DECODE(CODE.NM_1, '*', 'X', DATA.NM_1) = DECODE(CODE.NM_1, '*', 'X', CODE.NM_1) AND DECODE(CODE.NM_2, '*', 'X', DATA.NM_2) = DECODE(CODE.NM_2, '*', 'X', CODE.NM_2) AND DECODE(CODE.NM_3, '*', 'X', DATA.NM_3) = DECODE(CODE.NM_3, '*', 'X', CODE.NM_3) ) AA ) WHERE RN = 1
위의 쿼리를 실행하면 아래와 같은 나오는데
좀 무식한 것 같아 이렇게 올려봅니다.
AAAAA | A | T | D | A01 |
BBBBB | B | T | C | A02 |
DDDDD | C | T | B | A03 |
CCCCC | C | T | C | A05 |
쿼리는 CODE 테이블에 NM1, NM2, NM3가 있는데 '*'가 들어가 있는건 아무값이나 들어가도 되고,
'*' 가 아닌 부분은 값이 일치를 하는데 세개의 조건이 만족한 경우가 1순위이고, 그다음 2개의 조건, 그리고 1개의 조건만 만족할 경우 NM3가 우선순위 입니다.
재미삼아 만들어 봤어요..
WITH CODE AS ( SELECT 'A' NM_1, '*' NM_2, '*' NM_3, 'A01' CD FROM DUAL UNION ALL SELECT 'B' NM_1, '*' NM_2, 'C' NM_3, 'A02' CD FROM DUAL UNION ALL SELECT '*' NM_1, '*' NM_2, 'B' NM_3, 'A03' CD FROM DUAL UNION ALL SELECT 'C' NM_1, '*' NM_2, '*' NM_3, 'A05' CD FROM DUAL ), DATA AS ( SELECT 'AAAAA' KEY, 'A' NM_1, 'T' NM_2, 'D' NM_3 FROM DUAL UNION ALL SELECT 'BBBBB' KEY, 'B' NM_1, 'T' NM_2, 'C' NM_3 FROM DUAL UNION ALL SELECT 'CCCCC' KEY, 'C' NM_1, 'T' NM_2, 'D' NM_3 FROM DUAL UNION ALL SELECT 'DDDDD' KEY, 'C' NM_1, 'T' NM_2, 'B' NM_3 FROM DUAL ) SELECT KEY, NM_1, NM_2, NM_3, CD FROM ( SELECT KEY, AA.NM_1, AA.NM_2, AA.NM_3, CD , ROW_NUMBER() OVER(PARTITION BY KEY ORDER BY (DECODE(BB.NM_1,'*',1,AA.NM_1,2,0) + DECODE(BB.NM_2,'*',1,AA.NM_2,2,0) + DECODE(BB.NM_3,'*',1,AA.NM_3,3,0)) DESC) RN FROM DATA AA , CODE BB ) WHERE RN = 1 ;