Ms sql select top: TOP (Transact-SQL) — SQL Server

Содержание

Оператор SQL TOP: примеры, синтаксис

Оператор SQL TOP позволяет ограничить выборку числа записей до заданного числа. При использовании оператора SQL TOP совместно с оператором SQL ORDER BY, на вывод пойдет первые N записей отсортированного списка, в противном случае, выведутся первые N строк таблицы.

Используется только в СУБД MS SQL Server! Аналогом в MySQL является оператор SQL LIMIT.

Оператор SQL TOP имеет следующий синтаксис:

TOP ( N [PERCENT] )

Параметр PERCENT позволяет задать количество строк в процентах. Эта возможность появилась в MS SQL Server 2012.


Примеры оператора SQL TOP. Имеется следующая таблица Artists:

Singer Album Year Sale
The Prodigy Invaders Must Die 2008 1200000
Drowning Pool Sinner 2001 400000
Massive Attack Mezzanine 1998 2300000
The Prodigy Fat of the Land 1997 600000
The Prodigy Music For The Jilted Generation 1994 1500000
Massive Attack 100th Window 2003 1200000
Drowning Pool Full Circle 2007 800000
Massive Attack Danny The Dog 2004 1900000
Drowning Pool Resilience 2013 500000

Пример 1. Используя оператор SQL TOP вывести первые 4 строки таблицы:

SELECT TOP(4) * FROM Artists

Результат:

Singer Album Year Sale
The Prodigy Invaders Must Die 2008 1200000
Drowning Pool Sinner 2001 400000
Massive Attack Mezzanine 1998 2300000
The Prodigy Fat of the Land 1997 600000

Пример 2. Используя оператор SQL TOP вывести 3 самых свежих альбома (название и год выпуска):

SELECT TOP(3) Album, Year FROM Artists ORDER BY Year

Результат:

Album Year
Resilience 2013
Invaders Must Die 2008
Full Circle 2007

SQL Инструкция SELECT TOP, LIMIT, ROWNUM

На сайте нет рекламы



SQL SELECT TOP

Инструкция SELECT TOP используется для указания количества возвращаемых записей.

Инструкция SELECT TOP полезно для больших таблиц с тысячами записей. Возврат большого количества записей может повлиять на производительность.

Примечание: Не все базы данных поддерживают SELECT TOP. MySQL поддерживает предложение LIMIT для выбора ограниченного числа записей, в то время как Oracle использует ROWNUM.

Синтаксис SQL Server / MS Access:

SELECT TOP number|percent column_name(s)
FROM table_name
WHERE condition;

Синтаксис MySQL:

SELECT column_name(s)
FROM table_name
WHERE condition
LIMIT number;

Синтаксис Oracle:

SELECT column_name(s)
FROM table_name
WHERE ROWNUM <= number;


Демо база данных

Ниже приведен выбор из таблицы "Customers" в образце базы данных Northwind:

CustomerID CustomerName ContactName Address City PostalCode Country
1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany
2 Ana Trujillo Emparedados y helados Ana Trujillo Avda. de la Constitución 2222 México D.F. 05021 Mexico
3 Antonio Moreno Taquería Antonio Moreno Mataderos 2312 México D.F. 05023 Mexico
4 Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK
5 Berglunds snabbköp Christina Berglund Berguvsvägen 8 Luleå S-958 22 Sweden


Примеры SQL TOP, LIMIT и ROWNUM

Следующая инструкция SQL выбирает первые три записи из таблицы "Customers":

Следующий оператор SQL показывает эквивалентный пример использования предложения LIMIT:

Следующая инструкция SQL показывает соответствующий пример использования параметра ROWNUM:

Пример

SELECT * FROM Customers
WHERE ROWNUM <= 3;


Пример SQL TOP PERCENT

Следующая инструкция SQL выбирает первые 50% записей из таблицы "Customers":


Добавить WHERE

Следующая инструкция SQL выбирает первые три записи из таблицы "Customers", где страна - "Germany":

Следующий оператор SQL показывает эквивалентный пример использования LIMIT:

Следующая инструкция SQL показывает соответствующий пример использования параметра ROWNUM:

Пример

SELECT * FROM Customers
WHERE Country='Germany' AND ROWNUM <= 3;


База Данных MySQL Top ограничение ROWNUM



Предложение SQL SELECT Top

Предложение SELECT Top используется для указания количества возвращаемых записей.

Предложение SELECT Top полезно для больших таблиц с тысячами записей. Возврат большого количества записей может повлиять на производительность.

Примечание: Не все системы баз данных поддерживают предложение SELECT Top. MySQL поддерживает предложение Limit, чтобы выбрать ограниченное количество записей, в то время как Oracle использует ROWNUM.

Синтаксис доступа к SQL Server/MS:

SELECT TOP number|percent column_name(s)
FROM table_name
WHERE condition;

MySQL Синтаксис:

SELECT column_name(s)
FROM table_name
WHERE condition
LIMIT number;

Oracle Синтаксис:

SELECT column_name(s)
FROM table_name
WHERE ROWNUM <= number;


Демонстрационная база данных

Ниже приведен выбор из таблицы "Customers" в образце базы данных Northwind:

CustomerID CustomerName ContactName Address City PostalCode Country
1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany
2 Ana Trujillo Emparedados y helados Ana Trujillo Avda. de la Constitución 2222 México D.F. 05021 Mexico
3 Antonio Moreno Taquería Antonio Moreno Mataderos 2312 México D.F. 05023 Mexico
4 Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK
5 Berglunds snabbköp Christina Berglund Berguvsvägen 8 Luleå S-958 22 Sweden


Примеры верхнего, предельного и ROWNUM SQL

Следующая инструкция SQL выбирает первые три записи из таблицы «Customers»:

Пример

SELECT TOP 3 * FROM Customers;

Следующая инструкция SQL показывает эквивалентный пример с помощью предложения LIMIT:

Пример

SELECT * FROM Customers
LIMIT 3;

Следующая инструкция SQL показывает эквивалентный пример с помощью ROWNUM:

Пример

SELECT * FROM Customers
WHERE ROWNUM <= 3;


Пример верхнего процента SQL

Следующая инструкция SQL выбирает первые 50% записей из таблицы "Customers":

Пример

SELECT TOP 50 PERCENT * FROM Customers;


Добавление предложения WHERE

Следующая инструкция SQL выбирает первые три записи из таблицы «Customers», где страна «Германия»:

Пример

SELECT TOP 3 * FROM Customers
WHERE Country='Germany';

Следующая инструкция SQL показывает эквивалентный пример с помощью предложения LIMIT:

Пример

SELECT * FROM Customers
WHERE Country='Germany'
LIMIT 3;

Следующая инструкция SQL показывает эквивалентный пример с помощью ROWNUM:

Пример

SELECT * FROM Customers
WHERE Country='Germany' AND ROWNUM <= 3;


SELECT TOP SQL Server — Oracle PL/SQL •MySQL •MariaDB •SQL Server •SQLite

В этом учебном пособии вы узнаете, как использовать оператор SELECT TOP в SQL Server (Transact-SQL) с синтаксисом и примерами.

Описание

Оператор SELECT TOP SQL Server (Transact-SQL) используется для извлечения записей из одной или нескольких таблиц в SQL Server и ограничения количества возвращаемых записей на основе фиксированного значения или процента.

Синтаксис

Синтаксис оператора SELECT TOP в SQL Server (Transact-SQL):

SELECT TOP (top_value) [ PERCENT ] [ WITH TIES ]
expressions
FROM tables
[WHERE conditions]
[ORDER BY expression [ ASC | DESC ]];

Параметры или аргументы

TOP (top_value) - возвращает верхнее число строк в результирующем наборе на основе top_value. Например, TOP (10) вернет первые 10 строк из полного набора результатов.
PERCENT - необязательный. Если задан PERCENT, то верхние строки основаны на проценте от общего набора результатов (как указано в верхнем значении). Например, TOP (10) PERCENT вернет верхние 10% полного набора результатов.
WITH TIES - необязательный. Если указано условие WITH TIES, то возвращаются строки, связанные со строкой на последнем месте в ограниченном результирующем наборе. Это может привести к возврату большего количества строк, чем позволяет параметр TOP.

