T-SQL의 정렬 된 테이블에서 M 행부터 N 행을 가져 오는 방법

Nice programing

T-SQL의 정렬 된 테이블에서 M 행부터 N 행을 가져 오는 방법

nicepro 2020. 11. 23. 19:58

T-SQL의 정렬 된 테이블에서 M 행부터 N 행을 가져 오는 방법

테이블에서 상위 N 개 행을 가져 오는 간단한 방법이 있습니다.

SELECT TOP 10 * FROM MyTable ORDER BY MyColumn

N 행에서 시작하는 M 행을 쿼리하는 효율적인 방법이 있습니까?

예를 들면

Id Value
1    a
2    b
3    c
4    d
5    e
6    f

그리고 다음과 같은 쿼리

SELECT [3,2] * FROM MyTable ORDER BY MyColumn /* hypothetical syntax */

3d 행에서 시작하여 2 행, 즉 3d 및 4 번째 행을 쿼리합니다.

업데이트 SQL 2012를 사용하는 경우 새로운 구문이 추가되어이를 정말 쉽게 만들 수 있습니다. 이 쿼리로 페이징 (건너 뛰기 / 받기) 기능 구현을 참조하세요.

가장 우아한 방법은 ROW_NUMBER 함수 (MS SQL Server 2005에서 사용 가능)를 사용하는 것입니다.

WITH NumberedMyTable AS
(
    SELECT
        Id,
        Value,
        ROW_NUMBER() OVER (ORDER BY Id) AS RowNumber
    FROM
        MyTable
)
SELECT
    Id,
    Value
FROM
    NumberedMyTable
WHERE
    RowNumber BETWEEN @From AND @To

이 스레드와 웹의 다른 곳에서 제안 된 문제는 모든 제안 된 솔루션이 레코드 수와 관련하여 선형 시간으로 실행된다는 것입니다. 예를 들어 다음과 같은 쿼리를 고려하십시오.

select *
from
(
    select
        Row_Number() over (order by ClusteredIndexField) as RowNumber,
        *
    from MyTable
) as PagedTable
where RowNumber between @LowestRowNumber and @HighestRowNumber;

페이지 1을 가져올 때 쿼리에 0.577 초가 걸립니다. 그러나 15,619 페이지를 가져올 때이 동일한 쿼리는 2 분 55 초 이상 걸립니다.

다음 쿼리와 같이 레코드 번호, 인덱스 크로스 테이블을 생성하여이를 크게 개선 할 수 있습니다. 교차 테이블은 PagedTable이라고하며 비 영구적입니다.

select *
from
(
    select
        Row_Number() over (order by Field1 asc, Field2 asc, Field3 asc) as RowNumber,
        ClusteredIndexField
    from MyTable
) as PagedTable
left join MyTable on MyTable.ClusteredIndexField = PagedTable.ClusteredIndexField
where RowNumber between @LowestRowNumber and @HighestRowNumber;

이전 예제에서와 같이 780,928 개의 레코드가있는 매우 넓은 테이블에서 이것을 테스트했습니다. 50 페이지 크기를 사용하여 15,619 페이지가되었습니다.

페이지 1 (첫 페이지)에 소요 된 총 시간은 0.413 초입니다. 15,619 페이지 (마지막 페이지)에 소요 된 총 시간은 0.987 초로 1 페이지의 두 배에 불과합니다.이 시간은 SQL Server Profiler를 사용하여 측정되었으며 DBMS는 SQL Server 2008 R2입니다.

이 솔루션은 인덱스별로 테이블을 정렬 할 때 어떤 경우에도 작동합니다. 인덱스는 클러스터되거나 단순 할 필요가 없습니다. 필자의 경우 인덱스는 varchar (50) asc, varchar (15) asc, numeric (19,0) asc의 세 가지 필드로 구성되었습니다. 번거로운 색인에도 불구하고 성능이 우수하다는 것은이 접근 방식이 효과가 있음을 보여줍니다.

그러나 Row_Number 윈도우 함수의 order by 절이 인덱스에 해당하는 것이 중요합니다. 그렇지 않으면 성능이 첫 번째 예와 동일한 수준으로 저하됩니다.

이 접근 방식은 비 영구 교차 테이블을 생성하기 위해 여전히 선형 작업이 필요하지만 행 번호가 추가 된 인덱스 일 뿐이므로 매우 빠르게 발생합니다. 제 경우에는 0.347 초가 걸렸지 만 제 경우에는 복사해야하는 varchar가있었습니다. 단일 숫자 인덱스는 시간이 훨씬 적게 걸립니다.

