티스토리 뷰

  SELECT T.*, 
          (SELECT COUNT(1) FROM IP_TB_PRODUCT_EXPER_ENTRY WHERE EXPERIENCE_ID = T.EXPERIENCE_ID AND CATEGORY_ID = T.CATEGORY_ID ) AS APP_CNT 
             , (SELECT COUNT(1) FROM IP_TB_PRODUCT_EXPER_ENTRY WHERE EXPERIENCE_ID = T.EXPERIENCE_ID AND CATEGORY_ID = T.CATEGORY_ID AND PRIZE_YN = `Y` ) AS PRIZE_CNT 
      FROM ( 
      SELECT    
               ROW_NUMBER() OVER(ORDER BY PRIZE_DATE DESC) AS rNum, 
                     COUNT(*)OVER() AS totalCnt, 
               A.AD_ID,  
               A.EXPERIENCE_ID,  
               A.CATEGORYID AS CATEGORY_ID,  
               A.COMPANY,  
               A.PRODUCT_NAME, 
               TO_CHAR(TO_DATE(B.PRIZE_DATE),`YYYY-MM-DD`) AS PRIZE_DATE,  
               A.STATE 
      FROM     IP_TB_PRODUCT_AD A,  
               IP_TB_PRODUCT_EXPERIENCE B 
      WHERE    A.EXPERIENCE_ID = B.EXPERIENCE_ID  
      AND A.CATEGORYID = B.CATEGORY_ID  
      and A.STATE >= 7 ) T 
      WHERE rNum BETWEEN (1 - 1) * 10 +1 AND 1 *10;

'Study > DB' 카테고리의 다른 글

oracle 10g부터 사용할 수 있는 페이징 쿼리  (0) 2014.10.21
오라클 날짜 구하기 정리  (0) 2013.03.27
달력생성(with이용)  (0) 2012.09.26
오라클 쿼리 문자 아스키 코드  (0) 2012.08.20
오라클 MERGE 사용법  (0) 2012.05.24
공지사항
최근에 올라온 글