expressions - столбцы или вычисления, которые вы хотите получить.
tables - таблицы, из которых вы хотите получить записи. Должна быть хотя бы одна таблица, перечисленная в предложении FROM.
WHERE conditions - необязательный. Условия, которые должны быть выполнены для выбранных записей.
ORDER BY expression - необязательный. Он используется в операторе SELECT TOP для сортировки записей, которые вы хотите вернуть. ASC в порядке возрастания, а DESC - в порядке убывания.

Пример использования ключевого слова TOP

Давайте посмотрим на пример SQL Server, где мы используем ключевое слово TOP в операторе SELECT.
Например:

SELECT TOP(5) employee_id, last_name, first_name FROM employees WHERE last_name = 'Samson' ORDER BY employee_id;

SELECT TOP(5)

employee_id, last_name, first_name

FROM employees

WHERE last_name = 'Samson'

ORDER BY employee_id;

В этом примере SQL Server SELECT TOP были выбраны первые 5 записей из таблицы employees, где last_name – ‘Samson’. Если в таблице employee есть другие записи с last_name ‘Samson’, то они не будут возвращены оператором SELECT.

Вы можете изменить этот пример, включив предложение WITH TIES следующим образом:

SELECT TOP(5) WITH TIES employee_id, last_name, first_name FROM employees WHERE last_name = 'Samson' ORDER BY employee_id;

SELECT TOP(5) WITH TIES

employee_id, last_name, first_name

FROM employees

WHERE last_name = 'Samson'

ORDER BY employee_id;

Предложение WITH TIES будет включать строки, которые могут быть связаны со строкой на последнем месте в ограниченном наборе результатов. Поэтому, если с 5-й строкой верхнего набора связаны еще две строки, то все эти связанные строки будут возвращены оператором SELECT TOP. Это приведет к возврату более 7 записей.

Пример использования ключевого слова TOP PERCENT

Рассмотрим пример SQL Server, в котором мы используем ключевое слово TOP PERCENT в операторе SELECT.

Например:

SELECT TOP(10) PERCENT employee_id, last_name, first_name FROM employees WHERE last_name = 'Samson' ORDER BY employee_id;

SELECT TOP(10) PERCENT

employee_id, last_name, first_name

FROM employees

WHERE last_name = 'Samson'

ORDER BY employee_id;

Этот пример SQL Server SELECT TOP выберет первые 10% записей из полного набора результатов. Поэтому в этом примере оператора SELECT вернет 10% записей из таблицы employees, где last_name - 'Samson'. Остальные 90% набора результатов не будут возвращены оператором SELECT.
Вы можете изменить этот пример, включив предложение WITH TIES следующим образом:

SELECT TOP(10) PERCENT WITH TIES employee_id, last_name, first_name FROM employees WHERE last_name = 'Samson' ORDER BY employee_id;

SELECT TOP(10) PERCENT WITH TIES

employee_id, last_name, first_name

FROM employees

WHERE last_name = 'Samson'

ORDER BY employee_id;

Предложение WITH TIES будет включать строки, которые могут быть связаны со строкой которая на последнем месте в ограниченном наборе результатов. Поэтому, если есть такие строки в наборе записей SELECT TOP (10) PERCENT, то эти связанные записи будут возвращены оператором SELECT TOP. Это приведет к возврату более 10% от полного набора записей.

Почему нет `select last` или `select bottom` в SQL Server, как есть `select top`?

Вы можете думать об этом так.

SELECT TOP N без ORDER BY возвращает некоторые строки N , ни первые, ни последние, только некоторые . Какие строки он возвращает, не определено. Вы можете запустить один и тот же оператор 10 раз и каждый раз получать 10 разных наборов строк.

Таким образом, если бы у сервера был синтаксис SELECT LAST N , то результат этого оператора без ORDER BY снова был бы неопределенным, что именно то, что вы получаете с существующим SELECT TOP N без ORDER BY .


Вы подчеркнули в своем вопросе, что знаете и понимаете то, что я написал ниже, но я все равно сохраню это, чтобы все поняли это позже.

Ваша первая фраза в вопросе

В SQL-сервере у нас теперь есть SELECT TOP N ... , в котором мы можем получить первые n строк в порядке возрастания (по умолчанию), круто.

это неверно. С SELECT TOP N без ORDER BY вы получаете N "random" строк. Ну, не совсем случайно, сервер не скачет случайным образом из строки в строку специально. Он выбирает какой-то детерминированный способ сканирования таблицы, но может быть много различных способов сканирования таблицы, и сервер может свободно изменять выбранный путь, когда захочет. Это то, что подразумевается под "undefined".

Сервер не отслеживает порядок, в котором строки были вставлены в таблицу, поэтому опять же ваше предположение о том, что результаты SELECT TOP N без ORDER BY определяются порядком, в котором строки были вставлены в таблицу, неверно.


Итак, ответ на ваш последний вопрос

почему нет select last/bottom , как будто это аналог.

является:

  • без ORDER BY результаты SELECT LAST N были бы точно такими же, как результаты SELECT TOP N - неопределенными.
  • с ORDER BY результат SELECT LAST N ... ORDER BY X ASC точно такой же, как и результат SELECT TOP N ... ORDER BY X DESC .

Таким образом, нет смысла иметь два ключевых слова, которые делают одно и то же.


В ответе Питера есть хороший момент: слово TOP несколько вводит в заблуждение. Это действительно означает LIMIT результирующий набор для некоторого количества строк.

Кстати, начиная с SQL Server 2012 они добавили поддержку ANSI стандарта OFFSET :

OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS }
[
  FETCH { FIRST | NEXT } {integer_constant | fetch_row_count_expression } { ROW | ROWS } ONLY
]

Здесь добавление еще одного ключевого слова было оправдано тем, что это стандарт ANSI AND , он добавляет важную функциональность - разбиение на страницы, которой раньше не существовало.


Я хотел бы поблагодарить @Razort4x здесь за предоставление очень хорошей ссылки на MSDN в его вопросе. В разделе "Advanced Scanning" есть отличный пример механизма под названием "сканирование merry-go-round", который демонстрирует, почему порядок результатов, возвращаемых из оператора SELECT , не может быть гарантирован без предложения ORDER BY .

Эта концепция часто неправильно понимается, и я видел много вопросов здесь, на SO, которые были бы очень полезны, если бы у них была цитата из этой ссылки.


Ответ на ваш вопрос

Почему не SQL Server у SELECT LAST или сказать SELECT BOTTOM или что-то вроде того, где мы не должны указать ORDER BY и тогда это дало бы последней записи , вставленной в таблицу по времени выполнения запроса (я опять не вдаваясь в подробности о том, как будет этот результат в случае незавершенных чтения и фантомные чтения).

является:

Дьявол кроется в деталях, которые вы хотите опустить. Чтобы узнать, какая запись была "последней вставленной в таблицу во время выполнения запроса" (и знать это несколько consistent/non-random образом), серверу нужно было бы каким-то образом отслеживать эту информацию. Даже если это возможно во всех сценариях нескольких одновременно выполняемых транзакций, это, скорее всего, дорого с точки зрения производительности. Не каждый SELECT запросит эту информацию (на самом деле очень мало или вообще нет), но накладные расходы на отслеживание этой информации всегда будут.

Итак, вы можете думать об этом так: по умолчанию сервер не делает ничего конкретного, чтобы знать/отслеживать порядок, в котором были вставлены строки, потому что это влияет на производительность, но если вам нужно знать, что вы можете использовать, например, столбец IDENTITY . Microsoft могла бы разработать серверный движок таким образом, чтобы он требовал столбца IDENTITY в каждой таблице, но они сделали его необязательным, что, на мой взгляд, хорошо. Я знаю лучше, чем сервер, какие из моих таблиц нуждаются в столбце IDENTITY , а какие нет.

Резюме

Я хотел бы резюмировать, что вы можете смотреть на SELECT LAST без ORDER BY двумя разными способами.

1) Когда вы ожидаете, что SELECT LAST будет вести себя в соответствии с существующим SELECT TOP . В этом случае результат не определен как для LAST , так и для TOP, т. е. Результат фактически один и тот же. В этом случае все сводится к (отсутствию) другого ключевого слова. Разработчики языков (в данном случае язык T-SQL) всегда неохотно добавляют ключевые слова, если для этого нет веских причин. В этом случае этого явно можно избежать.

