SQL 페이징

 

우선 ROWNUM 1~10까지 출력

SELECT * 
FROM( 
    SELECT  
        ROWNUM, 
        N.* 
    FROM (SELECT * FROM NOTICE ORDER BY REGDATE DESC) N 
)     
WHERE ROWNUM BETWEEN 1 AND 10;  

 

 

다음 페이지 분리를 위해서 서브쿼리

-- ROWNUM 11~20 페이징
-- MEMBER에 NAME 칼럼을 (MN으로 별칭써서) 추가 = JOIN 

SELECT * 
FROM( 
    SELECT  
        ROWNUM NUM, 
        N.* 
    FROM ( 
        SELECT NOTICE.*, MEMBER.NAME MN 
        FROM NOTICE LEFT JOIN MEMBER  
        ON MEMBER.ID = NOTICE.WRITER_ID 
        ORDER BY NOTICE.REGDATE DESC
        ) N 
)   
WHERE NUM BETWEEN 11 AND 20;   



-- ROWNUM 21~30 페이징

SELECT *  
FROM ( 
    SELECT 
    	ROWNUM NUM, 
        N.*  
    FROM (
    	SELECT NOTICE.*, MEMBER.NAME MN  
    	FROM NOTICE LEFT JOIN MEMBER 
        ON MEMBER.ID = NOTICE.WRITER_ID  
    	ORDER BY NOTICE.REGDATE DESC
        ) N  
)  
WHERE NUM BETWEEN 21 AND 30;