Оператор 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 строк из полного набора результатов.
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 :
Язык кода: SQL (язык структурированных запросов) (sql)
SELECT TOP (выражение) [PERCENT] [С ГАЛСТАМИ] ИЗ table_name СОРТИРОВАТЬ ПО column_name;
В этом синтаксисе оператор 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 самых дорогих продуктов.
Язык кода: SQL (язык структурированных запросов) (sql)
ВЫБРАТЬ ТОП 10 наименование товара, список цен ИЗ производство.продукты СОРТИРОВАТЬ ПО list_price DESC;
Вот результат:
2) Использование
TOP для возврата процента строк В следующем примере для указания числа используется PERCENT продуктов, возвращенных в наборе результатов. Таблица production.products имеет 321 строк, поэтому один процент от 321 является дробным значением ( 3,21 ), SQL Server округляет его до следующего целого числа, которое составляет четыре ( 4 ) в Это дело.
Язык кода: SQL (язык структурированных запросов) (sql)
ВЫБРАТЬ ВЕРХНИЙ 1 ПРОЦЕНТ наименование товара, список цен ИЗ production.products СОРТИРОВАТЬ ПО list_price DESC;
Результат:
3) Использование
TOP WITH TIES для включения строк, соответствующих значениям в последней строкеСледующий оператор возвращает тройка самых дорогих товаров:
Язык кода: SQL (язык структурированных запросов) (sql)
SELECT TOP 3 WITH TIES наименование товара, список цен ИЗ производство.продукты СОРТИРОВАТЬ ПО list_price DESC;
Результат выглядит следующим образом:
В этом примере цена третьего дорогостоящего продукта составляет 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.
Таблица базы данных «Сотрудник»
| Идентификационный номер сотрудника | Имя сотрудника | Возраст | Пол | Местоположение | Заработная плата | |||||||
| 1001 54133 | 901 901 | Генри Йорк | 100000 | |||||||||
| 1002 | Tina | 36 | Женский | Москва | 80000 | |||||||
| 1003 | Джон | 24 | 24 | 400 | 24 | Мужской | Майл | 31 | Мужской | Лондон | 70000 | |
| 1005 | Тара | 26 | Женский | Москва | 50000 | 9013 9013 9013 901 901 903 901 903 901 901 9013 Лондон60000 |
Пример сервера MSSQL и MS Access: оператор, который будет извлекать 3 верхние записи из таблицы «Сотрудник»:
Пользователь может указать количество строк или% строк
ВЫБЕРИТЕ ТОП 3 * ОТ сотрудника;
или
ВЫБРАТЬ ТОП 50 ПРОЦЕНТОВ * ОТ сотрудника;
Если используется MySQL, то вот эквивалентный пример
ВЫБРАТЬ * ОТ Сотрудника
LIMIT 3;
Если используется ORACLE, то вот эквивалентный пример
ВЫБРАТЬ * ОТ Сотрудника
ГДЕ СТРОКА <= 3;
Все эти три команды дадут следующие результаты в соответствующей базе данных.
| Идентификационный номер сотрудника | Имя сотрудника | Возраст | Пол | Местоположение | Заработная плата | ||
| 1001 54133 | 901 901 | Генри Йорк | 100000 | ||||
| 1002 | Tina | 36 | Женский | Москва | 80000 | ||
| 1003 | Джон | 24 | 400 | Джон | 24 | 903400 | 24 SQL SELECT TOP Полезно? Top (MySql Limit, Oracle RowNum) - одна из самых полезных команд, используемых при выборе и фильтрации строк.Это ключевое слово TOP важно, когда нас не интересует получение всех строк, удовлетворяющих условию. Это ключевое слово также используется при нахождении самых высоких (n-самых высоких), самых низких (n-самых низких) данных из таблицы. Оставить комментарий |