2) Когда вы ожидаете, что SELECT LAST будет вести себя как SELECT LAST INSERTED ROW . Что, кстати, должно расширить те же ожидания до SELECT TOP , чтобы вести себя как SELECT FIRST INSERTED ROW , или добавить новые ключевые слова LAST_INSERTED, FIRST_INSERTED , чтобы сохранить существующее ключевое слово TOP нетронутым. В этом случае все сводится к производительности и дополнительным накладным расходам такого поведения. На данный момент сервер позволяет вам избежать этого снижения производительности, если вам не нужна эта информация. Если вам это действительно нужно, IDENTITY -довольно хорошее решение, если вы используете его осторожно.

sql-server — «SELECT TOP» вопрос производительности

У меня есть запрос, который работает намного быстрее с помощью выбора top 100 и ​​намного медленнее без top 100. Количество возвращаемых записей равно 0. Не могли бы вы объяснить разницу в планах запросов или поделиться ссылками, где объясняется такая разница?

Запрос без

top текста:
SELECT --TOP 100
*
FROM InventTrans
     JOIN
     InventDim
     ON InventDim.DATAAREAID = 'dat' AND 
        InventDim.INVENTDIMID = InventTrans.INVENTDIMID
WHERE InventTrans.DATAAREAID = 'dat' AND 
      InventTrans.ITEMID = '027743' AND 
      InventDim.INVENTLOCATIONID = 'КзРЦ Алмат' AND 
      InventDim.ECC_BUSINESSUNITID = 'Казахстан';

План запроса для вышеупомянутого (без

top):

https://Pastebin.com/cbtJpxFf

Статистика IO и TIME (без

top):
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

(0 row(s) affected)
Table 'INVENTDIM'. Scan count 0, logical reads 988297, physical reads 0, read-ahead reads 1, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'INVENTTRANS'. Scan count 1, logical reads 1234560, physical reads 0, read-ahead reads 14299, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 6256 ms,  elapsed time = 13348 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

Используемые индексы (без

top):
1. INVENTTRANS.I_177TRANSIDIDX
   4 KEYS:
 - DATAAREAID
 - INVENTTRANSID
 - INVENTDIMID
 - RECID
2. INVENTTRANS.I_177ITEMIDX
   3 KEYS:
   - DATAAREAID
   - ITEMID
   - DATEPHYSICAL 
3. INVENTDIM.I_698DIMIDIDX
   2 KEYS:
   - DATAAREAID
   - INVENTDIMID

Запрос с

top:
SELECT TOP 100
*
FROM InventTrans
     JOIN
     InventDim
     ON InventDim.DATAAREAID = 'dat' AND 
        InventDim.INVENTDIMID = InventTrans.INVENTDIMID
WHERE InventTrans.DATAAREAID = 'dat' AND 
      InventTrans.ITEMID = '027743' AND 
      InventDim.INVENTLOCATIONID = 'КзРЦ Алмат' AND 
      InventDim.ECC_BUSINESSUNITID = 'Казахстан';

План запроса (с TOP):

https://Pastebin.com/0dyu6QZd


Запрос IO и статистика ВРЕМЕНИ (с ТОП):

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

(0 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'INVENTTRANS'. Scan count 15385, logical reads 82542, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'INVENTDIM'. Scan count 1, logical reads 62704, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 265 ms,  elapsed time = 257 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

Используемые индексы (с ТОПом):

 1. INVENTTRANS.I_177TRANSIDIDX
     4 KEYS:
     - DATAAREAID
     - INVENTTRANSID
     - INVENTDIMID
     - RECID
 2. INVENTTRANS.I_177DIMIDIDX
    3 KEYS:
    - DATAAREAID
    - INVENTDIMID
    - ITEMID
 3. INVENTDIM.I_698DIMIDIDX
    2 KEYS:
    - DATAAREAID
    - INVENTDIMID
 4. INVENTDIM.I_698ECC_BUSUNITLOCIDX
    3 KEYS
    - DATAAREAID
    - ECC_BUSINESSUNITID
    - INVENTLOCATIONID

Буду глубоко признателен за любую помощь по теме!

Постраничная (пакетная, paging) выборка в MS SQL Server

От редактора. Данная версия статьи частично устарела, смотрите новый вариант с учетом изменений в SQL Server 2012

Материал этой статьи послужил основой для одной из глав книги "СУБД для программиста. Базы данных изнутри".

* * *

На дворе 2008 год, а разработчики MS SQL Server до сих пор не реализовали встроенную возможность ограничивать в запросах результирующую выборку номерами строк. Например, "выбрать заказы данного клиента, начиная с 10000-й строки и по 12000-ю". Нечто вроде простого и понятного:

SELECT O.*
  FROM orders O INNER JOIN customers C
       ON O.customer_code = C.customer_code
  ORDER BY O.qty_date ASC
  LIMIT 10000, 12000

Введенные в 2005-й версии функции ранжирования и в частности row_number() несколько скрасили серые будни рядовых разработчиков, но по сути проблему так и не решили. Дело в том, что конструкция LIMIT работает на уровне ядра СУБД, а функция row_number() - на пользовательском. Соответственно, скорость выполнения отличается принципиально, что особенно заметно на больших таблицах.

В данном обзоре я опишу различные методы решения задачи постраничной выборки (paging, пакетная выборка) на примере таблиц заказов и клиентов. Для тестов использовался MS SQL Server 2005 Service Pack 2 (9.00.3054.00) на рабочей станции с 2 Гб оперативной памяти (512 доступно под MS SQL) с двуядерным процессором Intel 1,8 ГГц.

Необходимо выбрать заказы всех итальянских клиентов (код страны "IT") пачками по 100 тысяч записей в каждом. Например, пакет с 400001-й строки и по 500000-ю - это четвертый пакет в серии. Заказов, соответствующим заданному критерию, в таблице порядка 800 тысяч. Всего же в таблице содержится примерно 4 млн 300 тыс. записей. Большим такое число не назовешь, но оно уже способно неплохо загрузить наш сервер для выявления оптимальных способов решения задачи.

Задача теста - выявить временные показатели выполения различных способов решения нашей задачи. Каждый способ выполняется в 4-х сериях тестов:

  • Серия 1: с составным ключом из символьных (nvarchar) полей, SQL Server перезапускаем только в начале серии
  • Серия 2: с составным ключом из символьных (nvarchar) полей, SQL Server перезапускаем перед каждым тестом серии
  • Серия 3: с простым целочисленным ключом, SQL Server перезапускаем только в начале серии
  • Серия 4: с простым целочисленным ключом, SQL Server перезапускаем перед каждым тестом серии

Перезапуск сервера производи для исключения влияния кэширования результатов предыдущих серий или отдельных тестов на последующие. После перезапуска всякий раз выполняем для "разогрева" - частичной загрузки данных в кэш и приближения к реальным условиям, следующий запрос:

SELECT count(*)
  FROM orders O INNER JOIN customers C
       ON O.customer_code = C.customer_code
  WHERE C.country_code = 'IT'

Структура таблиц

Таблица заказов имеет простой целочисленный ключ, добавленный специально для тестов, и составной ключ из символьных полей. С небольшими упрощениями структура выглядит следующим образом.

CREATE TABLE dbo.customers (
  customer_code  nvarchar(15)  NOT NULL,
  country_code   nchar(2)      NOT NULL,
  name           nvarchar(255) NOT NULL,
  street_address nvarchar(100) NULL,
  city           nvarchar(40)  NULL,
  postal_code    nvarchar(15)  NULL,
  CONSTRAINT PK_CUSTOMERS
    PRIMARY KEY NONCLUSTERED (customer_code ASC)
)
GO
CREATE INDEX IX1_COUNTRY_CODE ON dbo.customers (country_code ASC)
GO
CREATE TABLE dbo.orders (
  product_code  nvarchar(18) NOT NULL,
  customer_code nvarchar(15) NOT NULL,
  order_type    nvarchar(4)  NOT NULL,
  qty_date      datetime     NOT NULL,
  qty           int          NOT NULL,
  order_id      int          NOT NULL,
  CONSTRAINT PK_ORDERS PRIMARY KEY NONCLUSTERED(order_id ASC),
  CONSTRAINT FK1_ORDERS_CUSTOMERS FOREIGN KEY(customer_code)
    REFERENCES dbo.customers (customer_code)
)
GO
CREATE UNIQUE INDEX AK1_ORDERS ON orders(
  product_code ASC,
  customer_code ASC,
  order_type ASC,
  qty_date ASC)
GO 

Для каждого метода в качестве входных параметров мы определим два входых параметра: начальное смещение (@offset - заданный номер начальной строки выборки) и размер пакета (@batch_size - требуемое количество строк в выборке, начиная с заданной). Пример объявления и инициализации параметров перед выборкой:

DECLARE @offset int, @batch_size int;
SELECT @offset = 1, @batch_size = 100;

"Классический" способ с использованием стандартного SQL

У данного способа, видимо, есть только одно достоинство: запрос выполняется практически на любой СУБД. Принцип основан на соединении таблицы на саму себя (self join), что с миллионами записей более чем накладно. На таблицах же с несколькими тысячами/десятками тысяч записей способ вполне работоспособен. Так как окончания выполнения запроса на тестовом массиве данных я не дождался, то привожу только текст запроса без внесения результатов в сводную таблицу.

SELECT O.*
  FROM orders O
       INNER JOIN customers C ON O.customer_code = C.customer_code
  WHERE C.country_code = 'IT' AND
       (SELECT count(*)
          FROM orders O1
               INNER JOIN customers C1 ON O1.customer_code = C1.customer_code
          WHERE C1.country_code = 'IT' AND
                O1.product_code <= O.product_code AND
                O1.customer_code <= O.customer_code AND
                O1.order_type <= O.order_type AND
                O1.qty_date <= O.qty_date
       ) BETWEEN @offset AND @offset + @batch_size - 1
  ORDER BY O.product_code ASC, O.customer_code ASC, O.order_type ASC, O.qty_date ASC

В первом запросе номера строк не выводятся. Для случая с простым ключом "order_id" этот недостаток легко исправить:

SELECT num, O.*
  FROM orders O
       INNER JOIN
       (SELECT count(*) AS num, O2.order_id
          FROM orders O1
               INNER JOIN customers C1 ON O1.customer_code = C1.customer_code
               INNER JOIN orders O2 ON O1.order_id <= O2.order_id
               INNER JOIN customers C2
                 ON O2.customer_code = C2.customer_code AND
                    C1.country_code = C2.country_code AND
                    C1.country_code = 'IT'
          GROUP BY O2.order_id
          HAVING count(*) BETWEEN @offset AND @offset + @batch_size - 1
       ) AS OO ON O.order_id = OO.order_id
  ORDER BY OO.num ASC

Использование функции row_number()

Пример использования функции имеется в документации к MS SQL Server (Books online), наш запрос выглядит похоже.

WITH ordered_orders AS (
  SELECT O.*,
         row_number() OVER(
           ORDER BY O.product_code ASC,
                    O.customer_code ASC,
                    O.order_type ASC,
                    O.qty_date ASC
         ) AS row_num
    FROM orders O INNER JOIN customers C ON O.customer_code = C.customer_code
    WHERE C.country_code = 'IT'
)
SELECT *
  FROM ordered_orders
  WHERE row_num BETWEEN 400001 AND 500000

Использование временной таблицы

Заносим промежуточный результат (только ключевые поля) во временную таблицу с пронумерованными строками, отсекая по верхней границе, затем выбираем из нее нужный диапазон, соединяя с основной таблицей.

Не забудьте увеличить размер системной базы tempdb. Для данного примера она составила 1,5 Гбайта. В отсутствии верхнего предела для временных данных и заключается основной недостаток метода: чем больше исходная таблица и чем дальше от начального значения мы запрашиваем очередной пакет, тем больше потребуется заливать данных во временную таблицу. Конечно, дисковое пространство нынче большое и дешевое, но все таки винчестер не резиновый, да и скорость с ростом числа загружаемых во временную таблицу строк будет падать.

DECLARE @offset int, @batch_size int;
SELECT @offset = 400001, @batch_size = 100000;
 
CREATE TABLE #orders(
  row_num       int identity(1, 1) NOT NULL,
  product_code  nvarchar(18) NOT NULL,
  customer_code nvarchar(15) NOT NULL,
  order_type    nvarchar(4)  NOT NULL,
  qty_date      datetime     NOT NULL
);
 
