-- 첫번째 방법(토탈값 구할때 조인해서 구하는 방법)
SET ROWCOUNT 12000 -- 페이지 * rows
SELECT ROWNUM = IDENTITY(6),
*
INTO #ATHENA_DVD_USER_INFO_TEMP
FROM ATHENA_DVD_USER_INFO
WHERE USER_ID LIKE '1%'
SELECT A.*, B.TOTAL
FROM #ATHENA_DVD_USER_INFO_TEMP A,
(SELECT COUNT(*) TOTAL
FROM ATHENA_DVD_USER_INFO
WHERE USER_ID LIKE '1%') B
WHERE ROWNUM BETWEEN 11991 AND 12000 -- (페이지- 1) * rows +1 and 페이지 * rows
SET ROWCOUNT 0
DROP TABLE #ATHENA_DVD_USER_INFO_TEMP
-- 두번째 방법(토탈값 구할때 DECLARE 이용해서 구하는 방법)
DECLARE @TOTAL INT
SELECT @TOTAL = COUNT(*)
FROM ATHENA_DVD_USER_INFO
WHERE USER_ID LIKE '1%'
SET ROWCOUNT 12000 -- 페이지 * rows
SELECT ROWNUM = IDENTITY(6),
*
INTO #ATHENA_DVD_USER_INFO_TEMP
FROM ATHENA_DVD_USER_INFO
WHERE USER_ID LIKE '1%'
SELECT *, @TOTAL TOTAL
FROM #ATHENA_DVD_USER_INFO_TEMP
WHERE ROWNUM BETWEEN 11991 AND 12000 -- (페이지- 1) * rows +1 and 페이지 * rows
SET ROWCOUNT 0
DROP TABLE #ATHENA_DVD_USER_INFO_TEMP