복붙노트

[SQL] SQL Server 2008의 페이지 매김을 수행하는 방법

SQL

SQL Server 2008의 페이지 매김을 수행하는 방법

어떻게 2008 SQL Server의 페이지 매김을해야합니까?

해결법

  1. ==============================

    1.당신은 뭔가를 시도 할 수 있습니다

    당신은 뭔가를 시도 할 수 있습니다

    DECLARE @Table TABLE(
        Val VARCHAR(50)
)

DECLARE @PageSize INT,
        @Page INT

SELECT  @PageSize = 10,
        @Page = 2

;WITH PageNumbers AS(
        SELECT Val,
                ROW_NUMBER() OVER(ORDER BY Val) ID
        FROM    @Table
)
SELECT  *
FROM    PageNumbers
WHERE   ID  BETWEEN ((@Page - 1) * @PageSize + 1)
        AND (@Page * @PageSize)
  2. ==============================

    2.당신은 ROW_NUMBER ()를 사용할 수 있습니다 :

    당신은 ROW_NUMBER ()를 사용할 수 있습니다 :

    예:

    WITH CTEResults AS
(
    SELECT IDColumn, SomeField, DateField, ROW_NUMBER() OVER (ORDER BY DateField) AS RowNum
    FROM MyTable
)

SELECT * 
FROM CTEResults
WHERE RowNum BETWEEN 10 AND 20;
  3. ==============================

    3.SQL 서버 2012 매김 기능을 (http://www.codeproject.com/Articles/442503/New-features-for-database-developers-in-SQL-Server 참조) 제공

    SQL 서버 2012 매김 기능을 (http://www.codeproject.com/Articles/442503/New-features-for-database-developers-in-SQL-Server 참조) 제공

    SQL2008에서 당신은이 방법을 수행 할 수 있습니다 :

    declare @rowsPerPage as bigint; 
declare @pageNum as bigint; 
set @rowsPerPage=25; 
set @pageNum=10;   

With SQLPaging As   ( 
    Select Top(@rowsPerPage * @pageNum) ROW_NUMBER() OVER (ORDER BY ID asc) 
    as resultNum, * 
    FROM Employee )
select * from SQLPaging with (nolock) where resultNum > ((@pageNum - 1) * @rowsPerPage)

    입증! 그것은 작동하고 지속적으로 확장 할 수 있습니다.

  4. ==============================

    4.1)를 작성 DUMMY DATA

    1)를 작성 DUMMY DATA

    CREATE TABLE #employee (EMPID INT IDENTITY, NAME VARCHAR(20))

DECLARE @id INT = 1

WHILE @id < 200

BEGIN
INSERT INTO #employee ( NAME ) VALUES ('employee_' + CAST(@id AS VARCHAR) )
SET @id = @id + 1
END

    2) NOW 솔루션을 적용합니다.

    이 사건은 독특하고 정렬 된 열 수 EMPID를 가정합니다.

    오프 물론, 당신은 그것을 다른 열을 적용합니다 ...

    DECLARE @pageSize INT = 20

SELECT * FROM (

SELECT *, PageNumber =  CEILING(CAST(EMPID AS FLOAT)/@pageSize)   
FROM #employee
) MyQuery

WHERE MyQuery.PageNumber = 1
  5. ==============================

    5.다음은 SQL 서버 측에서 쿼리의 결과를 페이징에 대한 내 솔루션입니다. 나는 하나 개의 컬럼에 의해 필터링 및 순서의 개념을 추가했습니다. 당신이 페이징하고있는 gridview에 필터링 및 주문 때 그것은 매우 효율적입니다.

    다음은 SQL 서버 측에서 쿼리의 결과를 페이징에 대한 내 솔루션입니다. 나는 하나 개의 컬럼에 의해 필터링 및 순서의 개념을 추가했습니다. 당신이 페이징하고있는 gridview에 필터링 및 주문 때 그것은 매우 효율적입니다.

    테스트하기 전에, 당신은 하나 개의 샘플 테이블을 만들고이 테이블에 일부 행을 삽입해야합니다 : (현실 세계에서는 절은 테이블 필드를 고려하는 경우 변경해야 어쩌면 당신은 어떤 선택의 주요 부분에 참여하고 하위 쿼리 한)

    Create Table VLT
(
    ID int IDentity(1,1),
    Name nvarchar(50),
    Tel Varchar(20)
)
GO


Insert INTO VLT
VALUES
    ('NAME' + Convert(varchar(10),@@identity),'FAMIL' +     Convert(varchar(10),@@identity))