INSERT INTO #orders (product_code, customer_code, order_type, qty_date)
SELECT TOP (@offset + @batch_size)
       O.product_code, O.customer_code, O.order_type, O.qty_date
  FROM orders O INNER JOIN customers C ON O.customer_code = C.customer_code
  WHERE C.country_code = 'IT'
  ORDER BY O.product_code ASC, O.customer_code ASC, O.order_type ASC, O.qty_date ASC;
 
SELECT O.*
  FROM #orders T INNER JOIN orders O
       ON T.product_code = O.product_code AND
          T.customer_code = O.customer_code AND
          T.order_type = O.order_type AND
          T.qty_date = O.qty_date
  WHERE T.row_num BETWEEN @offset and @offset + @batch_size - 1;
 
DROP TABLE #orders;

Использование инструкции TOP

Принцип основан на отсечении нужного числа записей в двух запросах с противоположным порядком следования записей. По сути здесь нет отличий от способа со временной таблицей, кроме того, что она используется неявно. Однако, сравнив результаты, мы видим, что на небольших пакетах (100 записей) SQL Server манипулирует примежуточными выборками менее эффективно, чем в способе с явным использованием временных таблиц.

DECLARE @offset int, @batch_size int;
SELECT @offset = 400001, @batch_size = 100000;
 
SELECT TOP (@batch_size) *
  FROM
    (SELECT TOP (@offset + @batch_size) O.*
       FROM orders O INNER JOIN customers C ON O.customer_code = C.customer_code
       WHERE C.country_code = 'IT'
       ORDER BY O.product_code DESC, O.customer_code DESC, O.order_type DESC, O.qty_date DESC
    ) AS T1
  ORDER BY product_code ASC, customer_code ASC, order_type ASC, qty_date ASC

Использование серверного курсора

Данный способ является не вполне документированным, так как функции работы с серверными курсорами не описаны в SQL Server Books Online, хотя они активно используются разработчиками Microsoft. Поэтому имеется весьма небольшой риск несовместимости с будущими версиями. Неофициальные описания функций можно найти, например, по ссылке.

DECLARE @handle int, @rows int;
 
EXEC sp_cursoropen
  @handle OUT,
  'SELECT O.* FROM orders O INNER JOIN customers C ON O.customer_code = C.customer_code
   WHERE C.country_code = ''IT''
   ORDER BY O.product_code ASC, O.customer_code ASC, O.order_type ASC, O.qty_date ASC',
  1, -- Keyset-driven cursor
  1, -- Read-only
  @rows OUT SELECT @rows; -- Contains total rows count
 
EXEC sp_cursorfetch
  @handle,
  16,     -- Absolute row index
  400001, -- Fetch from row
  100000  -- Rows count to fetch
 
EXEC sp_cursorclose @handle;

Использование SET ROWCOUNT

Хотя способ использует стандартную настройку SET ROWCOUNT, но инициализация переменной в запросе, возвращающем более дной строки, его последним значением недокументирована. Во-вторых, как подтвердил эксперимент, данный метод не работает на составных ключах. Тем не менее, в случае простого ключа способ показал неплохие результаты.

DECLARE @order_id int;
 
SET ROWCOUNT @offset;
SELECT @order_id = O.order_id
  FROM orders O INNER JOIN customers C ON O.customer_code = C.customer_code
  WHERE C.country_code = 'IT'
  ORDER BY O.order_id ASC;
 
SET ROWCOUNT @batch_size;
SELECT O.*
  FROM orders O INNER JOIN customers C ON O.customer_code = C.customer_code
  WHERE C.country_code = 'IT' AND
        O.order_id >= @order_id
  ORDER BY O.order_id ASC;
SET ROWCOUNT 0;

Сводная таблица результатов выглядит следующим образом.

Номер первой записи (смещение) Размер пакета Время выполнения, сек
Row_number Rowcount Server cursor Temp table TOP
1 2 3 4 3 4 1 2 3 4 1 2 3 4 1 2 3 4
 
