수고하십니다. 

CONNECT BY 절을 사용하다가 궁금한게 있어 질문드립니다. 

답글의 DEPTH가 1차까지만 있는 게시판이 있습니다. 
특정 칼럼에는 해당 게시글의 성격을 지시하는 FLAG 정보가 들어있습니다. 
그런데 이게 부모글과 자식글이 같은 값을 갖을 수도 있고, 다른 값을 갖을 수도 있습니다. 

제가 하고 싶은 것은 부모글의 FLAG 정보를 가지고 필터링을 하는 것입니다. 
물론 부모글의 FLAG정보와 다른 값을 가지고 있는 자식글도 함께 필터링 되어야 하겠지요. 

생각같아서는 

부모글과 해당 부모글의 자식글을 하나의 그룹으로 묶고
그룹의 대표 FLAG를 부모글의 FLAG로 셋팅
대표 FLAG로 필터링

이렇게 진행하면 될거 같아서 하긴했는데.. 

좀 거시기... 해서요.. 

한번 봐주시면 감사하겠습니다. 


복잡하네요. 
쿼리로 정리해 볼게요.
게시판의 자료입니다.

 
WITH BOARD AS (
SELECT '1' 	AS ID , 	'' 		AS P_ID , '부모글' 					AS COMMENTS , '2' AS FLAG 	FROM DUAL UNION ALL
SELECT '2' 	AS ID , 	'1' 	AS P_ID , '자식글' 					AS COMMENTS , '3' AS FLAG 	FROM DUAL UNION ALL
SELECT '3' 	AS ID , 	'' 		AS P_ID , '부모글' 					AS COMMENTS , '2' AS FLAG 	FROM DUAL UNION ALL
SELECT '4' 	AS ID , 	'' 		AS P_ID , '부모글' 					AS COMMENTS , '2' AS FLAG 	FROM DUAL UNION ALL
SELECT '5' 	AS ID , 	'4' 	AS P_ID , '자식글' 					AS COMMENTS , '2' AS FLAG 	FROM DUAL UNION ALL
SELECT '6' 	AS ID , 	'' 		AS P_ID , '자식이없어속이편한글' 	AS COMMENTS , '3' AS FLAG 	FROM DUAL UNION ALL
SELECT '7' 	AS ID , 	'' 		AS P_ID , '부모글' 					AS COMMENTS , '3' AS FLAG 	FROM DUAL UNION ALL
SELECT '8' 	AS ID , 	'7' 	AS P_ID , '자식글' 					AS COMMENTS , '3' AS FLAG 	FROM DUAL UNION ALL
SELECT '9' 	AS ID , 	'' 		AS P_ID , '자식이없어속이편한글' 	AS COMMENTS , '2' AS FLAG 	FROM DUAL UNION ALL
SELECT '10' AS ID , 	'' 		AS P_ID , '부모글' 					AS COMMENTS , '1' AS FLAG 	FROM DUAL UNION ALL
SELECT '11' AS ID , 	'10' 	AS P_ID , '자식글' 					AS COMMENTS , '3' AS FLAG 	FROM DUAL UNION ALL
SELECT '12' AS ID , 	'' 		AS P_ID , '자식이없어속이편한글' 	AS COMMENTS , '2' AS FLAG 	FROM DUAL UNION ALL
SELECT '13' AS ID , 	'3' 	AS P_ID , '자식글' 					AS COMMENTS , '2' AS FLAG 	FROM DUAL UNION ALL
SELECT '14' AS ID , 	'' 		AS P_ID , '자식이없어속이편한글' 	AS COMMENTS , '2' AS FLAG 	FROM DUAL 
)
SELECT BOARD.*
FROM BOARD 
START WITH P_ID IS NULL 
CONNECT BY PRIOR ID = P_ID 
ORDER SIBLINGS BY TO_NUMBER(ID) ;

 

보시면 .
ID 게시글의 ID
P_ID  부모글의 ID
COMMENTS 내용
FLAG 게시글의 성격
이 있습니다. 

ID 10과 11번을 보시면 FLAG가 각각 1 과 3으로 다른 값을 가지고 있습니다. 
이게 FLAG = '1' 로 조회를 하면.. 
당근 .... 자식글은 조회가 되지를 않죠. ㅠㅠ
제가 당면하 문제점입니다. 
FLAG = '1' 로 조회를 하였을 때 FLAG = '1' 인 부모글이 조회 되고, 그 부모글이 자식을 가졌다면 자식도 함께 조회되어야 합니다. 

