계층쿼리 답이 있을까요? 특정 컬럼에 표시를 해주고 싶은데 1 2 696

by 겨울눈 [SQL Query] [2017.07.04 12:56:34]



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의 본체를 포함하여 본체에 속하는 자식들 이름 옆에 *(별)표시를 해주고 싶습니다.

 

 

by Dev03 [2017.07.04 13:14:13]

 SELECT LEVEL AS LV
          , LPAD(' ',LEVEL -1)||name as name1, id, parent_id
          , LPAD(' ',LEVEL -1)||CASE WHEN INSTR(SYS_CONNECT_BY_PATH(ID, '|'), '1002') > 0 THEN '*' END||name as name2
  FROM T
  START WITH parent_id IS NULL
  CONNECT BY PRIOR id = parent_id


by 겨울눈 [2017.07.04 13:19:20]

감사합니다!!

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입