1 100 5 5 5 5 7 6 94 88 86 87 2 2 6 5 5 6 5 5
1000 100 24 29 24 30 26 51 36 90 34 87 1 3 24 58 25 32 24 32
10000 100 79 108 78 107 80 81 36 88 33 87 2 3 78 78 79 81 78 80
100000 100 246 358 234 343 240 78 36 88 30 87 13 28 240 79 250 82 236 81
200000 100 48 394 30 368 31 80 36 88 25 86 17 29 34 82 46 83 35 82
300000 100 47 405 20 379 21 78 32 88 24 87 21 13 25 80 49 84 24 82
400000 100 59 426 24 386 25 80 31 88 21 86 27 30 29 81 68 84 29 83
700000 100 88 450 45 399 36 81 27 89 18 88 42 19 46 87 107 88 47 85
 
400001 100000 434 443 395 394 98 94 123 102 102 103 106 125 97 98 96 98 95 95
500001 100000 125 468 40 399 17 94 50 102 45 102 59 125 21 100 47 97 43 96
600001 100000 104 468 44 406 16 94 49 102 45 102 63 116 26 100 45 100 43 97
700001 100000 122 473 67 411 12 91 46 101 39 98 61 127 18 99 41 100 37 97

Номера столбцов означают:
(1) - использование составного ключа "product_code, customer_code, order_type, qty_date", перезапуск сервера перед каждой новой серией тестов
(2) - то же что и (1), но с перезапуском сервера перед каждым новым тестом
(3) - использование суррогатного ключа "order_id", перезапуск сервера перед каждой новой серией тестов
(4) - то же что и (3), но с перезапуском сервера перед каждым новым тестом

Результаты в графике:

К сожалению, появившаяся в SQL Server 2005 новая функция ранжирования row_number() показала в целом плохие результаты в тестах по сравнению с другими методами. Наиболее быстрым оказался метод с установкой ROWCOUNT, но неприменим на составных ключах. У остальных способов есть свои достоинства и недостатки, их и необходимо учесть при выборе. Наиболее универсальным методом, одновременно показывающим приемлемую скорость выборки, является использование серверного курсора: он использует ваш исходный запрос в чистом виде без необходимости добавлять к нему условий ограничения диапазона выборки, что является очень важным при работе с возвращающими результат хранимыми функциями и процедурами или проекциями (view). Например, использование функции в способе с TOP зачастую приводит к удвоению времени выборки. И, будем надеяться, что в новых версиях разработчики Microsoft все-таки реализуют на уровне ядра конструкцию LIMIT.

SQL SELECT TOP, LIMIT, FETCH FIRST ROWS ONLY, ROWNUM


Предложение SQL SELECT TOP

Предложение SELECT TOP используется для указания количества возвращаемых записей.

Предложение SELECT TOP полезно для больших таблиц с тысячами записи. Возврат большого количества записей может повлиять на производительность.

Примечание: Не все системы баз данных поддерживают Предложение SELECT TOP . MySQL поддерживает предложение LIMIT для выбора ограниченного числа записей, в то время как Oracle использует ВЫБРАТЬ ПЕРВЫЙ n РЯД ТОЛЬКО и ROWNUM .

Синтаксис доступа к SQL Server / MS:

ВЫБЕРИТЕ ТОП номер | процент имя_столбца
ИЗ имя_таблицы
ГДЕ условие ;

MySQL Синтаксис:

ВЫБЕРИТЕ имя_столбца
ИЗ имя_таблицы
ГДЕ условие
ПРЕДЕЛ номер ;

Oracle 12 Синтаксис:

ВЫБЕРИТЕ имя_столбца
ИЗ имя_таблицы
ЗАКАЗАТЬ ПО имя_столбца
FETCH FIRST number ТОЛЬКО СТРОКИ;

Старый синтаксис Oracle:

ВЫБЕРИТЕ имя_столбца
ИЗ имя_таблицы
ГДЕ СТРОКА <= номер ;

Старый синтаксис Oracle (с ORDER BY):

ВЫБРАТЬ *
ИЗ (ВЫБРАТЬ имя_столбца ИЗ имя_таблицы ЗАКАЗАТЬ имя_столбца )
ГДЕ СТРОКА <= номер ;


Демо-база данных

Ниже представлен выбор из таблицы «Клиенты» в образце базы данных Northwind:

Идентификатор клиента Имя клиента ContactName Адрес Город Почтовый индекс Страна
1 Альфредс Футтеркисте Мария Андерс Обере, ул.57 Берлин 12209 Германия
2 Ana Trujillo Emparedados y helados Ана Трухильо Avda. de la Constitución 2222 México D.F. 05021 Мексика
3 Антонио Морено Такерия Антонио Морено Матадерос 2312 México D.F. 05023 Мексика
4 Вокруг Рога Томас Харди 120 Ганновер пл. Лондон WA1 1DP Великобритания
5 Berglunds snabbköp Кристина Берглунд Berguvsvägen 8 Лулео С-958 22 Швеция


Примеры SQL TOP, LIMIT и FETCH FIRST

Следующий оператор SQL выбирает первые три записи из таблицы «Клиенты». (для SQL Server / MS Access):

Следующий оператор SQL показывает эквивалентный пример для MySQL:

Следующий оператор SQL показывает эквивалентный пример для Оракул:

Пример

ВЫБРАТЬ * ИЗ клиентов
ВЫБРАТЬ ТОЛЬКО ПЕРВЫЕ 3 РЯДЫ;


SQL TOP PERCENT Пример

Следующий оператор SQL выбирает первые 50% записей из Таблица «Клиенты» (для SQL Server / MS Access):

Следующий оператор SQL показывает эквивалентный пример для Оракул:

Пример

ВЫБРАТЬ * ИЗ клиентов
ВЫБРАТЬ ТОЛЬКО ПЕРВЫЕ 50 ПРОЦЕНТНЫХ СТРОК;


ДОБАВИТЬ СТАТЬЮ ГДЕ

Следующий оператор SQL выбирает первые три записи из таблицы «Клиенты», где страна "Германия" (для SQL Server / MS Access):

Следующий оператор SQL показывает эквивалентный пример для MySQL:

Следующий оператор SQL показывает эквивалентный пример для Оракул:

Пример

ВЫБРАТЬ * ИЗ клиентов
ГДЕ Страна = 'Германия'
ПОЛУЧАТЬ ТОЛЬКО ПЕРВЫЕ 3 СТРОКИ;



SQL Server SELECT TOP на практических примерах

Резюме : в этом руководстве вы узнаете, как использовать оператор SQL Server SELECT TOP для ограничения строк, возвращаемых запросом.

Введение в SQL Server

SELECT TOP

Предложение SELECT TOP позволяет ограничить количество строк или процент строк, возвращаемых в наборе результатов запроса.

Поскольку порядок строк, хранящихся в таблице, не определен, оператор SELECT TOP всегда используется вместе с предложением ORDER BY . Следовательно, набор результатов ограничен первым числом упорядоченных строк N .

Ниже показан синтаксис предложения TOP с оператором SELECT :

 

SELECT TOP (выражение) [PERCENT] [С ГАЛСТАМИ] ИЗ table_name СОРТИРОВАТЬ ПО column_name;

Язык кода: SQL (язык структурированных запросов) (sql)

В этом синтаксисе оператор SELECT может иметь другие предложения, такие как WHERE , JOIN , HAVING и GROUP BY .

выражение

После ключевого слова TOP следует выражение, определяющее количество возвращаемых строк. Выражение оценивается как значение с плавающей запятой, если используется PERCENT , в противном случае оно преобразуется в значение BIGINT .

PERCENT

Ключевое слово PERCENT указывает, что запрос возвращает первые N процентов строк, где N является результатом выражения .

WITH TIES

WITH TIES позволяет возвращать больше строк со значениями, соответствующими последней строке в ограниченном наборе результатов. Обратите внимание, что WITH TIES может привести к возврату большего количества строк, чем указано в выражении.

Например, если вы хотите вернуть самые дорогие товары, вы можете использовать TOP 1 . Однако, если два или более продукта имеют те же цены, что и самый дорогой продукт, вы пропустите другие самые дорогие продукты в результирующем наборе.

Чтобы избежать этого, вы можете использовать TOP 1 WITH TIES . В него войдет не только первый дорогой товар, но и второй и так далее.

SQL Server

SELECT TOP examples

Мы будем использовать таблицу production.products в примере базы данных для демонстрации.

1) Использование

TOP с постоянным значением

