WITH T AS ( SELECT 'A' 고객사, 'Y' 메인고객사 FROM dual UNION ALL SELECT 'B' , 'Y' FROM dual UNION ALL SELECT 'C' , 'Y' FROM dual UNION ALL SELECT 'C-1' , 'N' FROM dual UNION ALL SELECT 'C-2' , 'N' FROM dual UNION ALL SELECT 'C-3' , 'N' FROM dual ) SELECT 고객사, 메인고객사 FROM ( SELECT T.*, COUNT(*) OVER(PARTITION BY SUBSTR(고객사, 1, 1)) cnt FROM T ) WHERE (메인고객사 = 'Y' AND cnt = 1) OR (메인고객사 = 'N' AND cnt <> 1) ORDER BY 고객사
WITH T AS ( SELECT 'A---0' 고객사, 'Y' 메인고객사 FROM dual UNION ALL SELECT 'B---0' , 'Y' FROM dual UNION ALL SELECT 'C---0' , 'Y' FROM dual UNION ALL SELECT 'C---1' , 'N' FROM dual UNION ALL SELECT 'C---2' , 'N' FROM dual UNION ALL SELECT 'C---3' , 'N' FROM dual ) select 고객사, 메인고객사 from (select substr(고객사, 5) m_code, substr(고객사, 1,4)||decode(메인고객사, 'N', 0) n_code, 고객사, 메인고객사 from T) where CONNECT_BY_ISLEAF = 1 start with m_code = 0 connect by prior 고객사 = n_code ;
계층형 구조로 만들면 재밌겠다 싶어서 만들어 봤습니다.