모든 실제 목적을 위해이 디자인은 서버 측 페이징의 확장을 선형 연산에서 로그 연산으로 줄여서 큰 테이블의 확장을 허용합니다. 다음은 완전한 솔루션입니다.

-- For a sproc, make these your input parameters
declare
    @PageSize int = 50,
    @Page int = 15619;

-- For a sproc, make these your output parameters
declare @RecordCount int = (select count(*) from MyTable);
declare @PageCount int = ceiling(convert(float, @RecordCount) / @PageSize);
declare @Offset int = (@Page - 1) * @PageSize;
declare @LowestRowNumber int = @Offset;
declare @HighestRowNumber int = @Offset + @PageSize - 1;

select
    @RecordCount as RecordCount,
    @PageCount as PageCount,
    @Offset as Offset,
    @LowestRowNumber as LowestRowNumber,
    @HighestRowNumber as HighestRowNumber;

select *
from
(
    select
        Row_Number() over (order by Field1 asc, Field2 asc, Field3 asc) as RowNumber,
        ClusteredIndexField
    from MyTable
) as PagedTable
left join MyTable on MyTable.ClusteredIndexField = PagedTable.ClusteredIndexField
where RowNumber between @LowestRowNumber and @HighestRowNumber;

SQL 2012 에서는 OFFSET및 FETCH다음을 사용할 수 있습니다 .

SELECT *
FROM MyTable
ORDER BY MyColumn
OFFSET @N ROWS
FETCH NEXT @M ROWS ONLY;

나는 개인적으로 선호한다 :

DECLARE @CurrentSetNumber int = 0;
DECLARE @NumRowsInSet int = 2;

SELECT *
FROM MyTable
ORDER BY MyColumn
OFFSET @NumRowsInSet * @CurrentSetNumber ROWS
FETCH NEXT @NumRowsInSet ROWS ONLY;

SET @CurrentSetNumber = @CurrentSetNumber + 1;

여기서는 @NumRowsInSet반환 할 행 수이고 @CurrentSetNumber는 @NumRowsInSet건너 뛸 수입니다 .

25 번째 레코드에서 100 개의 레코드를 선택하려면 :

select TOP 100 * from TableName
where PrimaryKeyField 
   NOT IN(Select TOP 24 PrimaryKeyField from TableName);

추악하고 끔찍하지만 작동해야합니다.

select top(M + N - 1) * from TableName
except
select top(N - 1) * from TableName

아마도 작은 결과에 적합하며 모든 버전의 TSQL에서 작동합니다.

SELECT 
        * 
FROM
     (SELECT TOP (N) * 
      FROM 
            (SELECT TOP (M + N - 1) 
             FROM 
                   Table
             ORDER BY 
                      MyColumn) qasc
      ORDER BY 
               MyColumn DESC) qdesc
 ORDER BY 
         MyColumn

        -- *some* implementations may support this syntax (mysql?)
SELECT Id,Value
FROM xxx
ORDER BY Id
LIMIT 2 , 0
   ;

        -- Separate LIMIT, OFFSET
SELECT Id,Value
FROM xxx
ORDER BY Id
LIMIT 2 OFFSET 2
   ;

        -- SQL-2008 syntax
SELECT Id,Value
FROM xxx
ORDER BY Id
OFFSET 4
FETCH NEXT 2 ROWS ONLY
  ;

@start = 3
@records = 2

Select ID, Value 
From
(SELECT ROW_NUMBER() OVER(ORDER BY ID) AS RowNum, ID,Value 
From MyTable) as sub
Where sub.RowNum between @start and @start+@records

이것은 한 가지 방법입니다. Google SQL Paging을 사용하면 다른 많은 것들이 있습니다.

이 스레드는 꽤 오래되었지만 현재는 이렇게 할 수 있습니다. 훨씬 깔끔한 imho

SELECT *
FROM Sales.SalesOrderDetail
ORDER BY SalesOrderDetailID
OFFSET 20 ROWS
FETCH NEXT 10 ROWS ONLY;
GO

source: http://blog.sqlauthority.com/2013/12/30/sql-server-mysql-limit-and-offset-skip-and-return-only-next-few-rows-paging-solution/

In order to do this in SQL Server, you must order the query by a column, so you can specify the rows you want.

You can't use the "TOP" keyword when doing this, you must use offset N rows fetch next M rows.