В следующем примере постоянное значение используется для возврата 10 самых дорогих продуктов.

 

ВЫБРАТЬ ТОП 10 наименование товара, список цен ИЗ производство.продукты СОРТИРОВАТЬ ПО list_price DESC;

Язык кода: SQL (язык структурированных запросов) (sql)

Вот результат:

2) Использование

TOP для возврата процента строк

В следующем примере для указания числа используется PERCENT продуктов, возвращенных в наборе результатов. Таблица production.products имеет 321 строк, поэтому один процент от 321 является дробным значением ( 3,21 ), SQL Server округляет его до следующего целого числа, которое составляет четыре ( 4 ) в Это дело.

 

ВЫБРАТЬ ВЕРХНИЙ 1 ПРОЦЕНТ наименование товара, список цен ИЗ production.products СОРТИРОВАТЬ ПО list_price DESC;

Язык кода: SQL (язык структурированных запросов) (sql)

Результат:

3) Использование

TOP WITH TIES для включения строк, соответствующих значениям в последней строке

Следующий оператор возвращает тройка самых дорогих товаров:

 

SELECT TOP 3 WITH TIES наименование товара, список цен ИЗ производство.продукты СОРТИРОВАТЬ ПО list_price DESC;

Язык кода: SQL (язык структурированных запросов) (sql)

Результат выглядит следующим образом:

В этом примере цена третьего дорогостоящего продукта составляет 6499,99 . Поскольку в заявлении использовалось TOP WITH TIES , было возвращено еще три продукта, прейскурантные цены которых совпадают с ценами третьего.

В этом руководстве вы узнали, как использовать оператор SQL Server SELECT TOP для ограничения количества строк или процента строк, возвращаемых запросом.

SQL: оператор SELECT TOP


В этом руководстве по SQL объясняется, как использовать оператор SELECT TOP SQL с синтаксисом и примерами.

Описание

Оператор SQL SELECT TOP используется для извлечения записей из одной или нескольких таблиц в базе данных и ограничения количества возвращаемых записей на основе фиксированного значения или процента.

СОВЕТ: SELECT TOP - это проприетарная версия Microsoft для ограничения результатов и может использоваться в таких базах данных, как SQL Server и MSAccess.

Для других баз данных SQL попробуйте оператор SELECT LIMIT.

Синтаксис

Синтаксис оператора SELECT TOP в SQL:

 SELECT TOP (верхнее_значение) [PERCENT]
выражения
ИЗ столов
[ГДЕ условия]
[ORDER BY выражение [ASC | DESC]]; 

Параметры или аргументы

TOP (top_value)
Он вернет максимальное количество строк в наборе результатов на основе top_value . Например, TOP (10) вернет первые 10 строк из полного набора результатов.
ПРОЦЕНТОВ
Необязательно. Если указан PERCENT, то верхние строки основаны на процентах от общего набора результатов (как указано в top_value ). Например, TOP (10) PERCENT вернет первые 10% полного набора результатов.
выражения
Столбцы или вычисления, которые вы хотите получить.
стола
Таблицы, из которых вы хотите получить записи. В предложении FROM должна быть хотя бы одна таблица.
ГДЕ условия
Необязательно. Условия, которые должны быть выполнены для выбора записей.
ORDER BY выражение
Необязательно. Он используется в операторе SELECT TOP, чтобы вы могли упорядочить результаты и нацелить те записи, которые хотите вернуть. ASC - это возрастающий порядок, а DESC - убывающий.

Пример - использование ключевого слова TOP

Давайте посмотрим на пример SQL, где мы используем ключевое слово TOP в операторе SELECT.

Например:

 ВЫБРАТЬ ТОП (5)
contact_id, last_name, first_name
ИЗ контактов
ГДЕ last_name = 'Андерсон'
ЗАКАЗАТЬ ПО contact_id; 

В этом примере SQL SELECT TOP будут выбраны первые 5 записей из таблицы контактов , где last_name - «Андерсон». Если есть другие записи в таблице contacts , у которых есть last_name 'Anderson', они не будут возвращены оператором SELECT.

Пример - использование ключевого слова TOP PERCENT

Давайте посмотрим на пример SQL, где мы используем ключевое слово TOP PERCENT в операторе SELECT.

Например:

 ВЫБРАТЬ ВЕРХ (10) ПРОЦЕНТОВ
contact_id, last_name, first_name
ИЗ контактов
ГДЕ last_name = 'Андерсон'
ЗАКАЗАТЬ ПО contact_id; 

В этом примере SQL SELECT TOP будут выбраны первые 10% записей из полного набора результатов. Таким образом, в этом примере оператор SELECT вернет первые 10% записей из таблицы контактов , где last_name - «Андерсон». Остальные 90% набора результатов не будут возвращены оператором SELECT.

SQL Server: оператор SELECT TOP


В этом руководстве по SQL Server объясняется, как использовать оператор SELECT TOP в SQL Server (Transact-SQL) с синтаксисом и примерами.

Описание

Оператор SELECT TOP SQL Server (Transact-SQL) используется для извлечения записей из одной или нескольких таблиц в SQL Server и ограничения количества возвращаемых записей на основе фиксированного значения или процента.

Синтаксис

Синтаксис оператора SELECT TOP в SQL Server (Transact-SQL):

 SELECT TOP (top_value) [PERCENT] [WITH TIES]
выражения
ИЗ столов
[ГДЕ условия]
[ORDER BY выражение [ASC | DESC]]; 

Параметры или аргументы

TOP (top_value)
Возвращает верхнее количество строк в наборе результатов на основе top_value .Например, TOP (10) вернет первые 10 строк из полного набора результатов.
ПРОЦЕНТОВ
Необязательно. Если указан PERCENT, то верхние строки основаны на процентах от общего набора результатов (как указано в top_value ). Например, TOP (10) PERCENT вернет первые 10% полного набора результатов.
С УЗКАМИ
Необязательно. Если указано предложение WITH TIES , то возвращаются строки, занимающие последнее место в ограниченном наборе результатов.Это может привести к возврату большего количества строк, чем позволяет параметр TOP .
выражения
Столбцы или вычисления, которые вы хотите получить.
стола
Таблицы, из которых вы хотите получить записи. В предложении FROM должна быть хотя бы одна таблица.
ГДЕ условия
Необязательно. Условия, которые должны быть выполнены для выбора записей.
ORDER BY выражение
Необязательно.Он используется в операторе SELECT TOP, чтобы вы могли упорядочить результаты и нацелить те записи, которые хотите вернуть. ASC - это возрастающий порядок, а DESC - убывающий.

Пример - использование ключевого слова TOP

Давайте посмотрим на пример SQL Server, где мы используем ключевое слово TOP в операторе SELECT.

Например:

 ВЫБРАТЬ ТОП (5)
employee_id, last_name, first_name
ОТ сотрудников
ГДЕ last_name = 'Андерсон'
ЗАКАЗАТЬ ПО employee_id; 

В этом примере SQL Server SELECT TOP будут выбраны первые 5 записей из таблицы employee , где last_name - «Anderson».Если есть другие записи в таблице employee , у которых есть last_name «Anderson», они не будут возвращены оператором SELECT.

Вы можете изменить этот пример, включив предложение WITH TIES следующим образом:

 ВЫБОР ТОП (5) С ЗАВЯЗКАМИ
employee_id, last_name, first_name
ОТ сотрудников
ГДЕ last_name = 'Андерсон'
ЗАКАЗАТЬ ПО employee_id; 

Предложение WITH TIES будет включать строки, которые могут быть связаны на последнем месте в ограниченном наборе результатов.Таким образом, если пятая верхняя запись является ничьей, то все записи в этой позиции будут возвращены оператором SELECT TOP. В результате будет возвращено более 5 записей.

Пример - использование ключевого слова TOP PERCENT

Давайте посмотрим на пример SQL Server, где мы используем ключевое слово TOP PERCENT в операторе SELECT.

Например:

 ВЫБРАТЬ ВЕРХ (10) ПРОЦЕНТОВ
employee_id, last_name, first_name
ОТ сотрудников
ГДЕ last_name = 'Андерсон'
ЗАКАЗАТЬ ПО employee_id; 

В этом примере SQL Server SELECT TOP будут выбраны первые 10% записей из полного набора результатов.Таким образом, в этом примере оператор SELECT вернет первые 10% записей из таблицы employee , где last_name - «Anderson». Остальные 90% набора результатов не будут возвращены оператором SELECT.

