WITH T AS ( SELECT 1001 AS ID ,NULL AS PARENT_ID ,'컴퓨터' AS NAME FROM DUAL UNION ALL SELECT 1002 AS ID ,1001 AS PARENT_ID ,'본체' AS NAME FROM DUAL UNION ALL SELECT 1003 AS ID ,1001 AS PARENT_ID ,'모니터' AS NAME FROM DUAL UNION ALL SELECT 1004 AS ID ,1001 AS PARENT_ID ,'프린터' AS NAME FROM DUAL UNION ALL SELECT 1005 AS ID ,1002 AS PARENT_ID ,'메인보드' AS NAME FROM DUAL UNION ALL SELECT 1006 AS ID ,1002 AS PARENT_ID ,'랜카드' AS NAME FROM DUAL UNION ALL SELECT 1007 AS ID ,1002 AS PARENT_ID ,'파워서플라이' AS NAME FROM DUAL UNION ALL SELECT 1008 AS ID ,1005 AS PARENT_ID ,'CPU' AS NAME FROM DUAL UNION ALL SELECT 1009 AS ID ,1005 AS PARENT_ID ,'RAM' AS NAME FROM DUAL UNION ALL SELECT 1010 AS ID ,1005 AS PARENT_ID ,'VGA' AS NAME FROM DUAL UNION ALL SELECT 1011 AS ID ,1003 AS PARENT_ID ,'전원케이블' AS NAME FROM DUAL UNION ALL SELECT 1012 AS ID ,1003 AS PARENT_ID ,'스탠드' AS NAME FROM DUAL ) SELECT LPAD(' ',LEVEL -1)||name, id, parent_id FROM T START WITH parent_id IS NULL CONNECT BY PRIOR id = parent_id;
예를 들어 이런 쿼리가 있다고 하면
밑에 첨부파일 처럼 결과가 나오는데
특정레벨의 특정 ID가 부모인 경우 컬럼에 표시를 해주고 싶습니다
예를들어 레벨 2의 본체를 포함하여 본체에 속하는 자식들 이름 옆에 *(별)표시를 해주고 싶습니다.