[SQL] T-SQL에서 정렬 된 테이블에서 행 M부터 N 행을 얻는 방법
SQLT-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 개 행을 쿼리, 즉 3 차원과 4 행이 반환됩니다.
해결법
-
==============================
1.나는 가장 우아한는 (MS SQL 2005 서버에서 사용 가능) ROW_NUMBER 함수를 사용하는 것 같다 :
나는 가장 우아한는 (MS SQL 2005 서버에서 사용 가능) ROW_NUMBER 함수를 사용하는 것 같다 :
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
-
==============================
2.이 스레드 및 제안에 대한 문제는 웹상의 모든 제안 된 솔루션 레코드의 수에 대한 선형 시간에 실행하는 것이 있습니다. 예를 들어, 다음과 같은 쿼리를 고려한다.
이 스레드 및 제안에 대한 문제는 웹상의 모든 제안 된 솔루션 레코드의 수에 대한 선형 시간에 실행하는 것이 있습니다. 예를 들어, 다음과 같은 쿼리를 고려한다.
select * from ( select Row_Number() over (order by ClusteredIndexField) as RowNumber, * from MyTable ) as PagedTable where RowNumber between @LowestRowNumber and @HighestRowNumber;
페이지 1을 얻는 경우, 쿼리는 0.577 초 정도 걸립니다. 페이지 15619을 받고 그러나,이 같은 쿼리 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;
마찬가지로 이전 예제에서, 나는 780928 개 레코드와 매우 넓은 테이블에이 테스트. 나는 15,619 페이지의 결과 (50)의 페이지 크기를 사용했다.
페이지 1 (첫 페이지) 총 시간은 0.413 초이다. 페이지 15619 (마지막 페이지)에 걸리는 총 시간은 0.987 초, 긴 페이지에서 1. 이러한 시간은 SQL Server 프로파일 러를 사용하여 측정하고, DBMS는 SQL 서버 2008 R2했다으로 단지 두 배입니다.
이 솔루션을 사용하여 인덱스하여 테이블을 정렬하는 모든 경우에 적용됩니다. 인덱스는 클러스터 또는 단순 할 필요가 없습니다. 내 경우, 인덱스가 세 개의 필드로 구성되었다 : VARCHAR (50) ASC, VARCHAR (15) ASC, 숫자 (19,0) 오름차순을. 성능이 성가신 지수에도 불구하고 우수한 것을 단지 더이 방법이 작동하는지 보여줍니다.
그러나, 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;
-
==============================
3.SQL 2012에서는 OFFSET 및 FETCH 사용할 수 있습니다 :
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의 수입니다.
-
==============================
4.당신은 25 일 기록에서 100 개 개의 레코드를 선택합니다 :
당신은 25 일 기록에서 100 개 개의 레코드를 선택합니다 :
select TOP 100 * from TableName where PrimaryKeyField NOT IN(Select TOP 24 PrimaryKeyField from TableName);
-
==============================
5.못생긴, hackish,하지만 작동합니다 :
못생긴, hackish,하지만 작동합니다 :
select top(M + N - 1) * from TableName except select top(N - 1) * from TableName
-
==============================
6.작은 결과를 아마 좋은 SQL의 모든 버전에서 작동 :
작은 결과를 아마 좋은 SQL의 모든 버전에서 작동 :
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
-
==============================
7.
-- *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 ;
-
==============================
8.
@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
이것은 하나의 방법입니다. 당신은 SQL 페이징 구글 경우 다른 사람이 많이 있습니다.
-
==============================
9.이 스레드는 꽤 오래된, 그러나 현재이 작업을 수행 할 수 있습니다 : 많은 청소기 이럴
이 스레드는 꽤 오래된, 그러나 현재이 작업을 수행 할 수 있습니다 : 많은 청소기 이럴
SELECT * FROM Sales.SalesOrderDetail ORDER BY SalesOrderDetailID OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY; GO
출처 : http://blog.sqlauthority.com/2013/12/30/sql-server-mysql-limit-and-offset-skip-and-return-only-next-few-rows-paging-solution/
-
==============================
10.당신은 당신이 원하는 행을 지정할 수 있도록 SQL 서버에서이 작업을 수행하기 위해, 당신은 열을 기준으로 쿼리를 주문해야합니다.
당신은 당신이 원하는 행을 지정할 수 있도록 SQL 서버에서이 작업을 수행하기 위해, 당신은 열을 기준으로 쿼리를 주문해야합니다.
이 일을 할 때 N 행이 다음 M 행을 가져 오프셋 사용한다은 "TOP"키워드를 사용할 수 없습니다.
예:
select * from table order by [some_column] offset 10 rows FETCH NEXT 10 rows only
당신은 자세한 내용은 여기를 배울 수 있습니다 : https://technet.microsoft.com/pt-br/library/gg699618%28v=sql.110%29.aspx
-
==============================
11.간단한 쿼리는 M + 테이블 1 번째 행에서 N 행을 나열합니다됩니다 다음. 원하는 숫자 M과 N을 교체합니다.
간단한 쿼리는 M + 테이블 1 번째 행에서 N 행을 나열합니다됩니다 다음. 원하는 숫자 M과 N을 교체합니다.
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
내가이 상황에 유용 여부를 알려 주시기 바랍니다.
-
==============================
12.그리고 이것은 당신이 기본 키가없는 테이블에 같은 목표를 달성 할 수있는 방법입니다 :
그리고 이것은 당신이 기본 키가없는 테이블에 같은 목표를 달성 할 수있는 방법입니다 :
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
-
==============================
13.나는 여기에 모든 응답을 읽고 마지막으로 간단하게 사용 가능한 솔루션을 함께했다. 성능 문제는 행 번호 자체의 문 BETWEEN 아닌 세대에서 발생한다. 그래서 페이지 번호와 레코드의 수를 전달하여 동적 페이징을 수행하는 알고리즘을 사용했다.
나는 여기에 모든 응답을 읽고 마지막으로 간단하게 사용 가능한 솔루션을 함께했다. 성능 문제는 행 번호 자체의 문 BETWEEN 아닌 세대에서 발생한다. 그래서 페이지 번호와 레코드의 수를 전달하여 동적 페이징을 수행하는 알고리즘을 사용했다.
당신이되지 않도록 2000이 값 저장 프로 시저 변수로 대체 될 수있다 - 이용권은 행과 행의 수를 시작하는 것이 아니라, 오히려 "페이지 당 행 (500)"행을 1501 것 "페이지 번호 (4)" 특정 페이징 량 사용에 고정.
select * from ( select (((ROW_NUMBER() OVER(ORDER BY MyField) - 1) / 500) + 1) AS PageNum , * from MyTable ) as PagedTable where PageNum = 4;
-
==============================
14.행 N에 대한 ID를 찾기 그런 것보다 아이디 이상이 있거나 그와 동일한 최고 M 행을 얻을
행 N에 대한 ID를 찾기 그런 것보다 아이디 이상이 있거나 그와 동일한 최고 M 행을 얻을
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
뭐 그런 ...하지만 난 여기에 몇 가지 가정을했습니다 (예를 들어 당신이 ID로 주문합니다)
-
==============================
15.당신은 많은 수의 행을 생략하는 경우가 prestanda 효과적인 경우 잘 모르겠어요하지만 T-SQL에 대한 꽤 솔직 방법은있다.
당신은 많은 수의 행을 생략하는 경우가 prestanda 효과적인 경우 잘 모르겠어요하지만 T-SQL에 대한 꽤 솔직 방법은있다.
SELECT TOP numberYouWantToTake [yourColumns...] FROM yourTable WHERE yourIDColumn NOT IN ( SELECT TOP numberYouWantToSkip yourIDColumn FROM yourTable ORDER BY yourOrderColumn ) ORDER BY yourOrderColumn
당신이 닷넷을 사용하는 경우, 당신은 당신의 데이터 결과를 IEnumerable 예에서 다음을 사용할 수 있습니다 :
IEnumerable<yourDataType> yourSelectedData = yourDataInAnIEnumerable.Skip(nubmerYouWantToSkip).Take(numberYouWantToTake);
이렇게하면 데이터 저장소에서 모든 데이터를 얻고 있다는 뒷면있다.
-
==============================
16.왜 두 개의 질의를하지 :
왜 두 개의 질의를하지 :
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;
-
==============================
17.
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
from https://stackoverflow.com/questions/758186/how-to-get-n-rows-starting-from-row-m-from-sorted-table-in-t-sql by cc-by-sa and MIT license
'SQL' 카테고리의 다른 글
[SQL] Laravel 고급 알의 방법 함수에 변수를 전달하는 방법? (0) | 2020.05.08 |
---|---|
[SQL] 정체성 대 순서 (0) | 2020.05.08 |
[SQL] SQL은 : 첫 글자를 대문자 만 [중복] (0) | 2020.05.08 |
[SQL] 어떻게 순수한 SQL에 LINQ의 .Skip (1000) .Take (100)을 작성하려면 어떻게해야합니까? (0) | 2020.05.07 |
[SQL] SQL 쿼리는 최소값과 별개의 행을 선택합니다 (0) | 2020.05.07 |