๋กœ์ผ“๐Ÿพ
article thumbnail
๋ฐ˜์‘ํ˜•

์˜ค๋ผํด์—์„œ ํŽ˜์ด์ง• ์ฒ˜๋ฆฌ ๋“ฑ์„ ํ• ๋•Œ ๋กœ์šฐ์˜ ์ˆœ๋ฒˆ์„ ๋งค๊ธด ๋’ค ์ •๋ ฌ์„ ํ•ด์ค˜์•ผ ํ•ฉ๋‹ˆ๋‹ค.

์ด๋•Œ ROW_NUMBER() ์„ ์‚ฌ์šฉํ•˜๋ฉด ์‰ฝ๊ฒŒ ์ˆœ๋ฒˆ์„ ๋งค๊ธธ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.

 


# ์˜ˆ์ œ ์ฝ”๋“œ

 

๋‹จ์ˆœ SELECT ์ฟผ๋ฆฌ๋ฌธ

 

์˜ˆ์ œ ํ…Œ์ด๋ธ”์—์„œ ๋‹จ์ˆœ SELECT ์ฟผ๋ฆฌ๋ฌธํ–ˆ์„ ๋•Œ ์œ„์™€ ๊ฐ™์€ ๋กœ์šฐ๋ฅผ ๋ณด์—ฌ์ค๋‹ˆ๋‹ค.

 

๊ทธ๋ฆฌ๊ณ  ์ € ํ…Œ์ด๋ธ”์˜ ๋กœ์šฐ๋ฅผ ๋‚ ์งœ(REGIST_DT)๋กœ ์ •๋ ฌํ•˜๊ณ  ์‹ถ์Šต๋‹ˆ๋‹ค.

ORDER BY๋ฅผ ์‚ฌ์šฉํ•˜์—ฌ ์ •๋ ฌ ํ•  ์ˆ˜ ์žˆ๊ฒ ์ง€๋งŒ ์ด๋ ‡๊ฒŒ๋งŒ ํ•˜๋ฉด ๋ถ„๋ช… ํ•œ๊ณ„๊ฐ€ ์กด์žฌํ•ฉ๋‹ˆ๋‹ค.

 

ํŽ˜์ด์ง• ์ฒ˜๋ฆฌ๋ฅผ ํ•ด์•ผํ•œ๋‹ค๊ณ  ๊ฐ€์ •ํ•ด๋ด…์‹œ๋‹ค. 1, 2, .. ํŽ˜์ด์ง€ ๋“ฑ์ด ์กด์žฌํ•  ๊ฒƒ์ด๊ณ , ํ•œ ํŽ˜์ด์ง€์—๋Š” 10๊ฐœ์˜ ๋กœ์šฐ๋ฅผ ๋ณด์—ฌ์ฃผ๊ณ  ๋กœ์šฐ๋Š” ๋‚ ์งœ๋ฅผ ๊ธฐ์ค€์œผ๋กœ ๋ทฐ์— ๋ฟŒ๋ ค์ง„๋‹ค๊ณ  ํ•ฉ์‹œ๋‹ค.

 

๋งŒ์•ฝ ORDER BY๋งŒ ์‚ฌ์šฉํ•œ๋‹ค๋ฉด 1ํŽ˜์ด์ง€์—๋Š” ๋ฌธ์ œ ์—†์ด ๋กœ์šฐ๊ฐ’์„ ๋ฟŒ๋ฆด์ˆ˜ ์žˆ๊ฒ ์ง€๋งŒ, ๋ฌธ์ œ๋Š” 2ํŽ˜์ด์ง€๋ถ€ํ„ฐ ์ž…๋‹ˆ๋‹ค.

2ํŽ˜์ด์ง€์—์„œ 11๋ฒˆ์งธ๋ถ€ํ„ฐ 20๋ฒˆ์งธ๊นŒ์ง€์˜ ๋กœ์šฐ(1๋ฒˆ~10๋ฒˆ ํŽ˜์ด์ง€๋Š” ์ œ์™ธํ•˜๊ณ )๋ฅผ ๊ฐ€์ ธ์™€์•ผ ํ•˜๋Š”๋ฐ ์ด๋Š” ์‰ฝ์ง€ ์•Š์Šต๋‹ˆ๋‹ค.

 

์ด๋•Œ ๋ฐ”๋กœ ROW_NUMBER()์„ ์ด์šฉํ•˜์—ฌ ์กฐํšŒ ์ˆœ๋ฒˆ์„ ์ •ํ•ด์ฃผ๋Š” ๊ฒƒ์ž…๋‹ˆ๋‹ค.

 

SELECT 
      SEQ
    , TITLE
    , TO_CHAR(REGIST_DT, 'YYYY-MM-DD HH24:MI') AS REGIST_DT
    , ROW_NUMBER() OVER( ORDER BY REGIST_DT DESC) AS rnum
    FROM TEMP_TABLE
    WHERE 1=1

 

์ฟผ๋ฆฌ๋ฌธ ๊ฒฐ๊ณผ

 


# ์‘์šฉ ์˜ˆ์ œ

 

SELECT
    A.*   
  FROM 
    (    
    SELECT                         
      BANNER_SN AS seq
     , BANNER_SJ AS TITLE
     , TO_CHAR(REGIST_DT, 'YYYY-MM-DD HH24:MI') AS REGIST_DT
     , ROW_NUMBER() OVER( ORDER BY REGIST_DT DESC) AS rnum
    FROM BANNER
    ) A
  WHERE A.rnum > 4 AND A.rnum < 8

 

๊ฒฐ๊ณผ

 

๋‹จ์ˆœ ORDER BY๋ฅผ ํ–ˆ์„ ๊ฒฝ์šฐ ์œ„์™€ ๊ฐ™์ด ํŠน์ • ๋ฒˆํ˜ธ์˜ ROW๋ฅผ ๊ฐ€์ ธ์˜ค๊ธฐ๊ฐ€ ์ƒ๋‹นํžˆ ๋ฒˆ๊ฑฐ๋กญ์Šต๋‹ˆ๋‹ค. ๋”ฐ๋ผ์„œ ROW_NUMBER()์„ ์ด์šฉํ•˜์—ฌ ๋งค๊ธด ์ˆœ๋ฒˆ์— ๋Œ€ํ•ด ์ •๋ ฌ ํ•ด๋†“์œผ๋ฉด ์–ด๋Š ROW ๋“  ๊ฐ€์ ธ์˜ค๊ธฐ๊ฐ€ ์‰ฝ์Šต๋‹ˆ๋‹ค.

 

 

์ฝ์–ด์ฃผ์…”์„œ ๊ฐ์‚ฌํ•ฉ๋‹ˆ๋‹ค.

์งˆ๋ฌธ์€ ์–ธ์ œ๋‚˜ ํ™˜์˜ํ•ฉ๋‹ˆ๋‹ค.

 

"๋‚œ ๋ฐ˜๋“œ์‹œ ๋ฐฑ์—”๋“œ ์™•์ด ๋ ๊ฑฐ์•ผ"

๋ฐ˜์‘ํ˜•
profile on loading

Loading...