아.. 자식글이 FLAG = '1' 인 경우는 해당되지 않습니다. 부모글의 FLAG 값이 3이면.. 자식글의 FLAG 값이 1이라도 조회가 되지 않아야 합니다. 

제가 작성해본 쿼리입니다. 

WITH BOARD AS (
SELECT '1' 	AS ID , 	'' 		AS P_ID , '부모글' 					AS COMMENTS , '2' AS FLAG 	FROM DUAL UNION ALL
SELECT '2' 	AS ID , 	'1' 	AS P_ID , '자식글' 					AS COMMENTS , '1' AS FLAG 	FROM DUAL UNION ALL
SELECT '3' 	AS ID , 	'' 		AS P_ID , '부모글' 					AS COMMENTS , '2' AS FLAG 	FROM DUAL UNION ALL
SELECT '4' 	AS ID , 	'' 		AS P_ID , '부모글' 					AS COMMENTS , '2' AS FLAG 	FROM DUAL UNION ALL
SELECT '5' 	AS ID , 	'4' 	AS P_ID , '자식글' 					AS COMMENTS , '2' AS FLAG 	FROM DUAL UNION ALL
SELECT '6' 	AS ID , 	'' 		AS P_ID , '자식이없어속이편한글' 	AS COMMENTS , '3' AS FLAG 	FROM DUAL UNION ALL
SELECT '7' 	AS ID , 	'' 		AS P_ID , '부모글' 					AS COMMENTS , '3' AS FLAG 	FROM DUAL UNION ALL
SELECT '8' 	AS ID , 	'7' 	AS P_ID , '자식글' 					AS COMMENTS , '3' AS FLAG 	FROM DUAL UNION ALL
SELECT '9' 	AS ID , 	'' 		AS P_ID , '자식이없어속이편한글' 	AS COMMENTS , '2' AS FLAG 	FROM DUAL UNION ALL
SELECT '10' AS ID , 	'' 		AS P_ID , '부모글' 					AS COMMENTS , '1' AS FLAG 	FROM DUAL UNION ALL
SELECT '11' AS ID , 	'10' 	AS P_ID , '자식글' 					AS COMMENTS , '3' AS FLAG 	FROM DUAL UNION ALL
SELECT '12' AS ID , 	'' 		AS P_ID , '자식이없어속이편한글' 	AS COMMENTS , '2' AS FLAG 	FROM DUAL UNION ALL
SELECT '13' AS ID , 	'3' 	AS P_ID , '자식글' 					AS COMMENTS , '2' AS FLAG 	FROM DUAL UNION ALL
SELECT '14' AS ID , 	'' 		AS P_ID , '자식이없어속이편한글' 	AS COMMENTS , '2' AS FLAG 	FROM DUAL 
)
SELECT BASE2.* FROM (
 SELECT BASE.*
 , DECODE(LV, 2, LAG(FLAG, 1) OVER(ORDER BY FAMILY, LV), FLAG) AS FLAG_GRP -- 부모와 자식을 같은 그룹으로 묶었으므로 바로 이전 ROW(부모)의 FLAG값을 대표 FLAG 값으로 셋팅
 FROM (
 SELECT DECODE(LEVEL, 2, ROWNUM -1, ROWNUM) AS FAMILY, --LEVEL이 2인 경우는 바로 윗글의 ROWNUM 대체. 같은 ROWNUM을 그룹ID로 사용 (그룹)
  
  LEVEL AS LV,
  BOARD.*
 FROM BOARD 
 START WITH P_ID IS NULL 
 CONNECT BY PRIOR ID = P_ID 
 ORDER SIBLINGS BY TO_NUMBER(ID) -- CONNECT BY 상태로 정렬
 ) BASE 
) BASE2
ORDER BY FAMILY, LV

 

새로운 칼럼 FLAG_GRP (가족 대표 FLAG 값)을 만들기 위해 이런 저런 처리를 해봤습니다. ㅠㅠ

아래는 제가 원하는 결과의 예입니다.

 

