[SQL] 데이터베이스별 페이징 SQL(oracle/mysql/postgresql 등등)

1. 개요

• SQL 페이징은 데이터베이스에서 대량의 데이터를 작은 단위로 나누어 결과를 반환하는 방법입니다.
• 이를 통해 사용자는 한 번에 많은 양의 데이터를 처리하지 않고 필요한 만큼만 처리할 수 있습니다.
• 보통 SQL 페이징은 LIMIT 및 OFFSET 구문을 사용하여 구현됩니다.
• LIMIT 구문은 가져올 레코드 수를 지정하며, OFFSET 구문은 가져올 첫 번째 레코드의 위치를 지정합니다.

database sql

 

2. 개념잡기

• 예를 들어, 다음과 같은 SQL 쿼리를 실행하여 10개의 레코드를 가져오고 첫 번째 레코드부터 20번째 레코드까지 건너뛰고 21번째 레코드부터 30번째 레코드까지 가져올 수 있습니다.

SELECT * FROM my_table LIMIT 10 OFFSET 20;

 

3. SQL테스트 사이트

• 아래사이트에서 테이블 RDMS별 SQL를 작동시킬수 있다.
• http://sqlfiddle.com/

 

 

3. 오라클페이징

• ROW_NUMBER() 사용

SELECT TB.* FROM  
    ( SELECT A.USER_ID                       
           , ROW_NUMBER() OVER (ORDER BY A.USER_ID DESC) AS rnum
       FROM TB_USER A
      ORDER BY A.CRE_DT DESC 
    ) TB
WHERE TB.rnum BETWEEN #{startNo} AND #{endNo}

 

 

4. 오라클페이징 

• ROWNUM 사용

//recordCountPerPage : 한페이지당 보여질 개수 (10 또는 20)	
//firstIndex : 첫번째 인덱스 (0개)

SELECT * FROM ( SELECT rownum rnum, TB.* FROM (
    SELECT A.USER_ID
         , A.USER_NM				     
         , A.CRE_DT
      FROM TB_USER A					
     ORDER BY A.CRE_DT DESC 
) TB ) WHERE rnum BETWEEN #{firstIndex} + 1 AND #{firstIndex} + #{recordCountPerPage}

 

 

5. MySQL / MariaDB

• ROWNUM TEMP 테이블 사용

//startNo : 0
//endNo : 10 또는 20

SELECT LST.* FROM
    (SELECT @ROWNUM := @ROWNUM + 1 AS RNUM, TB.* FROM (
         SELECT A.USER_ID
              , A.USER_NM
              , A.CRE_DT						     
           FROM TB_USER A
         ) TB, (SELECT @ROWNUM := 0) TMP
    ) LST
WHERE LST.RNUM BETWEEN #{startNo} and #{endNo}

 

 

6. MySQL / MariaDB

• LIMIT 와 OFFSET 사용

//recordCountPerPage : 한페이지당 보여질 개수 (10개 또는 20개)	
//firstIndex : 첫번째 인덱스 (0개)

SELECT A.USER_ID
     , A.USER_NM
     , A.CRE_DT						     
  FROM TB_USER A
 ORDER BY A.CRE_DT DESC 
 LIMIT #{recordCountPerPage} OFFSET #{firstIndex}

 

 

7. PostgreSQL

• LIMIT 와 OFFSET 사용

//recordCountPerPage : 한페이지당 보여질 개수 (10개 또는 20개)	
//firstIndex : 첫번째 인덱스 (0개)

SELECT A.USER_ID
     , A.USER_NM
     , A.CRE_DT						     
  FROM TB_USER A
 ORDER BY A.CRE_DT DESC 
 LIMIT #{recordCountPerPage} OFFSET #{firstIndex}

 

 

8. PostgreSQL

• ROW_NUMBER() 과 OVER()를 활용한 한번에 전체글개수 까지 조회

//recordCountPerPage : 한페이지당 보여질 개수 (10개 또는 20개)	
//page : 현재페이지 (1,2,3 등등)

SELECT AAA.* FROM  (SELECT AA.*, TO_NUMBER(TO_CHAR(CEIL(ROW_NUM/#{recordCountPerPage}),'9999999'),'9999999')+1 PAGE  FROM (
    SELECT COUNT(*) OVER() TOTAL_ROW_CNT , ROW_NUMBER() OVER()-1 AS ROW_NUM, A.*  FROM (
        SELECT A.USER_ID
             , A.USER_NM
             , A.CRE_DT
          FROM TB_USER A
         ORDER BY A.CRE_DT DESC
    )A
)AA)AAA WHERE PAGE = #{page}

 

 

9. tibero

• ROW_NUMBER() 과 OVER()를 활용한 한번에 전체글개수 까지 조회

//recordCountPerPage : 한페이지당 보여질 개수 (10개 또는 20개)	
//firstIndex : 첫번째 인덱스 (0개)

SELECT * FROM ( SELECT rownum rn, TB.* FROM (
    SELECT A.USER_ID
         , A.USER_NM
         , A.CRE_DT						     
      FROM TB_USER A
     ORDER BY A.CRE_DT DESC      
) TB ) WHERE rn BETWEEN #{firstIndex} + 1 AND #{firstIndex} + #{recordCountPerPage}