한 테이블 안에서 답변형 게시판을 만들었습니다.

 즉, board_seq, parent_board_seq로 구분하여

    답변형이 아닐 때에는  board_seq, parent_board_seq에 똑같은  seq값이 들어가고

답변형 일 때에는  board_seq, parent_board_seq에 다른 값이 들어갑니다.

  즉,  board_seq  = 30 , parent_board_seq = 29 이렇게...

그런데 중요한건 리스트 가져올 때 각 게시물에  속한 답변 게시물의 총 개수를 가져오려고 했는데..

 아래 쿼리로 돌리면 가져오긴 합니다.

문제점은 삭제 된 글을 빼고 가져와야 하는데 모두 가져 옵니다.

 COUNT(*) OVER(PARTITION BY BBS.PARENT_SEQ ) AS CNT  이 구문에서 삭제 된 게시물은 빼고 가져오려면 어떻게 해야 하나요??

삭제는 del_yn = 'Y' 입니다.

SELECT PAGE ,
         BBS_NO,
         BOARD_SEQ,
         PARENT_SEQ,
         TITLE,
         CONTENTS,
         DOWN_CNT,
         EMAIL,
         EVENT_DAY ,
         EVENT_HOUR ,
         INP_NAME ,
         UPDT_NAME ,
         PWD ,
         READ_CNT ,
         VIDEO_URL ,
         PRODUCT_NM,
         PRODUCT_CODE,
         INP_DATE,
         UPDT_DATE,
         FILE_PATH,
         ORIGIN_FILE_NAME,
         TRANSFER_FILE_NAME ,
         FILE_TYPE,
         PHONE_NUM ,
         QUESTION_CODE ,
         RECIVE_YN,
         REPLY_CONTENTS,
         REPLY_YN,
         SMS_YN,
         FILE_PATH,
         TAG,
         CNT
    FROM
         (
         SELECT
                CEIL( ROWNUM / 10 ) AS PAGE ,
                RANK() OVER (ORDER BY ROWNUM  desc ) AS BBS_NO,
                A.BOARD_SEQ,
                A.PARENT_SEQ,
                A.TITLE,
                A.CONTENTS,
                A.DOWN_CNT,
                A.EMAIL,
                A.EVENT_DAY ,
                A.EVENT_HOUR ,
                A.INP_NAME ,
                A.UPDT_NAME ,
                A.PWD ,
                A.READ_CNT ,
                A.VIDEO_URL ,
                A.PRODUCT_NM,
                A.PRODUCT_CODE,
                A.INP_DATE,
                A.UPDT_DATE,
                A.FILE_PATH,
                A.ORIGIN_FILE_NAME,
                A.TRANSFER_FILE_NAME ,
                A.FILE_TYPE,
                A.PHONE_NUM ,
                A.QUESTION_CODE ,
                A.RECIVE_YN,
                A.REPLY_CONTENTS,
                A.REPLY_YN,
                A.SMS_YN,
                A.TAG,
                A.CNT
               
           FROM
                (
               
                SELECT
                       BBS.BOARD_SEQ,
                       BBS.PARENT_SEQ,
                       BBS.TITLE,
                       BBS.CONTENTS, 
                       BBS.DOWN_CNT,
                       BBS.EMAIL,
                       BBS.EVENT_DAY ,
                       BBS.EVENT_HOUR ,
                       BBS.INP_NAME ,
                       BBS.UPDT_NAME ,
                       BBS.PWD ,
                       BBS.READ_CNT ,
                       BBS.VIDEO_URL ,
                       BBS.PRODUCT_NM,
                       BBS.PRODUCT_CODE,
                       to_char(BBS.INP_DATE, 'yyyy.mm.dd') AS INP_DATE ,
                       to_char(BBS.UPDT_DATE, 'yyyy.mm.dd') AS UPDT_DATE ,
                       FF.FILE_PATH ,
                       FF.ORIGIN_FILE_NAME ,
                       FF.TRANSFER_FILE_NAME ,
                       FF.FILE_TYPE ,
                       BBS_ADD.PHONE_NUM,
                       BBS_ADD.QUESTION_CODE,
                       BBS_ADD.RECIVE_YN,
                       BBS_ADD.REPLY_CONTENTS ,
                       BBS_ADD.REPLY_YN,
                       BBS_ADD.SMS_YN,
                       BBS.TAG,
                       COUNT(*) OVER(PARTITION BY BBS.PARENT_SEQ ) AS CNT 
                         
                  FROM CHSY_BOARD BBS,
                       CHSY_BOARD_FILE FF ,
                       CHSY_BOARD_ADD BBS_ADD
                 WHERE BBS.DEL_YN='N'
                       AND BBS.BOARD_CODE = 'M61'
                       AND BBS.BOARD_SEQ = FF.PARENT_SEQ(+)
                       AND BBS.BOARD_SEQ = BBS_ADD.PARENT_SEQ(+)
                      
              ORDER BY BBS.PARENT_SEQ DESC ,BBS.BOARD_SEQ ASC
                )A
       ORDER BY PARENT_SEQ DESC ,BOARD_SEQ ASC
         )
   WHERE PAGE = 1