WITH BOARD AS (
SELECT '1' 	AS ID , 	'' 		AS P_ID , '부모글' 					AS COMMENTS , '2' AS FLAG 	FROM DUAL UNION ALL
SELECT '2' 	AS ID , 	'1' 	AS P_ID , '자식글' 					AS COMMENTS , '1' AS FLAG 	FROM DUAL UNION ALL
SELECT '3' 	AS ID , 	'' 		AS P_ID , '부모글' 					AS COMMENTS , '2' AS FLAG 	FROM DUAL UNION ALL
SELECT '4' 	AS ID , 	'' 		AS P_ID , '부모글' 					AS COMMENTS , '2' AS FLAG 	FROM DUAL UNION ALL
SELECT '5' 	AS ID , 	'4' 	AS P_ID , '자식글' 					AS COMMENTS , '2' AS FLAG 	FROM DUAL UNION ALL
SELECT '6' 	AS ID , 	'' 		AS P_ID , '자식이없어속이편한글' 	AS COMMENTS , '3' AS FLAG 	FROM DUAL UNION ALL
SELECT '7' 	AS ID , 	'' 		AS P_ID , '부모글' 					AS COMMENTS , '3' AS FLAG 	FROM DUAL UNION ALL
SELECT '8' 	AS ID , 	'7' 	AS P_ID , '자식글' 					AS COMMENTS , '3' AS FLAG 	FROM DUAL UNION ALL
SELECT '9' 	AS ID , 	'' 		AS P_ID , '자식이없어속이편한글' 	AS COMMENTS , '2' AS FLAG 	FROM DUAL UNION ALL
SELECT '10' AS ID , 	'' 		AS P_ID , '부모글' 					AS COMMENTS , '1' AS FLAG 	FROM DUAL UNION ALL
SELECT '11' AS ID , 	'10' 	AS P_ID , '자식글' 					AS COMMENTS , '3' AS FLAG 	FROM DUAL UNION ALL
SELECT '12' AS ID , 	'' 		AS P_ID , '자식이없어속이편한글' 	AS COMMENTS , '2' AS FLAG 	FROM DUAL UNION ALL
SELECT '13' AS ID , 	'3' 	AS P_ID , '자식글' 					AS COMMENTS , '2' AS FLAG 	FROM DUAL UNION ALL
SELECT '14' AS ID , 	'' 		AS P_ID , '자식이없어속이편한글' 	AS COMMENTS , '2' AS FLAG 	FROM DUAL 
)
SELECT BASE2.* FROM (
 SELECT BASE.*
 , DECODE(LV, 2, LAG(FLAG, 1) OVER(ORDER BY FAMILY, LV), FLAG) AS FLAG_GRP 
 FROM (
  SELECT DECODE(LEVEL, 2, ROWNUM -1, ROWNUM) AS FAMILY,
    
    LEVEL AS LV,
    BOARD.*
  FROM BOARD 
  START WITH P_ID IS NULL 
  CONNECT BY PRIOR ID = P_ID 
  ORDER SIBLINGS BY TO_NUMBER(ID)
 ) BASE 
) BASE2
WHERE FLAG_GRP = '1' --내가 원하는 필터링..
ORDER BY FAMILY, LV


 

FLAG_GRP 가 있으니 조회는 쉽네요. ㅎ

자.. 정리하면.. 
CONNECT BY를 사용하여 계층구조의 리스트를 만들고.. 그룹핑을 하였고.. 해당 그룹의 대표값을 생성후 그 대표값으로 필터링을 하였습니다. 

원하는대로 동작은 하는데.. 
너무 복잡한거 같아요. ㅠㅠ
그리고 정렬 조건을 명시하긴하였습니다만 그룹을 만들고, 대표값을 만드는 과정에서 
LAG(FLAG, 1) , DECODE(LEVEL, 2, ROWNUM -1, ROWNUM)  같은 처리를 하였는데요. 
이것도 좀 찜찜하고요.. 

다른 좋은 방법이 있는지 궁금해서 질문을 드립니다. 


CONNECT BY에서 그룹핑을 할 수 있는 방법이 있다거나..안된다고 알고 있습니다만. ㅠㅠ
그룹만드는 과정에서 ROWNUM -1 요런거 없이 할 수 있는 방법이 있는지요..


주저리 주저리 기나긴 글 읽어 주셔서 감사합니다. 
깔끔하게 설명할 수 있는 능력이 모자라요. ㅠㅠ

그럼 좋은 하루 되시길 바랍니다. 

흠. 점심시간이 가까워  오네요. 밥 맛나게 드세요.