GO 500000

    SQL 서버 2008에서는 CTE 개념을 사용할 수 있습니다. 그 때문에, 나는 SQL 서버에 대한 쿼리의 두 가지 유형을 쓴 2008+

    - SQL 서버 2008+

    DECLARE @PageNumber Int = 1200
DECLARE @PageSize INT = 200
DECLARE @SortByField int = 1 --The field used for sort by
DECLARE @SortOrder nvarchar(255) = 'ASC' --ASC or DESC
DECLARE @FilterType nvarchar(255) = 'None' --The filter type, as defined on the client side (None/Contain/NotContain/Match/NotMatch/True/False/)
DECLARE @FilterValue nvarchar(255) = '' --The value the user gave for the filter
DECLARE @FilterColumn int = 1 --The column to wich the filter is applied, represents the column number like when we send the information.

SELECT 
  Data.ID,
  Data.Name,
  Data.Tel
FROM
  (  
    SELECT 
      ROW_NUMBER() 
        OVER( ORDER BY 
                CASE WHEN @SortByField = 1 AND @SortOrder = 'ASC'
                      THEN VLT.ID END ASC,
                CASE WHEN @SortByField = 1 AND @SortOrder = 'DESC'
                      THEN VLT.ID END DESC,
                CASE WHEN @SortByField = 2 AND @SortOrder = 'ASC'
                      THEN VLT.Name END ASC,
                CASE WHEN @SortByField = 2 AND @SortOrder = 'DESC'
                      THEN VLT.Name END ASC,
                CASE WHEN @SortByField = 3 AND @SortOrder = 'ASC'
                      THEN VLT.Tel END ASC,
                CASE WHEN @SortByField = 3 AND @SortOrder = 'DESC'
                      THEN VLT.Tel END ASC
         ) AS RowNum
      ,*  
    FROM VLT 
    WHERE
      ( -- We apply the filter logic here
        CASE
          WHEN @FilterType = 'None' THEN 1

          -- Name column filter
          WHEN @FilterType = 'Contain' AND @FilterColumn = 1
            AND ( -- In this case, when the filter value is empty, we want to show everything.
                VLT.ID LIKE '%' + @FilterValue + '%'
               OR
                @FilterValue = ''
               ) THEN 1
          WHEN @FilterType = 'NotContain' AND @FilterColumn = 1
            AND ( -- In this case, when the filter value is empty, we want to show everything.
                VLT.ID NOT LIKE '%' + @FilterValue + '%'
               OR
                @FilterValue = ''
               ) THEN 1
          WHEN @FilterType = 'Match' AND @FilterColumn = 1
            AND VLT.ID = @FilterValue THEN 1
          WHEN @FilterType = 'NotMatch' AND @FilterColumn = 1
            AND VLT.ID <> @FilterValue THEN 1               

          -- Name column filter
          WHEN @FilterType = 'Contain' AND @FilterColumn = 2
            AND ( -- In this case, when the filter value is empty, we want to show everything.
                VLT.Name LIKE '%' + @FilterValue + '%'
               OR
                @FilterValue = ''
               ) THEN 1
          WHEN @FilterType = 'NotContain' AND @FilterColumn = 2
            AND ( -- In this case, when the filter value is empty, we want to show everything.
                VLT.Name NOT LIKE '%' + @FilterValue + '%'
               OR
                @FilterValue = ''
               ) THEN 1
          WHEN @FilterType = 'Match' AND @FilterColumn = 2
            AND VLT.Name = @FilterValue THEN 1
          WHEN @FilterType = 'NotMatch' AND @FilterColumn = 2
            AND VLT.Name <> @FilterValue THEN 1         

         -- Tel column filter   
         WHEN @FilterType = 'Contain' AND @FilterColumn = 3
            AND ( -- In this case, when the filter value is empty, we want to show everything.
                VLT.Tel LIKE '%' + @FilterValue + '%'
               OR
                @FilterValue = ''
               ) THEN 1
          WHEN @FilterType = 'NotContain' AND @FilterColumn = 3
            AND ( -- In this case, when the filter value is empty, we want to show everything.
                VLT.Tel NOT LIKE '%' + @FilterValue + '%'
               OR
                @FilterValue = ''
               ) THEN 1
          WHEN @FilterType = 'Match' AND @FilterColumn = 3
            AND VLT.Tel = @FilterValue THEN 1
          WHEN @FilterType = 'NotMatch' AND @FilterColumn = 3
            AND VLT.Tel <> @FilterValue THEN 1    

        END
      ) = 1   
  ) AS Data
WHERE Data.RowNum > @PageSize * (@PageNumber - 1)
  AND Data.RowNum <= @PageSize * @PageNumber
ORDER BY Data.RowNum

