본문 바로가기
㏈ª By β┖υΕJini/MS-SQL

pagesing Query ^^

by ㏈ª ☞ β┖υΕJini.κR 2007. 10. 30.
SELECT TOP page_size * FROM table WHERE primary_key NOT IN
    (SELECT TOP page_size * (page_number - 1) primary_key FROM table
     WHERE filter_conditions
     ORDER BY sort_field)
AND filter_criteria
ORDER BY sort_field



[리스트 2] 기본키에 검색조건을 먼저 설정하는 일반화 쿼리
SELECT * FROM table WHERE primary key IN
    (SELECT TOP page_size primary_key FROM table
     WHERE primary_key NOT IN

          (SELECT TOP page_size * (page_number - 1) primary_key FROM table
           WHERE filter_conditions ORDER BY sort_field) AND filter_criteria
     ORDER BY sort_field)
ORDER BY sort_field


[리스트 3] SELECT_WITH_PAGING 저장 프로시저
CREATE PROCEDURE SELECT_WITH_PAGING (
@strFields varchar(4000),
@strPK varchar(100),
@strTables varchar(4000),
@intPageNo int = 1,
@intPageSize int = NULL,
@blnGetRecordCount bit = 0,
@strFilter varchar(8000) = NULL,
@strSort varchar(8000) = NULL,
@strGroup varchar(8000) = NULL)
/* 매개변수에 따라 반환되는 결과값을 특정 페이지로 정의하거나 전체 행을 모두 반환할 수 있도록 설정한다. */
AS
DECLARE @blnBringAllRecords bit
DECLARE @strPageNo varchar(50)
DECLARE @strPageSize varchar(50)
DECLARE @strSkippedRows varchar(50)
DECLARE @strFilterCriteria varchar(8000)
DECLARE @strSimpleFilter varchar(8000)
DECLARE @strSortCriteria varchar(8000)
DECLARE @strGroupCriteria varchar(8000)
DECLARE @intRecordcount int
DECLARE @intPagecount int
/* 페이징 조건 정규화 의미있는 페이징 조건이 입력되지 않은 경우, 페이징하지 않고 좀 더 효율적인 방법으로 쿼리를 실행시키기 위해 blnBringAllRecords 플래그를 사용 */
IF @intPageNo < 1
SET @intPageNo = 1
SET @strPageNo = CONVERT(varchar(50), @intPageNo)
IF @intPageSize IS NULL OR @intPageSize < 1
?- 페이징하지 않고 전체 행을 반환
   SET @blnBringAllRecords = 1
ELSE
   BEGIN
     SET @blnBringAllRecords = 0
     SET @strPageSize = CONVERT(varchar(50), @intPageSize)
     SET @strPageNo = CONVERT(varchar(50), @intPageNo)
     SET @strSkippedRows = CONVERT(varchar(50), @intPageSize * (@intPageNo - 1))
   END
/* 정렬 및 필터링 조건 정규화 정렬 및 필터링 조건이 지정되지 않으면, 필터링이나 정렬작업이 수행되지 않도록 하여 쿼리의 성능을 향상시킴.*/
IF @strFilter IS NOT NULL AND @strFilter != ''
   BEGIN
     SET @strFilterCriteria = ' WHERE ' + @strFilter + ' '
     SET @strSimpleFilter = ' AND ' + @strFilter + ' '
   END
ELSE
   BEGIN
     SET @strSimpleFilter = ''
     SET @strFilterCriteria = ''
   END
IF @strSort IS NOT NULL AND @strSort != ''
   SET @strSortCriteria = ' ORDER BY ' + @strSort + ' '
ELSE
   SET @strSortCriteria = ''
IF @strGroup IS NOT NULL AND @strGroup != ''
   SET @strGroupCriteria = 'GROUP BY' + @strGroup + ' '
ELSE
   SET @strGroupCriteria = ''
/* 실제 조회작업을 시작 */
IF @blnBringAllRecords = 1 -- 페이징 하지 않고 단순한 SELECT 문장만을 실행
   BEGIN

     EXEC (
               'SELECT ' + @strFields + 'FROM' + @strTables + @strFilterCriteria +
               @strGroupCriteria + @strSortCriteria
               )
   END
-- 전체 레코드를 반환.
ELSE -- 지정된 페이지를 반환
   BEGIN
     IF @intPageNo = 1 -- 맨 처음 페이지를 찾기 때문에 서브쿼리가 없어서 가장 효율적으로 실

                                 행된다.
        EXEC (
                  'SELECT TOP' + @strPageSize + ' ' + @strFields + 'FROM' + @strTables +
                  @strFilterCriteria + @strGroupCriteria + @strSortCriteria
                 )
     ELSE -- 특정 페이지를 선택하기 위해 서브쿼리 구조를 실행한다.
        EXEC (
                   'SELECT' + @strFields + 'FROM' + @strTables + 'WHERE' + @strPK + 'IN' + '
                        (SELECT TOP' + @strPageSize + ' ' + @strPK + 'FROM' + @strTables +
                       ' WHERE' + @strPK + 'NOT IN' + '
                            (SELECT TOP' + @strSkippedRows + ' ' + @strPK + 'FROM' + @strTables +
                             @strFilterCriteria + @strGroupCriteria + @strSortCriteria + ') ' +
                             @strSimpleFilter +
                             @strGroupCriteria +
                         @strSortCriteria + ') ' +
                    @strGroupCriteria +
                    @strSortCriteria
                 )
   END -- 특정 페이지를 지정한 경우
/* 전체 레코드 수를 반환하도록 지정된 경우 */
IF @blnGetRecordCount = 1
IF @strGroupCriteria != ''
    EXEC (
               'SELECT COUNT(*) AS RECORDCOUNT FROM (SELECT COUNT(*) FROM' +
                @strTables + @strFilterCriteria + @strGroupCriteria + ') AS tbl (id)
             )
ELSE
    EXEC (
               'SELECT COUNT(*) AS RECORDCOUNT FROM' + @strTables + @strFilterCriteria

               + @strGroupCriteria)
GO