일자별 게시글 순위 조회 쿼리

원하는 쿼리

원하는 쿼리 결과

 

 

WITH board AS
(
SELECT '2020-12-01' createdate, 'StarCraft' type, '스타는 재미있다' content FROM dual
UNION ALL SELECT '2020-12-01', 'StarCraft', '스타2도 재미있다' FROM dual
UNION ALL SELECT '2020-12-03', 'LOL'      , '롤은 재미있다'    FROM dual
UNION ALL SELECT '2020-12-05', 'StarCraft', '스타는 재미없다'  FROM dual
UNION ALL SELECT '2020-12-07', 'LOL'      , '롤은 재미없다'    FROM dual
)
SELECT *
  FROM (SELECT createdate, type, content
             , ROW_NUMBER() OVER(PARTITION BY type, createdate ORDER BY 1) rn
          FROM board
         WHERE createdate BETWEEN '2020-12-01' AND '2020-12-07'
        )
 PIVOT (MIN(content) FOR createdate IN ( '2020-12-01' "2020-12-01"
                                       , '2020-12-02' "2020-12-02"
                                       , '2020-12-03' "2020-12-03"
                                       , '2020-12-04' "2020-12-04"
                                       , '2020-12-05' "2020-12-05"
                                       , '2020-12-06' "2020-12-06"
                                       , '2020-12-07' "2020-12-07"
                                       ) )
 ORDER BY type, rn
;