GO

    그리고 SQL 서버의 CTE와 두 번째 솔루션을 2008+

    DECLARE @PageNumber Int = 1200
DECLARE @PageSize INT = 200
DECLARE @SortByField int = 1 --The field used for sort by
DECLARE @SortOrder nvarchar(255) = 'ASC' --ASC or DESC
DECLARE @FilterType nvarchar(255) = 'None' --The filter type, as defined on the client side (None/Contain/NotContain/Match/NotMatch/True/False/)
DECLARE @FilterValue nvarchar(255) = '' --The value the user gave for the filter
DECLARE @FilterColumn int = 1 --The column to wich the filter is applied, represents the column number like when we send the information.

;WITH
  Data_CTE
  AS
  (  
    SELECT 
      ROW_NUMBER() 
        OVER( ORDER BY 
                CASE WHEN @SortByField = 1 AND @SortOrder = 'ASC'
                      THEN VLT.ID END ASC,
                CASE WHEN @SortByField = 1 AND @SortOrder = 'DESC'
                      THEN VLT.ID END DESC,
                CASE WHEN @SortByField = 2 AND @SortOrder = 'ASC'
                      THEN VLT.Name END ASC,
                CASE WHEN @SortByField = 2 AND @SortOrder = 'DESC'
                      THEN VLT.Name END ASC,
                CASE WHEN @SortByField = 3 AND @SortOrder = 'ASC'
                      THEN VLT.Tel END ASC,
                CASE WHEN @SortByField = 3 AND @SortOrder = 'DESC'
                      THEN VLT.Tel END ASC
         ) AS RowNum
      ,*  
    FROM VLT
    WHERE
      ( -- We apply the filter logic here
        CASE
          WHEN @FilterType = 'None' THEN 1

          -- Name column filter
          WHEN @FilterType = 'Contain' AND @FilterColumn = 1
            AND ( -- In this case, when the filter value is empty, we want to show everything.
                VLT.ID LIKE '%' + @FilterValue + '%'
               OR
                @FilterValue = ''
               ) THEN 1
          WHEN @FilterType = 'NotContain' AND @FilterColumn = 1
            AND ( -- In this case, when the filter value is empty, we want to show everything.
                VLT.ID NOT LIKE '%' + @FilterValue + '%'
               OR
                @FilterValue = ''
               ) THEN 1
          WHEN @FilterType = 'Match' AND @FilterColumn = 1
            AND VLT.ID = @FilterValue THEN 1
          WHEN @FilterType = 'NotMatch' AND @FilterColumn = 1
            AND VLT.ID <> @FilterValue THEN 1               

          -- Name column filter
          WHEN @FilterType = 'Contain' AND @FilterColumn = 2
            AND ( -- In this case, when the filter value is empty, we want to show everything.
                VLT.Name LIKE '%' + @FilterValue + '%'
               OR
                @FilterValue = ''
               ) THEN 1
          WHEN @FilterType = 'NotContain' AND @FilterColumn = 2
            AND ( -- In this case, when the filter value is empty, we want to show everything.
                VLT.Name NOT LIKE '%' + @FilterValue + '%'
               OR
                @FilterValue = ''
               ) THEN 1
          WHEN @FilterType = 'Match' AND @FilterColumn = 2
            AND VLT.Name = @FilterValue THEN 1
          WHEN @FilterType = 'NotMatch' AND @FilterColumn = 2
            AND VLT.Name <> @FilterValue THEN 1         

         -- Tel column filter   
         WHEN @FilterType = 'Contain' AND @FilterColumn = 3
            AND ( -- In this case, when the filter value is empty, we want to show everything.
                VLT.Tel LIKE '%' + @FilterValue + '%'
               OR
                @FilterValue = ''
               ) THEN 1
          WHEN @FilterType = 'NotContain' AND @FilterColumn = 3
            AND ( -- In this case, when the filter value is empty, we want to show everything.
                VLT.Tel NOT LIKE '%' + @FilterValue + '%'
               OR
                @FilterValue = ''
               ) THEN 1
          WHEN @FilterType = 'Match' AND @FilterColumn = 3
            AND VLT.Tel = @FilterValue THEN 1
          WHEN @FilterType = 'NotMatch' AND @FilterColumn = 3
            AND VLT.Tel <> @FilterValue THEN 1    

        END
      ) = 1     
  )

SELECT 
  Data.ID,
  Data.Name,
  Data.Tel
FROM Data_CTE AS Data
WHERE Data.RowNum > @PageSize * (@PageNumber - 1)
  AND Data.RowNum <= @PageSize * @PageNumber