Вы можете изменить этот пример, включив предложение WITH TIES следующим образом:

 ВЫБРАТЬ ТОП (10) ПРОЦЕНТОВ С ГУЯЗКАМИ
employee_id, last_name, first_name
ОТ сотрудников
ГДЕ last_name = 'Андерсон'
ЗАКАЗАТЬ ПО employee_id; 

Предложение WITH TIES будет включать строки, которые могут быть связаны на последнем месте в ограниченном наборе результатов.Таким образом, если последняя позиция в наборе записей SELECT TOP (10) PERCENT является равной, то эти связанные записи будут возвращены оператором SELECT TOP. Это приведет к возврату более 10% полного набора записей.

Обзор и примеры оператора SQL SELECT TOP

В этой статье мы узнаем, как использовать запросы SQL SELECT TOP, а также закрепим это обучение с помощью Примеры.

Введение

Предложение TOP позволяет нам ограничить набор результатов запросов в соответствии с количеством строк или процентным соотношением ряды.Как правило, конструкции TOP и ORDER BY используются вместе. В противном случае предложение TOP вернет N количество строк в неопределенном порядке. По этой причине рекомендуется использовать предложение TOP с ORDER. BY для получения определенного отсортированного результата.

Синтаксис предложения TOP следующий:

SELECT TOP (выражение) [PERCENT]

[WITH TIES]

FROM

имя_таблицы

Аргументы

Выражение

Это числовое выражение определяет, сколько строк возвращается из запроса.Например, когда мы хотим вернуться первые 10 строк таблицы мы можем установить этот параметр как 10. В этом примере мы извлекаем случайные 5 строк из Таблица товаров.

SELECT TOP (5) Name,

ProductNumber,

StandardCost

FROM Production.Product;

ПРОЦЕНТОВ

Ключевое слово PERCENT указывает, что запрос будет возвращать строки в пропорции% n от набора результатов.Это значение должно быть от 0 до 100. Например, если мы хотим получить половину строк в таблице, достаточно установить это значение 50. Следующий запрос вернет 20 процентов строк в таблице.

SELECT TOP (20) PERCENT Name,

ProductNumber,

StandardCost

FROM Production.Product;

С УЗКАМИ

Ключевое слово WITH TIES позволяет включать в набор результатов строки, совпадающие с последним ряд.Мы должны принять во внимание один момент, связанный с WITH TIES, использование этого выражения в запросах может вызвать должно быть возвращено больше строк, чем мы указываем в выражении TOP. Например, если мы хотим получить самую высокую стоимость продукт мы можем использовать ключевое слово TOP 1. Однако, если мы добавим ключевое слово WITH TIES в оператор SQL SELECT TOP, query вернет все строки с одинаковой стоимостью. Ключевое слово WITH TIES должно использоваться с ORDER BY. Выполним следующий запрос, а затем интерпретируйте результат.

ВЫБЕРИТЕ ТОП (1) СО СТРУКТУРАМИ Имя,

Номер продукта,

StandardCost

ИЗ ПРОИЗВОДСТВА Продукт

ЗАКАЗАТЬ ПО StandardCost DESC

Как мы видим, запрос вернул более одного продукта, стоимость которого такая же, как и у первого.

Использование оператора SQL SELECT TOP с переменной

Переменные - это объекты базы данных, которые используются для хранения данных во время выполнения запроса.В следующих запрос, мы присваиваем значение переменной, и запрос вернет первые строки, которые равны присвоенной переменной ценить.

равно присвоенному переменной.

DECLARE @Val AS INT

SET @Val = 3

SELECT TOP (@Val) Имя,

ProductNumber,

StandardCost

FROM Production.Product

ORDER BY StandardCost DESC

  • Совет: Предложение TOP принимает в качестве аргумента значение от 0 до bigint (9223372036854775807).Например, следующий запрос вернет ошибку, потому что аргумент больше, чем максимальное значение bigint

SELECT TOP (9223372036854775808) Имя,

Номер продукта,

StandardCost

ИЗ ПРОИЗВОДСТВА Продукт

ЗАКАЗАТЬ ПО StandardCost DESC

Как было указано на изображении выше, запрос вернул ошибку арифметического переполнения.

Использование оператора SQL UPDATE TOP

Когда мы используем предложение TOP с оператором обновления, обновление выполняется для неопределенных строк, потому что мы не могли добавить ORDER BY для этого типа оператора обновления. Следующий запрос демонстрирует такое использование.

ОБНОВЛЕНИЕ ТОП (10) Production.ProductListColors

SET Color = 'Pink'

В то же время мы можем использовать ключевое слово PERCENT в операторах обновления.Следующий запрос изменит половину строки в таблице.

ОБНОВЛЕНИЕ ТОП (50) ПРОЦЕНТОВ Production.ProductListColors

SET Color = 'Pink'

Использование операторов SQL DELETE TOP

Мы можем использовать оператор TOP в запросах на удаление. Следующий запрос удаляет случайную строку из ProductListColors таблица.

УДАЛИТЬ ВЕРХУ (1)

ИЗ Production.ProductListColors;

Если мы хотим удалить первую строку таблицы в соответствии с определенным порядком, мы можем использовать CTE для выполнения этого типа требований. Сквозь По следующему запросу мы можем удалить строку с самым большим ProductID.

С DelCTE AS (

SELECT TOP 1 * ИЗ ПРОИЗВОДСТВА.ProductListColors

ЗАКАЗАТЬ ПО ИДЕНТИФИКАТОРУ ПРОДУКТА DESC)

УДАЛИТЬ ИЗ DelCTE

Оператор SQL SELECT TOP против опции

SET ROWCOUNT

Параметр SET ROWCOUNT ограничивает количество строк, возвращаемых из запроса. Когда мы устанавливаем SET ROWCOUNT как 4 для следующего запроса, он обрабатывает весь запрос, но возвращает только 4 строки.

SET ROWCOUNT 4

SELECT Name,

ProductNumber,

StandardCost

FROM Production.Товар

Параметр SET ROWCOUNT переопределяет ключевое слово TOP, если установленное значение ROWCOUNT меньше, чем выражение TOP, возвращаемое количество строк будет равно параметру ROWCOUNT. Например, следующий запрос вернет только 2 ряда.

SET ROWCOUNT 2

SELECT TOP (5) Имя,

ProductNumber,

StandardCost

FROM Производство.Товар

Основное различие между оператором SQL SELECT TOP и параметром SET ROWCOUNT заключается в том, что оператор SET ROWCOUNT вариант не учитывается оптимизатором запросов, и его производительность может быть хуже, чем предложение TOP. Несмотря предложения TOP участвуют в планах запросов.

Оператор SQL SELECT TOP против функции ROW_NUMBER

Функция ROW_NUMBER помогает присвоить временные номера результирующему набору запроса, и ее также можно использовать вместо ТОП статей.Например, следующий запрос вернет первые 4 строки таблицы Product.

SELECT * FROM (

SELECT Name,

ProductNumber,

StandardCost,

ROW_NUMBER () OVER (ORDER BY StandardCost DESC) AS RN

FROM Production.Product

) AS TMP8_TMP8 =

) 4

Заключение

В этой статье мы говорили об операторах SQL SELECT TOP, эта структура запроса помогает ограничить результат. набор запросов.В то же время мы узнали некоторые альтернативы предложениям TOP.

Эсат Эркеч - специалист по SQL Server, который начал свою карьеру более 8 лет назад в качестве разработчика программного обеспечения. Он является сертифицированным экспертом по решениям Microsoft SQL Server.

Большую часть своей карьеры он посвятил администрированию и разработке баз данных SQL Server. В настоящее время он занимается администрированием баз данных и бизнес-аналитикой. Вы можете найти его в LinkedIn.

Посмотреть все сообщения от Esat Erkec

Последние сообщения от Esat Erkec (посмотреть все)

sql - получить 1 верхнюю строку каждой группы

Проверка потрясающего и правильного ответа Клинта сверху:

Интересна производительность между двумя приведенными ниже запросами.52% - лучший. И 48% - второе. Повышение производительности на 4% при использовании DISTINCT вместо ORDER BY. Но ORDER BY имеет преимущество сортировки по нескольким столбцам.

  ЕСЛИ (OBJECT_ID ('tempdb .. # DocumentStatusLogs') НЕ НУЛЕВО) НАЧАТЬ ТАБЛИЦА УДАЛЕНИЯ #DocumentStatusLogs END