Example:

select * from table order by [some_column] 
offset 10 rows
FETCH NEXT 10 rows only

You can learn more here: https://technet.microsoft.com/pt-br/library/gg699618%28v=sql.110%29.aspx

Following is the simple query will list N rows from M+1th row of the table. Replace M and N with your preferred numbers.

Select Top N B.PrimaryKeyColumn from 
    (SELECT 
        top M PrimaryKeyColumn
     FROM 
        MyTable
) A right outer join MyTable B 
on 
    A.PrimaryKeyColumn = B.PrimaryKeyColumn
where 
    A.PrimaryKeyColumn IS NULL

Please let me know whether this is usefull for your situation.

And this is how you can achieve same goal on tables without primary key:

select * from
(
    select row_number() over(order by (select 0)) rowNum,*
    from your_table
) tmp
where tmp.rowNum between 20 and 30 -- any numbers you need

I read all of the responses here and finally came up with a usable solution that is simple. The performance issues arise from the BETWEEN statement, not the generation of the row numbers themselves. So I used an algorithm to do dynamic paging by passing the page number and the number of records.

The passes are not start row and number of rows, but rather "rows per page (500)" and "page number (4)" which would be rows 1501 - 2000. These values can be replaced by stored procedure variables so you are not locked into using a specific paging amount.

select * from (
    select
        (((ROW_NUMBER() OVER(ORDER BY MyField) - 1) / 500) + 1) AS PageNum
        , *
    from MyTable
) as PagedTable
where PageNum = 4;

Find id for row N Then get the top M rows that have an id greater than or equal to that

declare @N as int
set @N = 2
declare @M as int
set @M = 3

declare @Nid as int

set @Nid = max(id)
from
  (select top @N *
from MyTable
order by id)

select top @M *
from MyTable
where id >= @Nid
order by id

Something like that ... but I've made some assumptions here (e.g. you want to order by id)

There is a pretty straight-forward method for T-SQL, although I'm not sure if it is prestanda-effective if you're skipping a large number of rows.

SELECT TOP numberYouWantToTake 
    [yourColumns...] 
FROM yourTable 
WHERE yourIDColumn NOT IN (
    SELECT TOP numberYouWantToSkip 
        yourIDColumn 
    FROM yourTable 
    ORDER BY yourOrderColumn
)
ORDER BY yourOrderColumn

If you're using .Net, you can use the following on for example an IEnumerable with your data results:

IEnumerable<yourDataType> yourSelectedData = yourDataInAnIEnumerable.Skip(nubmerYouWantToSkip).Take(numberYouWantToTake);

This has the backside that you're getting all the data from the data storage.

Why not do two queries:

select top(M+N-1) * from table into temp tmp_final with no log;
select top(N-1) * from tmp_final order by id desc;

SELECT * FROM (
  SELECT
    Row_Number() Over (Order by (Select 1)) as RawKey,
    * 
  FROM [Alzh].[dbo].[DM_THD_TRANS_FY14]
) AS foo
WHERE RawKey between 17210400 and 17210500

참고URL : https://stackoverflow.com/questions/758186/how-to-get-n-rows-starting-from-row-m-from-sorted-table-in-t-sql

'Nice programing' 카테고리의 다른 글

글꼴 색상을 변경하는 방법은 무엇입니까? (0)	2020.11.23
암시 적으로 형식화 된 지역 변수 사용 (0)	2020.11.23
CSS가있는 물결 모양 (0)	2020.11.22
모든 Excel 시트 끝에 명명 된 시트를 추가하는 방법은 무엇입니까? (0)	2020.11.22
Windows에서 Rails v4.1.0 서버를 시작하는 동안 TZInfo :: DataSourceNotFound 오류 (0)	2020.11.22

현재글T-SQL의 정렬 된 테이블에서 M 행부터 N 행을 가져 오는 방법

nicepro

T-SQL의 정렬 된 테이블에서 M 행부터 N 행을 가져 오는 방법

T-SQL의 정렬 된 테이블에서 M 행부터 N 행을 가져 오는 방법

'Nice programing' 카테고리의 다른 글

'Nice programing'의 다른글

티스토리툴바

T-SQL의 정렬 된 테이블에서 M 행부터 N 행을 가져 오는 방법

T-SQL의 정렬 된 테이블에서 M 행부터 N 행을 가져 오는 방법

'Nice programing' 카테고리의 다른 글

'Nice programing'의 다른글

관련글

티스토리툴바