ORDER BY Data.RowNum
  6. ==============================

    6.적어도 SQL 2005에서 작동하는 또 다른 해결책은, BY 절 SELECT 서브 쿼리 및 ORDER와 TOP을 사용하는 것입니다.

    적어도 SQL 2005에서 작동하는 또 다른 해결책은, BY 절 SELECT 서브 쿼리 및 ORDER와 TOP을 사용하는 것입니다.

    요컨대, 페이지 당 10 개의 행과 2 행 페이지를 검색하는 제 20 행의 마지막 10 개의 행을 검색하는 것과 동일하다. 이는 다시 주문 ASC를 사용하기 전에, ASC 순으로 제 20 개 행을 검색로 변환하고 DESC 순서와 다음 제 10 행.

    예 : 페이지 당 3 행 오는 2 페이지 행

    create table test(id integer);
insert into test values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);

select * 
    from (
        select top 2 * 
            from (
                select  top (4) * 
                    from test 
                    order by id asc) tmp1
            order by id desc) tmp1 
    order by id asc
  7. ==============================

    7.

    SELECT DISTINCT Id,ParticipantId,ActivityDate,IsApproved,
    IsDeclined,IsDeleted,SubmissionDate,    IsResubmitted,  

    [CategoryId] Id,[CategoryName] Name,

    [ActivityId] [Id],[ActivityName] Name,Points,   

    [UserId] [Id],Email,
    ROW_NUMBER() OVER(ORDER BY Id desc)   AS RowNum from
    (SELECT DISTINCT
    Id,ParticipantId,
    ActivityDate,IsApproved,
    IsDeclined,IsDeleted,
    SubmissionDate, IsResubmitted,  

    [CategoryId] [CategoryId],[CategoryName] [CategoryName],

    [ActivityId] [ActivityId],[ActivityName] [ActivityName],Points, 

    [UserId] [UserId],Email,
    ROW_NUMBER() OVER(ORDER BY Id desc)   AS RowNum from

     (SELECT DISTINCT ASN.Id,
    ASN.ParticipantId,ASN.ActivityDate,
    ASN.IsApproved,ASN.IsDeclined,
    ASN.IsDeleted,ASN.SubmissionDate,
    CASE WHEN (SELECT COUNT(*) FROM FDS_ActivitySubmission WHERE ParentId=ASN.Id)>0 THEN CONVERT(BIT, 1) ELSE CONVERT(BIT, 0) END IsResubmitted,

    AC.Id [CategoryId], AC.Name [CategoryName],

    A.Id [ActivityId],A.Name [ActivityName],A.Points,

    U.Id[UserId],U.Email    


FROM
FDS_ActivitySubmission ASN WITH (NOLOCK)
INNER JOIN  
    FDS_ActivityCategory AC WITH (NOLOCK)
ON 
    AC.Id=ASN.ActivityCategoryId
        INNER JOIN
    FDS_ApproverDetails FDSA
ON
FDSA.ParticipantID=ASN.ParticipantID

        INNER JOIN
       FDS_ActivityJobRole FAJ
ON
     FAJ.RoleId=FDSA.JobRoleId
    INNER JOIN

    FDS_Activity A WITH (NOLOCK)
ON 
    A.Id=ASN.ActivityId
INNER JOIN
   Users U WITH (NOLOCK)
ON
    ASN.ParticipantId=FDSA.ParticipantID
WHERE
       IsDeclined=@IsDeclined AND IsApproved=@IsApproved    AND ASN.IsDeleted=0
       AND
       ISNULL(U.Id,0)=ISNULL(@ApproverId,0)
       AND ISNULL(ASN.IsDeleted,0)<>1)P)t where t.RowNum between 
       (((@PageNumber - 1) * @PageSize) + 1) AND (@PageNumber * PageSize)
    AND t.IsDeclined=@IsDeclined AND t.IsApproved=@IsApproved AND t.IsDeleted = 0
 AND (ISNULL(t.Id,0)=ISNULL(@SubmissionId,0)or ISNULL(@SubmissionId,0)<=0)

    8. from https://stackoverflow.com/questions/2244322/how-to-do-pagination-in-sql-server-2008 by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] 정수 진수와 진수로 정수로 변환  (0) 2020.03.30
[SQL] 는 SQL 표준 역 따옴표 ( ')의 사용에 대해 무엇을 말하는가?  (0) 2020.03.30
[SQL] (가) SQL 또는 MySQL의에서 키워드 조인을 사용하지 않는 조인이 뭔가 잘못인가?  (0) 2020.03.30
[SQL] 열 이름을 탈출 SQL 표준?  (0) 2020.03.30
[SQL] 외래 키 테이블 열은 NULL이 될 수 있습니까?  (0) 2020.03.30
,

티스토리툴바