MS-SQL에서 ROW_NUMBER() 함수로 페이징 구현하기

안녕하세요? 정말 정말 오랜만에 티스토링을 합니다. 최근에 개인적인 일과 회사업무가 바빠서 여유가 없었습니다. 이번에는 MS-SQL에서 ROW_NUMBER() 함수에 대해서 알아보겠습니다.

 

 

 

 ROW_NUMBER() 함수는 생각보다 유용하게 사용할 수 있습니다. 단순하게는 행의 순서를 표시를 할 수 있습니다. 가장 많이 사용하는 경우는 게시판의 페이징을 구현할때 입니다. 그룹별로 순서를 정해야 하는 경우에도 사용할 수 있습니다.

 

 우선 쿼리에 사용할 데이터를 만들어 보겠습니다. 아래와 같이 쿼리를 작성하시면 #TMP라는 임시테이블을 만들고 조회 쿼리를 수행합니다. 그리고 마지막에 생성한 #TMP 임시테이블을 삭제합니다. 저는 항상 생성이 있으면 소멸을 해주도록 하고 있습니다. 대부분 자동으로 소멸이 되지만 명시적으로 지정해서 사용하는 것을 권장해 드립니다(임시테이블이 생성된 쿼리 실행 창에서 같은 이름의 임시테이블을 다시 생성하면 오류가 발생합니다.  임시테이블을 사용하고 나서 DROP TABLE #TMP로 삭제를 하면 좀더 편리하겠죠?).

 

SELECT 1 AS NUM, '수학' AS TITLE, 95.34 AS GRADES, '홍길동' AS NAME
INTO #TMP
UNION ALL
SELECT 2 AS NUM, '영어' AS TITLE, 90.1 AS GRADES, '홍길동' AS NAME
UNION ALL
SELECT 3 AS NUM, '국어' AS TITLE, 98.3 AS GRADES, '홍길동' AS NAME
UNION ALL
SELECT 4 AS NUM, '과학' AS TITLE, 75.4 AS GRADES, '홍길동' AS NAME
UNION ALL
SELECT 5 AS NUM, '지리' AS TITLE, 90.1 AS GRADES, '갑순이' AS NAME
UNION ALL
SELECT 6 AS NUM, '역사' AS TITLE, 60.0 AS GRADES, '갑순이' AS NAME

 

SELECT * FROM #TMP

 

DROP TABLE #TMP

 

  INTO #TMP으로 임시테이블을 생성하고 DROP TABLE #TMP로 임시테이블을 삭제합니다. 아래는 쿼리를 실행한 결과입니다.

 

 

 

 이번에는 ROW_NUMBER() 함수를 이용하여 순서(행번호)를 표시해 보겠습니다. 위 쿼리에서 마지막 두번째 쿼리만 변경을 하여 실행을 하시면 됩니다. ROW_NUMBER() 함수는 OVER, PARTITION BY, ORDER BY와 함께 사용합니다. PARTITION BY는 옵션사항으로 뒤에 설명을 하겠습니다.

 

 

 위 그림의 실행결과를 보면 ROW_NUM 컬럼이 추가 되어 있습니다. ORDER BY에 GRADES 컬럼을 지정을 하였습니다. GRADES 정렬기준으로 순서(행번호)를 표시한다는 의미 입니다. 여기서 주의해야 할점은 GRADES의 값이 90.10으로 똑같은 행이 있습니다. ROW_NUM 컬럼을 보면 같은 번호가 아니라 순차적으로 표시가 되고 있습니다(NUM 컬럼의 숫자가 6, 4, 5, 2, 1, 3 순서입니다).

 

 이번에는 DESC를 적용하여 역순으로 순서(행번호)를 표시해 보겠습니다.

 

 

 위 결과를 보면 역순으로 표시되는 것을 알 수 있습니다(NUM 컬럼의 숫자가 3, 1, 2, 5, 4, 6 순서입니다).

 

 PARTITION BY를 적용하면 어떻게 되는지 확인해 보겠습니다. ORDER BY 앞에 PARTITION BY를 추가하고 NAME 컬럼을 지정하였습니다. 쿼리를 실행하시면 아래같은 결과를 볼 수 있습니다.

 

 

 NAME 컬럼의 값을 그룹으로 하여(갑순이, 홍길동) ROW_NUM 컬럼의 순서(행번호)가 표시되는 것을 알 수 있습니다. 그룹별로 순서(또는 등수)를 알아야될때 유용합니다.

 

 

 

 이번에는 게시판에서 페이징을 구현하는 쿼리를 해보겠습니다. 아래 그림의 상단의 2줄은 기존의 데이터와 비교를 위해서 추가 하였습니다. 그리고 페이징 사이즈와 표시할 페이지 번호를 위한 변수를 선언 하였습니다. 마지막 5줄은 페이징을 적용한 쿼리입니다.

 

 

 위 그림의 두번째 쿼리 결과를 보면 페이징 크기가 2이고 두번째 페이지가 표시되는 것을 확인 할 수 있습니다.

 

 ROW_NUMBER()를 배워두시면 매우 유용합니다. ROW_NUMBER()를 응용할 수 있는 다른 방법이 있으시면 댓글로 남겨 공유해 주시기 바랍니다.