СОЗДАТЬ ТАБЛИЦУ #DocumentStatusLogs (
    [ID] int NOT NULL,
    [DocumentID] int NOT NULL,
    [Статус] varchar (20),
    [DateCreated] datetime
)

ВСТАВИТЬ В #DocumentStatusLogs ([ID], [DocumentID], [Status], [DateCreated]) ЗНАЧЕНИЯ (2, 1, 'S1', '29.07.2011 1:00:00')
ВСТАВИТЬ В #DocumentStatusLogs ([ID], [DocumentID], [Status], [DateCreated]) ЗНАЧЕНИЯ (3, 1, 'S2', '30.07.2011 2:00:00')
ВСТАВИТЬ В #DocumentStatusLogs ([ID], [DocumentID], [Status], [DateCreated]) ЗНАЧЕНИЯ (6, 1, 'S1', '8/02/2011 3:00:00')
ВСТАВИТЬ В #DocumentStatusLogs ([ID], [DocumentID], [Status], [DateCreated]) ЗНАЧЕНИЯ (1, 2, 'S1', '28.07.2011 4:00:00')
ВСТАВИТЬ В #DocumentStatusLogs ([ID], [DocumentID], [Status], [DateCreated]) ЗНАЧЕНИЯ (4, 2, 'S2', '30.07.2011 5:00:00')
ВСТАВИТЬ В #DocumentStatusLogs ([ID], [DocumentID], [Status], [DateCreated]) ЗНАЧЕНИЯ (5, 2, 'S3', '8/01/2011 6:00:00')
ВСТАВИТЬ В #DocumentStatusLogs ([ID], [DocumentID], [Status], [DateCreated]) ЗНАЧЕНИЯ (6, 3, 'S1', '8/02/2011 7:00:00')
  

Вариант 1:

  ВЫБРАТЬ
    [Extent1].[Я БЫ],
    [Extent1]. [DocumentID],
    [Extent1]. [Status],
    [Extent1]. [DateCreated]
ИЗ #DocumentStatusLogs AS [Extent1]
    ВНЕШНИЙ ПРИМЕНИТЬ (
        ВЫБРАТЬ ТОП 1
            [Extent2]. [ID],
            [Extent2]. [DocumentID],
            [Extent2]. [Status],
            [Extent2]. [DateCreated]
        ИЗ #DocumentStatusLogs AS [Extent2]
        ГДЕ [Extent1]. [DocumentID] = [Extent2]. [DocumentID]
        ORDER BY [Extent2]. [DateCreated] DESC, [Extent2]. [ID] DESC
    ) AS [Project2]
ГДЕ ([Project2].[ID] IS NULL ИЛИ [Project2]. [ID] = [Extent1]. [ID])
  

Вариант 2:

  ВЫБРАТЬ
    [Limit1]. [DocumentID] AS [ID],
    [Limit1]. [DocumentID] AS [DocumentID],
    [Лимит1]. [Статус] AS [Статус],
    [Limit1]. [DateCreated] AS [DateCreated]
ИЗ (
    ВЫБЕРИТЕ DISTINCT [Extent1]. [DocumentID] AS [DocumentID] ИЗ #DocumentStatusLogs AS [Extent1]
) AS [Distinct1]
    ВНЕШНИЙ ПРИМЕНИТЬ (
        ВЫБЕРИТЕ TOP (1) [Project2]. [ID] AS [ID], [Project2]. [DocumentID] AS [DocumentID], [Project2].[Статус] AS [Статус], [Project2]. [DateCreated] AS [DateCreated]
        ИЗ (
            ВЫБРАТЬ
                [Extent2]. [ID] AS [ID],
                [Extent2]. [DocumentID] AS [DocumentID],
                [Extent2]. [Status] AS [Status],
                [Extent2]. [DateCreated] AS [DateCreated]
            ИЗ #DocumentStatusLogs AS [Extent2]
            ГДЕ [Distinct1]. [DocumentID] = [Extent2]. [DocumentID]
        ) AS [Project2]
        ЗАКАЗАТЬ [Project2]. [ID] DESC
    ) AS [Limit1]
  

M $ Management Studio: выделив и запустив первый блок, выделите как вариант 1, так и вариант 2, щелкните правой кнопкой мыши -> [Показать предполагаемый план выполнения].Затем запустите все это, чтобы увидеть результаты.

Вариант 1 Результаты:

  ID DocumentID Статус Дата создания
6 1 С1 02.08.11 3:00
5 2 С3 01.08.11 6:00
6 3 С1 02.08.11 7:00
  

Вариант 2 Результаты:

  ID DocumentID Статус Дата создания
6 1 С1 02.08.11 3:00
5 2 С3 01.08.11 6:00
6 3 С1 02.08.11 7:00
  

Примечание:

Я обычно использую APPLY, когда хочу, чтобы соединение было 1-к-(1 из многих).

Я использую JOIN, если хочу, чтобы соединение было «1 ко многим» или «многие ко многим».

Я избегаю CTE с ROW_NUMBER (), если мне не нужно делать что-то продвинутое и меня устраивает снижение производительности окон.

Я также избегаю подзапросов EXISTS / IN в предложении WHERE или ON, так как я испытал это, приводя к ужасным планам выполнения. Но пробег бывает разным. Просматривайте план выполнения и профилируйте производительность там и тогда, когда это необходимо!

SQL Tutorial; Команда SQL SELECT TOP

Предложение SELECT TOP используется для возврата верхних X-чисел или N-процентной строки из таблицы.Только сервер MSSQL и база данных MS Access поддерживают предложение SELECT TOP.

Для получения ограниченного количества записей в базе данных MySQL используется предложение LIMIT, а в базе данных Oracle - ROWNUM. Вы можете указать «количество строк» ​​или «процент строк» ​​после ключевого слова TOP.

Синтаксис SQL TOP CLAUSE

ВЫБРАТЬ ВЕРХНЕЕ число | проценты имя_столбца
ИЗ имя_таблицы
ГДЕ [условие];

Примечание. Условие WHERE необязательно использовать вместе с предложением SELECT TOP.

Таблица базы данных «Сотрудник»

9013 9013 9013 901 901 903 901 903 901 901 9013 Лондон
Идентификационный номер сотрудника Имя сотрудника Возраст Пол Местоположение Заработная плата
1001 54133 901 901 Генри Йорк 100000
1002 Tina 36 Женский Москва 80000
1003 Джон 24 24 400 24 Мужской Майл 31 Мужской Лондон 70000
1005 Тара 26 Женский Москва 50000 60000

Пример сервера MSSQL и MS Access: оператор, который будет извлекать 3 верхние записи из таблицы «Сотрудник»:
Пользователь может указать количество строк или% строк

ВЫБЕРИТЕ ТОП 3 * ОТ сотрудника;

или

ВЫБРАТЬ ТОП 50 ПРОЦЕНТОВ * ОТ сотрудника;

Если используется MySQL, то вот эквивалентный пример

ВЫБРАТЬ * ОТ Сотрудника
LIMIT 3;

Если используется ORACLE, то вот эквивалентный пример

ВЫБРАТЬ * ОТ Сотрудника
ГДЕ СТРОКА <= 3;

Выход SQL TOP CLAUSE

Все эти три команды дадут следующие результаты в соответствующей базе данных.

903
Идентификационный номер сотрудника Имя сотрудника Возраст Пол Местоположение Заработная плата
1001 54133 901 901 Генри Йорк 100000
1002 Tina 36 Женский Москва 80000
1003 Джон 24 400 Джон 24 400 24 SQL SELECT TOP Полезно?

Top (MySql Limit, Oracle RowNum) - одна из самых полезных команд, используемых при выборе и фильтрации строк.Это ключевое слово TOP важно, когда нас не интересует получение всех строк, удовлетворяющих условию.

Это ключевое слово также используется при нахождении самых высоких (n-самых высоких), самых низких (n-самых низких) данных из таблицы.

Оставить комментарий

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *

© 2019 Штирлиц Сеть печатных салонов в Перми

Цифровая печать, цветное и черно-белое копирование документов, сканирование документов, ризография в Перми.