В PostgreSQL есть ключевые слова Limit
и Offset
, которые позволяют очень легко разбивать наборы результатов на страницы.
Каков эквивалентный синтаксис для SQL Server?
В PostgreSQL есть ключевые слова Limit
и Offset
, которые позволяют очень легко разбивать наборы результатов на страницы.
Каков эквивалентный синтаксис для SQL Server?
Эквивалент LIMIT
- SET ROWCOUNT
, но если вам нужна общая разбивка на страницы, лучше написать такой запрос:
;WITH Results_CTE AS
(
SELECT
Col1, Col2, ...,
ROW_NUMBER() OVER (ORDER BY SortCol1, SortCol2, ...) AS RowNum
FROM Table
WHERE <whatever>
)
SELECT *
FROM Results_CTE
WHERE RowNum >= @Offset
AND RowNum < @Offset + @Limit
Преимуществом здесь является параметризация смещения и ограничения на случай, если вы решите изменить параметры разбиения на страницы (или разрешите это сделать пользователю).
Примечание. параметр @Offset
должен использовать для этого индексацию с отсчетом от единицы, а не обычную индексацию с отсчетом от нуля.
WHERE RowNum >= (@Offset + 1)
- person Eric Herlitz; 03.04.2014
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified
. MSSQL2008 R2.
- person Paul; 26.08.2014
WHERE RowNum > @Offset
, и мы в порядке с условием запуска.
- person Damian Vogel; 12.01.2016
Table
имеет 200 тыс. Записей, он сначала извлечет все, а затем применит лимит? Этот запрос эффективен?
- person Jigar; 20.06.2016
left join
по этому запросу?
- person Drenyl; 22.02.2019
Эта функция теперь упрощена в SQL Server 2012. Это работает с SQL Server 2012 и далее.
Ограничьте со смещением, чтобы выбрать от 11 до 20 строк в SQL Server:
SELECT email FROM emailTable
WHERE user_id=3
ORDER BY Id
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;
ORDER BY
: требуетсяOFFSET
: необязательное количество пропущенных строкNEXT
: необходимое количество следующих строкСсылка: https://docs.microsoft.com/en-us/sql/t-sql/queries/select-order-by-clause-transact-sql
SQL_CALC_FOUND_ROWS
при использовании этого?
- person Petah; 24.03.2015
select top {LIMIT HERE} * from (
select *, ROW_NUMBER() over (order by {ORDER FIELD}) as r_n_n
from {YOUR TABLES} where {OTHER OPTIONAL FILTERS}
) xx where r_n_n >={OFFSET HERE}
Примечание. Это решение будет работать только в SQL Server 2005 или более поздней версии, так как это было когда ROW_NUMBER()
было реализовано.
AS xx
- person Concrete Gannet; 05.04.2016
Для этого вы можете использовать ROW_NUMBER в общем табличном выражении.
;WITH My_CTE AS
(
SELECT
col1,
col2,
ROW_NUMBER() OVER(ORDER BY col1) AS row_number
FROM
My_Table
WHERE
<<<whatever>>>
)
SELECT
col1,
col2
FROM
My_CTE
WHERE
row_number BETWEEN @start_row AND @end_row
Для меня использование OFFSET и FETCH вместе было медленным, поэтому я использовал комбинацию TOP и OFFSET, подобную этой (которая была быстрее):
SELECT TOP 20 * FROM (SELECT columname1, columname2 FROM tablename
WHERE <conditions...> ORDER BY columname1 OFFSET 100 ROWS) aliasname
Примечание. Если вы используете TOP и OFFSET вместе в одном запросе, например:
SELECT TOP 20 columname1, columname2 FROM tablename
WHERE <conditions...> ORDER BY columname1 OFFSET 100 ROWS
Затем вы получите сообщение об ошибке, поэтому для совместного использования TOP и OFFSET вам необходимо разделить его подзапросом.
И если вам нужно использовать SELECT DISTINCT, тогда запрос будет примерно таким:
SELECT TOP 20 FROM (SELECT DISTINCT columname1, columname2
WHERE <conditions...> ORDER BY columname1 OFFSET 100 ROWS) aliasname
Примечание. Использование SELECT ROW_NUMBER с DISTINCT не помогло мне.
SELECT TOP 20 id FROM table1 where id > 10 order by date OFFSET 20 rows
, вы должны преобразовать его, как SELECT TOP 20 * FROM (SELECT id FROM table1 where id > 10 order by date OFFSET 20 ROWS) t1
. Отредактирую свой ответ. Спасибо и извините за мой английский.
- person sebasdev; 09.05.2017
Добавляя небольшое изменение к решению Aaronaught, я обычно параметризую номер страницы (@PageNum) и размер страницы (@PageSize). Таким образом, каждое событие нажатия страницы просто отправляет запрошенный номер страницы вместе с настраиваемым размером страницы:
begin
with My_CTE as
(
SELECT col1,
ROW_NUMBER() OVER(ORDER BY col1) AS row_number
FROM
My_Table
WHERE
<<<whatever>>>
)
select * from My_CTE
WHERE RowNum BETWEEN (@PageNum - 1) * (@PageSize + 1)
AND @PageNum * @PageSize
end
В частности, для SQL-SERVER это можно сделать разными способами. В качестве реального примера мы взяли здесь таблицу Customer.
Пример 1. С SET ROWCOUNT
SET ROWCOUNT 10
SELECT CustomerID, CompanyName from Customers
ORDER BY CompanyName
Чтобы вернуть все строки, установите для ROWCOUNT значение 0.
SET ROWCOUNT 0
SELECT CustomerID, CompanyName from Customers
ORDER BY CompanyName
Пример 2: с «ROW_NUMBER and OVER»
With Cust AS
( SELECT CustomerID, CompanyName,
ROW_NUMBER() OVER (order by CompanyName) as RowNumber
FROM Customers )
select *
from Cust
Where RowNumber Between 0 and 10
Пример 3: с "СМЕЩЕНИЕМ и ПОЛУЧЕНИЕМ", но с этим "ЗАКАЗАТЬ ПО" является обязательным
SELECT CustomerID, CompanyName FROM Customers
ORDER BY CompanyName
OFFSET 0 ROWS
FETCH NEXT 10 ROWS ONLY
Надеюсь, это тебе поможет.
Другой образец:
declare @limit int
declare @offset int
set @offset = 2;
set @limit = 20;
declare @count int
declare @idxini int
declare @idxfim int
select @idxfim = @offset * @limit
select @idxini = @idxfim - (@limit-1);
WITH paging AS
(
SELECT
ROW_NUMBER() OVER (order by object_id) AS rowid, *
FROM
sys.objects
)
select *
from
(select COUNT(1) as rowqtd from paging) qtd,
paging
where
rowid between @idxini and @idxfim
order by
rowid;
здесь кто-то рассказывает о эта функция в sql 2011, грустно, что они выбрали немного другое ключевое слово «OFFSET / FETCH», но это не стандартно, тогда хорошо.
Самое близкое, что я мог сделать, это
select * FROM( SELECT *, ROW_NUMBER() over (ORDER BY ID ) as ct from [db].[dbo].[table] ) sub where ct > fromNumber and ct <= toNumber
Что, я думаю, похоже на select * from [db].[dbo].[table] LIMIT 0, 10
Поскольку еще никто не предоставил этот код:
SELECT TOP @limit f1, f2, f3...
FROM t1
WHERE c1 = v1, c2 > v2...
AND
t1.id NOT IN
(SELECT TOP @offset id
FROM t1
WHERE c1 = v1, c2 > v2...
ORDER BY o1, o2...)
ORDER BY o1, o2...
Важные точки:
@limit
можно заменить на количество результатов, которые нужно получить,@offset
- количество результатов, которые нужно пропуститьwhere
и order by
и даст неверные результаты, если они не синхронизированыorder by
явно присутствует, если это то, что нужноВ SQL-сервере вы должны использовать TOP вместе с ROW_NUMBER ()
Поскольку я тестирую этот сценарий больше раз, более полезный на 1 миллион записей на каждую страницу. 100 записей с разбивкой на страницы работают быстрее, мой компьютер выполняет этот сценарий 0 секунд, в то время как по сравнению с mysql имеет собственный предел и смещение около 4,5 секунд для получения результата.
Кто-то может не понимать, что Row_Number () всегда сортируется по определенному полю. В случае, если нам нужно определить только строку в последовательности, следует использовать:
SELECT TOP {LIMIT} * FROM (
SELECT TOP {LIMIT} + {OFFSET} ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS ROW_NO,*
FROM {TABLE_NAME}
) XX WHERE ROW_NO > {OFFSET}
Объяснять: