TOP (Transact-SQL)
Область применения: SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure конечной точке аналитики платформы Аналитики Azure Synapse Analytics (PDW) в Microsoft Fabric Хранилище в Microsoft Fabric
Ограничивает число строк, возвращаемых в результирующем наборе запроса до заданного числа или процентного значения в SQL Server. Если вы используете TOP с предложением ORDER BY, в результирующий набор включаются только первые N строк отсортированного результата. В противном случае TOP возвращает N строк в неопределенном порядке. Это предложение позволяет ограничить число строк, возвращаемых инструкцией SELECT. Также TOP можно использовать для указания числа строк, затрагиваемых инструкциями INSERT, UPDATE, MERGE или DELETE.
Соглашения о синтаксисе Transact-SQL
Синтаксис
Ниже приводится синтаксис для SQL Server и Базы данных SQL Azure:
[
TOP (expression) [PERCENT]
[ WITH TIES ]
]
Ниже приводится синтаксис для Azure Synapse Analytics и Системы платформы аналитики (PDW):
[
TOP ( expression )
[ WITH TIES ]
]
Примечание.
Сведения о синтаксисе Transact-SQL для SQL Server 2014 (12.x) и более ранних версиях см . в документации по предыдущим версиям.
Аргументы
выражение
Числовое выражение, определяющее количество возвращаемых строк. Если значение expression указано в процентах, оно неявно преобразуется в формат float. В противном случае expression преобразуется в формат bigint.
ПРОЦЕНТ
Указывает на то, что запрос возвращает только первые expression процентов строк из результирующего набора. Дробные значения округляются до следующего целого числа.
WITH TIES
Возвращает две или более строки, которые соперничают за последнее место в ограниченном результирующем наборе. Этот аргумент можно использовать только с предложением ORDER BY. В режиме WITH TIES количество возвращаемых строк может превысить значение expression. Например, если expression имеет значение 5 и существуют еще 2 строки с такими же значениями столбцов, указанных в предложении ORDER BY, что и у пятой строки, результирующий набор будет содержать 7 строк.
Предложение TOP можно сочетать с аргументом WITH TIES только в инструкциях SELECT, и только если вместе с ними задано предложение ORDER BY. Порядок возврата связанных записей произволен. ORDER BY не влияет на это правило.
Рекомендации
В инструкции SELECT всегда указывайте ORDER BY вместе с предложением TOP. Дело в том, что это единственный предсказуемый способ отбора строк предложением TOP.
Для реализации решения разбиения на страницы пользуйтесь предложениями OFFSET и FETCH в предложении ORDER BY, а не предложением TOP. Решение разбиения на страницы (т.е. постраничной выдачи данных клиенту) проще реализовать с помощью предложений OFFSET и FETCH. Дополнительные сведения см. в разделе Предложение ORDER BY (Transact-SQL).
Для ограничения числа возвращаемых строк пользуйтесь TOP (или OFFSET и FETCH), а не SET ROWCOUNT. Эти методы предпочтительнее, чем SET ROWCOUNT, по следующим причинам:
- Являясь частью инструкции SELECT, оптимизатор запросов может принимать значение expression в предложениях TOP или FETCH во время оптимизации запроса. Так как SET ROWCOUNT используется вне инструкции, выполняющей запрос, значение не может быть учтено в плане запроса.
Поддержка совместимости
В целях обратной совместимости скобки в инструкции SELECT необязательны, если выражение является целочисленной константой. Мы рекомендуем всегда использовать круглые скобки для аргумента TOP в инструкциях SELECT. Такой подход обеспечивает согласованность с синтаксисом инструкций INSERT, UPDATE, MERGE и DELETE.
Совместимость
Выражение TOP не влияет на другие выражения, которые могут быть запущены триггером. Таблицы inserted и deleted в триггерах всегда возвращают только те строки, которые реально затронуты инструкциями INSERT, UPDATE, MERGE или DELETE. Например, если INSERT TRIGGER срабатывает в результате выполнения инструкции INSERT с предложением TOP,
то SQL Server позволяет обновлять строки через представления. Так как предложение TOP можно включать в определение представления, некоторые строки могут исчезнуть из представления после обновления, если они больше не соответствуют требованиям выражения TOP.
При использовании в инструкции MERGE предложение TOP применяется после соединения всей исходной таблицы и всей целевой таблицы, а также удаления соединенных строк, которые не соответствуют требованиям к вставке, обновлению или удалению. Предложение TOP дополнительно сокращает количество соединенных строк до указанного значения, а затем к оставшимся соединенным строкам применяются операции вставки, обновления или удаления без определенного порядка. Это означает, что порядок распределения строк по действиям, определенным в предложениях WHEN отсутствует. Например если TOP (10) в запросе затрагивает 10 строк, в их число могут попасть 7 обновляемых и 3 добавляемые строки. Или же 1 строка будет удаляемая, 5 обновляемых и 4 добавляемые строки либо любой другой вариант. Инструкция MERGE выполняет полное сканирование исходной и целевой таблиц, поэтому предложение TOP для изменения большой таблицы путем создания нескольких пакетов может снизить производительность ввода-вывода. В этом случае необходимо сделать так, чтобы все последующие пакеты содержали только новые строки.
Соблюдайте осторожность при использовании предложения TOP в запросе, содержащем операторы UNION, UNION ALL, EXCEPT и INTERSECT. Есть вероятность того, что результаты запроса будут непредсказуемыми из-за неожиданной логики обработки предложений TOP и ORDER BY в операции выбора. Например, исходя из следующих таблиц и данных, предположим, что необходимо вернуть самый недорогой красный и синий автомобили. Иными словами, красный седан и синий фургон.
CREATE TABLE dbo.Cars(Model VARCHAR(15), Price MONEY, Color VARCHAR(10));
INSERT dbo.Cars VALUES
('sedan', 10000, 'red'), ('convertible', 15000, 'blue'),
('coupe', 20000, 'red'), ('van', 8000, 'blue');
Чтобы получить такие результаты, можно написать следующий запрос.
SELECT TOP(1) Model, Color, Price
FROM dbo.Cars
WHERE Color = 'red'
UNION ALL
SELECT TOP(1) Model, Color, Price
FROM dbo.Cars
WHERE Color = 'blue'
ORDER BY Price ASC;
GO
Ниже приведен результирующий набор.
Model Color Price
------------- ---------- -------
sedan red 10000.00
convertible blue 15000.00
Возвращаются непредвиденные результаты, так как инструкция TOP логически выполняется раньше, чем предложение ORDER BY, которое сортирует результаты оператора (в данном случае UNION ALL). Таким образом, предыдущие запросы вернут произвольный красный и произвольный синий автомобили, а затем отсортируют результат такого объединения по цене. Следующий пример показывает, как правильно написать запрос, который позволит получить нужный результат.
SELECT Model, Color, Price
FROM (SELECT TOP(1) Model, Color, Price
FROM dbo.Cars
WHERE Color = 'red'
ORDER BY Price ASC) AS a
UNION ALL
SELECT Model, Color, Price
FROM (SELECT TOP(1) Model, Color, Price
FROM dbo.Cars
WHERE Color = 'blue'
ORDER BY Price ASC) AS b;
GO
Использование TOP и ORDER BY во вложенной операции выбора гарантирует, что результаты предложения ORDER BY применяются к предложению TOP, а не к сортировке результата операции UNION.
Результирующий набор:
Model Color Price
------------- ---------- -------
sedan red 10000.00
van blue 8000.00
Ограничения
При использовании TOP в инструкции INSERT, UPDATE, MERGE или DELETE к строкам не применяется никакое упорядочение. Также вы не можете напрямую указать предложение ORDER BY в этих инструкциях. Если вы намерены использовать TOP, чтобы вставлять, удалять или изменять строки в определенном хронологическом порядке, добавьте TOP в предложение ORDER BY, включенное во вложенную инструкцию выборки. Подробнее см. раздел с примерами в этой статье.
Предложение TOP не может быть использовано в инструкциях UPDATE и DELETE для секционированных представлений.
TOP нельзя сочетать с OFFSET и FETCH в одном выражении запроса (в той же области запроса). Дополнительные сведения см. в разделе Предложение ORDER BY (Transact-SQL).
Примеры
Категория | Используемые элементы синтаксиса |
---|---|
Основной синтаксис | TOP * PERCENT |
Включить равные значения | WITH TIES |
Ограничение числа строк, обрабатываемых инструкциями DELETE, INSERT и UPDATE | DELETE * INSERT * UPDATE |
Основной синтаксис
В примерах этого раздела показана базовая функциональность предложения ORDER BY с использованием минимально необходимого синтаксиса.
А. Использование ключевого слова TOP со значением константы
В следующих примерах константа указывает число сотрудников, возвращаемых в результирующем наборе запроса. В первом примере возвращаются 10 произвольных строк, так как отсутствует предложение ORDER BY. Во втором примере предложение ORDER BY возвращает 10 сотрудников, последними принятых на работу.
USE AdventureWorks2022;
GO
-- Select the first 10 random employees.
SELECT TOP(10)JobTitle, HireDate
FROM HumanResources.Employee;
GO
-- Select the first 10 employees hired most recently.
SELECT TOP(10)JobTitle, HireDate
FROM HumanResources.Employee
ORDER BY HireDate DESC;
GO
B. Использование ключевого слова TOP с переменной
В следующем примере переменная указывает число сотрудников, возвращаемых в результирующем наборе запроса.
USE AdventureWorks2022;
GO
DECLARE @p AS INT = 10;
SELECT TOP(@p)JobTitle, HireDate, VacationHours
FROM HumanResources.Employee
ORDER BY VacationHours DESC;
GO
C. Указание процентов
В следующем примере PERCENT указывает число сотрудников, возвращаемых в результирующем наборе запроса. В таблице HumanResources.Employee
содержится 290 сотрудников. 5 % от 290 является дробным числом, поэтому значение округляется вверх до целого числа.
USE AdventureWorks2022;
GO
SELECT TOP(5)PERCENT JobTitle, HireDate
FROM HumanResources.Employee
ORDER BY HireDate DESC;
GO
Включить равные значения
А. Использование ключевого слова WITH TIES для включения строк, соответствующих значениям в последней строке
Следующий пример извлекает первые 10
процентов работников с наибольшей зарплатой и возвращает их в порядке убывания зарплаты. Выбор WITH TIES
гарантирует, что в результирующий набор будут включены все работники с минимальной в этом списке зарплатой, даже если это приведет к превышению ограничения в 10
процентов работников.
USE AdventureWorks2022;
GO
SELECT TOP(10) PERCENT WITH TIES
pp.FirstName, pp.LastName, e.JobTitle, e.Gender, r.Rate
FROM Person.Person AS pp
INNER JOIN HumanResources.Employee AS e
ON pp.BusinessEntityID = e.BusinessEntityID
INNER JOIN HumanResources.EmployeePayHistory AS r
ON r.BusinessEntityID = e.BusinessEntityID
ORDER BY Rate DESC;
GO
Ограничение числа строк, обрабатываемых инструкциями DELETE, INSERT и UPDATE
А. Ограничение числа удаляемых строк с помощью ключевого слова TOP
Если с инструкцией DELETE применяется предложение TOP (n), операция удаления производится с произвольной выборкой из n строк. Таким образом, инструкция DELETE выбирает любое число (n) строк, которые удовлетворяют условию, указанному в предложении WHERE. Следующий пример удаляет из таблицы PurchaseOrderDetail
20
строк с датой завершения до 1 июля 2002 г.
USE AdventureWorks2022;
GO
DELETE TOP (20)
FROM Purchasing.PurchaseOrderDetail
WHERE DueDate < '20020701';
GO
Если вы намерены с помощью предложения TOP удалять строки в требуемом хронологическом порядке, включите TOP во вложенный запрос с предложением ORDER BY. Следующий запрос удаляет из таблицы PurchaseOrderDetail
10 строк, имеющих самую раннюю дату. Чтобы гарантировать удаление только 10 строк, столбец, указанный в инструкции подзапроса выборки (PurchaseOrderID
) должен являться первичным ключом таблицы. Использование неключевого столбца в инструкции подзапроса выборки может привести к удалению более чем 10 строк, если указанный столбец содержит повторяющиеся значения.
USE AdventureWorks2022;
GO
DELETE FROM Purchasing.PurchaseOrderDetail
WHERE PurchaseOrderDetailID IN
(SELECT TOP 10 PurchaseOrderDetailID
FROM Purchasing.PurchaseOrderDetail
ORDER BY DueDate ASC);
GO
B. Ограничение числа вставляемых строк с помощью ключевого слова TOP
В следующем примере создается таблица EmployeeSales
и вставляется имя и данные о продажах за текущий год для 5 первых сотрудников из таблицы HumanResources.Employee
. Инструкция INSERT выбирает из результата инструкции SELECT
произвольные 5 строк, которые удовлетворяют условию, определенному в предложении WHERE. Предложение OUTPUT отображает строки, вставляемые в таблицу EmployeeSales
. Обратите внимание, что в инструкции SELECT не используется предложение ORDER BY для определения 5 первых сотрудников.
USE AdventureWorks2022;
GO
IF OBJECT_ID ('dbo.EmployeeSales', 'U') IS NOT NULL
DROP TABLE dbo.EmployeeSales;
GO
CREATE TABLE dbo.EmployeeSales
( EmployeeID NVARCHAR(11) NOT NULL,
LastName NVARCHAR(20) NOT NULL,
FirstName NVARCHAR(20) NOT NULL,
YearlySales MONEY NOT NULL
);
GO
INSERT TOP(5)INTO dbo.EmployeeSales
OUTPUT inserted.EmployeeID, inserted.FirstName, inserted.LastName, inserted.YearlySales
SELECT sp.BusinessEntityID, c.LastName, c.FirstName, sp.SalesYTD
FROM Sales.SalesPerson AS sp
INNER JOIN Person.Person AS c
ON sp.BusinessEntityID = c.BusinessEntityID
WHERE sp.SalesYTD > 250000.00
ORDER BY sp.SalesYTD DESC;
GO
Если вы намерены с помощью предложения TOP добавлять строки в требуемом хронологическом порядке, включите TOP во вложенный запрос с предложением ORDER BY. Следующий пример показывает, как это сделать. Предложение OUTPUT отображает строки, вставляемые в таблицу EmployeeSales
. Обратите внимание, что вставка данных о 5 первых сотрудниках выполняется теперь по результатам предложения ORDER BY, а не для произвольных строк.
INSERT INTO dbo.EmployeeSales
OUTPUT inserted.EmployeeID, inserted.FirstName, inserted.LastName, inserted.YearlySales
SELECT TOP (5) sp.BusinessEntityID, c.LastName, c.FirstName, sp.SalesYTD
FROM Sales.SalesPerson AS sp
INNER JOIN Person.Person AS c
ON sp.BusinessEntityID = c.BusinessEntityID
WHERE sp.SalesYTD > 250000.00
ORDER BY sp.SalesYTD DESC;
GO
C. Ограничение числа обновляемых строк с помощью ключевого слова TOP
В следующем примере предложение TOP используется для обновления строк в таблице. Если предложение TOP (n) используется с инструкцией UPDATE, операция обновления выполняется для неопределенного количества строк. Таким образом, инструкция UPDATE выбирает любое число (n) строк, которые удовлетворяют условию, указанному в предложении WHERE. В следующем примере 10 случайно выбранных заказчиков переназначаются от одного менеджера по продажам к другому.
USE AdventureWorks2022;
UPDATE TOP (10) Sales.Store
SET SalesPersonID = 276
WHERE SalesPersonID = 275;
GO
Если нужно применить изменения с предложением TOP в определенной последовательности, укажите во вложенном запросе SELECT предложение ORDER BY. В следующем примере изменяется длительность отпуска для 10 сотрудников, имеющих наибольший стаж работы.
UPDATE HumanResources.Employee
SET VacationHours = VacationHours + 8
FROM (SELECT TOP 10 BusinessEntityID FROM HumanResources.Employee
ORDER BY HireDate ASC) AS th
WHERE HumanResources.Employee.BusinessEntityID = th.BusinessEntityID;
GO
Примеры: Azure Synapse Analytics и система платформы аналитики (PDW)
В приведенном ниже примере возвращаются первые строки (31), соответствующие условию запроса. Предложение ORDER BY гарантирует, что возвращается 31 строка в порядке сортировки столбца LastName
по алфавиту.
Использование TOP без указания равных элементов.
SELECT TOP (31) FirstName, LastName
FROM DimEmployee ORDER BY LastName;
Результат: возвращается 31 строка.
Использование TOP с указанием WITH TIES.
SELECT TOP (31) WITH TIES FirstName, LastName
FROM DimEmployee ORDER BY LastName;
Результат: возвращаются 33 строки, так как три сотрудника с именем Браун галстук для 31-й строки.
См. также
SELECT (Transact-SQL)
INSERT (Transact-SQL)
UPDATE (Transact-SQL)
DELETE (Transact-SQL)
Предложение ORDER BY (Transact-SQL)
SET ROWCOUNT (Transact-SQL)
MERGE (Transact-SQL)