Почему SQL Server не гарантирует сортировку результатов без ORDER BY / Хабр
И снова здравствуйте. В июне OTUS вновь запускает курс «MS SQL Server разработчик», традиционно в преддверии старта курса мы начинаем делиться с вами материалом по теме.Если в вашем запросе отсутствует ORDER BY, то вы не можете быть уверены в том, что сортировка результатов не изменится со временем.
Конечно, поначалу все будет довольно предсказуемо, но по мере того, как происходят изменения (в индексах, таблицах, конфигурации сервера, объеме ваших данных), вы можете столкнуться с некоторыми неприятными сюрпризами.
Давайте начнем с чего-нибудь простого: выполним SELECT для таблицы Users базы данных Stack Overflow. В этой таблице есть кластерный индекс по колонке Id, который начинается с единицы и увеличивается до триллиона. Для этого запроса данные возвращаются в порядке кластерного индекса:
Но если создать индекс на DisplayName и Location, то SQL Server внезапно решит использовать новый индекс, а не кластерный:
Вот план выполнения:
Почему SQL Server решил использовать этот индекс, хотя ему не нужно было сортировать по DisplayName и Location? Потому что этот индекс — наименьшая копия данных, которые необходимо получить.
Давайте посмотрим на размеры индексов с помощью sp_BlitzIndex:
В кластерном индексе (CX/PK) около 8,9 млн строк и его размер 1,1 ГБ.
В некластерном индексе для DisplayName, Location также около 8,9 млн строк, но его размер всего 368 МБ. Если вам нужно сделать scan для получения результатов запроса, то почему бы не выбрать наименьший источник данных, так как это будет быстрее. Именно по этой причине SQL Server поступил таким образом.
Хорошо, теперь, когда у нас есть индекс для DisplayName и Location, давайте попробуем выполнить запрос, который ищет конкретное имя (DisplayName). Результаты получаются отсортированными по DisplayName:В плане выполнения видно, что используется индекс по DisplayName и Location:
Но если искать по другому значению, то результаты уже не будут отсортированы по DisplayName:
SQL Server обнаружил, что Alex’ов много и более разумно выполнить Clustered Index Scan вместо Index Seek + Key Lookup:
Даже в этих, действительно простых случаях, вы не можете гарантировать, что SQL Server всегда будет использовать ту копию данных, которую вы ожидаете.
В последнее время я столкнулся с гораздо более сложными случаями:
- Удаление индекса, который использовался в запросе
- Включение принудительной параметризации (Forced Parameterization), которая изменяет оценку предполагаемого количества строк, заставляя SQL Server выбирать другой индекс
- Изменение уровня совместимости базы данных (Compatibility Level) с включением нового механизма оценки кардинальности (Cardinality Estimator), который выдает другой вариант плана.
Узнать подробнее о курсе.
sql — Использование ORDER BY и GROUP BY
Вопрос задан
Изменён 1 год 7 месяцев назад
Просмотрен 305 раз
Помогите пожалуйста разобраться с запросом.
Мне нужно посчитать стоимость всех экземпляров каждого автора без учета книг «Идиот» и «Белая гвардия». В результат включить только тех авторов, у которых суммарная стоимость книг более 5000 руб и результат отсортировать по убыванию стоимости.
SELECT author, SUM(price*amount) AS Стоимость FROM book WHERE title <> 'Идиот' AND title <> 'Белая гвардия' GROUP BY author,price HAVING SUM(price*amount)>5000 ORDER BY price DESC;
Отобраны неверные записи
Query result:
| author | Стоимость |
|---|---|
| Есенин С.А. | 9750.00 |
Affected rows: 1
- sql
- group-by
- order
- order-by
author, SUM(price*amount) — дает по всем книгам одного автора, а не в разрезе автор и книга
GROUP BY author,price — группирует по автору и цене тоесть вы получите сумму по конкретной цене (если она была одна на все книги одного автора то один результат, если цена разная, то столько раз будет складывать
https://dbfiddle.
вот пример реализации.
SELECT author, SUM(price*amount) AS Стоимость
FROM book
WHERE title not in('Идиот','Белая гвардия')
GROUP BY author
HAVING SUM(price*amount)>5000
ORDER BY SUM(price*amount) DESC;
PS в ордере можно обращаться к присвоеному имени поля если поместить запрос в подзапрос:
SELECT * FROM
(SELECT author, SUM(price*amount) AS Стоимость
FROM book
WHERE title not in('Идиот','Белая гвардия')
GROUP BY author
HAVING SUM(price*amount)>5000) AS T
ORDER BY Стоимость DESC;
Зарегистрируйтесь или войдите
Регистрация через Google Регистрация через Facebook Регистрация через почтуОтправить без регистрации
ПочтаНеобходима, но никому не показывается
Отправить без регистрации
ПочтаНеобходима, но никому не показывается
By clicking “Отправить ответ”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.
SQL ORDER BY — javatpoint
следующий → ← предыдущая
Прежде чем писать запросы для сортировки записей, разберемся с синтаксисом. Синтаксис для сортировки записей в порядке возрастания:SELECT ColumnName1,…, ColumnNameN FROM TableName ORDER BY ColumnName ASC; Синтаксис для сортировки записей в порядке убывания:SELECT ColumnName1,…, ColumnNameN FROM TableName ORDER BY ColumnNameDESC; Синтаксис для сортировки записей в порядке возрастания без использования ключевого слова ASC:SELECT ColumnName1,…, ColumnNameN FROM TableName ORDER BY ColumnName; Давайте подробнее рассмотрим эту тему с помощью примеров. Мы будем использовать базу данных MySQL для написания запросов в примерах. Допустим, у нас есть таблица клиентов со следующими записями:
Пример 1: Напишите запрос для сортировки записей в порядке возрастания имен клиентов, хранящихся в таблице клиентов. Запрос: mysql> SELECT *FROM клиентов ORDER BY Name ASC; Здесь в запросе SELECT предложение ORDER BY применяется к столбцу «Имя» для сортировки записей. Ключевое слово ASC сортирует записи в порядке возрастания. Вы получите следующий вывод:
Все записи, присутствующие в таблице клиентов, отображаются в порядке возрастания имени клиента. Пример 2:Напишите запрос для сортировки записей в порядке возрастания адресов, хранящихся в таблице клиентов. Запрос: mysql> SELECT *FROM клиентов ORDER BY Address; Здесь в запросе SELECT предложение ORDER BY применяется к столбцу «Адрес» для сортировки записей. После предложения ORDER BY ключевое слово не используется. Следовательно, записи по умолчанию будут отсортированы в порядке возрастания. Вы получите следующий вывод:
Все записи, присутствующие в таблице клиентов, отображаются в порядке возрастания адреса клиента. Пример 3:Напишите запрос для сортировки записей в порядке убывания зарплаты клиентов, хранящихся в таблице клиентов. Запрос: mysql> SELECT *FROM клиентов ORDER BY Salary DESC; Здесь в запросе SELECT предложение ORDER BY применяется к столбцу ?Зарплата? для сортировки записей. Ключевое слово DESC сортирует записи в порядке убывания. Вы получите следующий вывод:
Все записи, присутствующие в таблице клиентов, отображаются в порядке убывания зарплаты клиента. Пример 4:Напишите запрос для сортировки записей в порядке убывания возраста клиента, хранящегося в таблице клиентов. Запрос: mysql> SELECT *FROM клиентов ORDER BY Age DESC; Здесь в запросе SELECT предложение ORDER BY применяется к столбцу «Возраст» для сортировки записей. Ключевое слово DESC сортирует записи в порядке убывания. Вы получите следующий вывод:
Все записи, присутствующие в таблице клиентов, отображаются в порядке убывания возраста клиента. Допустим, у нас есть другая таблица с именем Agents со следующими записями:
Пример 1: Напишите запрос для сортировки записей в порядке возрастания имен агентов, хранящихся в таблице агентов. Запрос: mysql> SELECT *FROM агентов ORDER BY Name ASC; Здесь в запросе SELECT предложение ORDER BY применяется к столбцу «Имя» для сортировки записей. Ключевое слово ASC сортирует записи в порядке возрастания. Вы получите следующий вывод:
Все записи, присутствующие в таблице операторов, отображаются в порядке возрастания имени агента. Пример 2:Напишите запрос для сортировки записей в порядке убывания рабочей области, хранящейся в таблице агентов. Запрос: mysql> SELECT *FROM агентов ORDER BY WorkArea DESC; Здесь в запросе SELECT предложение ORDER BY применяется к столбцу WorkArea для сортировки записей. Ключевое слово DESC сортирует записи в порядке убывания. Вы получите следующий вывод:
Все записи, присутствующие в таблице операторов, отображаются в порядке убывания рабочей области клиента. Пример 3:Напишите запрос для сортировки записей в порядке возрастания зарплаты агентов, хранящихся в таблице операторов. Запрос: mysql> SELECT *FROM агентов ORDER BY Зарплата; Здесь в запросе SELECT предложение ORDER BY применяется к столбцу «Зарплата» для сортировки записей. После предложения ORDER BY ключевое слово не используется. Следовательно, записи по умолчанию будут отсортированы в порядке возрастания. Вы получите следующий вывод:
Все записи, присутствующие в таблице агентов, отображаются в порядке возрастания зарплаты клиента. Пример 4:Напишите запрос для сортировки записей в порядке убывания зарплаты агентов, хранящихся в таблице операторов. Запрос: mysql> SELECT *FROM агентов ORDER BY Salary DESC; Здесь в запросе SELECT предложение ORDER BY применяется к столбцу «Зарплата» для сортировки записей. Ключевое слово DESC сортирует записи в порядке убывания. Вы получите следующий вывод:
Все записи, присутствующие в таблице агентов, отображаются в порядке убывания адреса клиента. Следующая темаSQL ORDER BY CLAUSE ASC ← предыдущая следующий → |
Ключевое слово DESC отсортирует записи в порядке убывания.
SQL ORDER BY Примеры сортировки и упорядочивания данных
Автор: Joe Gavin | Обновлено: 10 апреля 2023 г. | Комментарии (2) | Связанный: Подробнее > TSQL
Проблема
Язык SQL может быть очень мощным, помогая вам манипулировать данными и одной частью SQL. очень полезно упорядочить результаты в определенном порядке. В этом SQL учебник, мы рассмотрим различные способы использования SQL для упорядочения данных вместе с несколько примеров в базе данных Microsoft SQL Server.
Решение
Сортировка наборов результатов выполняется с помощью предложения SQL ORDER BY в операторе SELECT. ORDER BY, как следует из названия, упорядочивает набор результатов в порядке возрастания или убывания. Мы рассмотрим несколько примеров, показывающих, как ORDER BY работает.
База данных AdventureWorks2019
Для этих примеров мы будем использовать бесплатную пробную базу данных ПриключенияВоркс.
Все примеры запрашивают
таблица Person.Person.
Мы будем использовать AdventureWorksLT2019базы данных для примеров следующим образом.
ЕГЭ [AdventureWorks2019];
ORDER BY Синтаксис
Вот полный синтаксис для ORDER BY:
ORDER BY order_by_expression
[ COLLATE имя_сопоставления ]
[ АСЦ | DESC ]
[ ,... п ]
[ <смещение_выборки> ]
::=
{
СМЕЩЕНИЕ {целая_константа | offset_row_count_expression } {СТРОКА | РЯДЫ }
[
ВЫБЕРИТЕ {ПЕРВЫЙ | СЛЕДУЮЩИЙ } {integer_constant | fetch_row_count_expression } {СТРОКА | РЯДЫ } ТОЛЬКО
]
}
SQL ORDER BY в порядке возрастания
Во-первых, в следующем запросе мы получим список полных имен сотрудников (предложение WHERE) и упорядочить список по Фамилия в порядке сортировки по возрастанию.
ВЫБЕРИТЕ [Фамилия],
[Имя],
[Второе имя]
ОТ [Человека].[Человека]
ГДЕ [PersonType] = 'EM'
ЗАКАЗАТЬ ПО [Фамилия];
ИДТИ
ORDER BY по умолчанию является возрастающим порядком, и указывать его необязательно.
Здесь мы указываем порядок возрастания, но это работать так же с или без использования АСК .
ВЫБЕРИТЕ [Фамилия],
[Имя],
[Второе имя]
ОТ [Человека].[Человека]
ГДЕ [PersonType] = 'EM'
ЗАКАЗАТЬ ПО [Фамилия] ASC;
ИДТИ
SQL ORDER BY с использованием номера столбца
Я хочу предварить этот пример, сказав, что только потому, что вы можете что-то сделать не означает, что вы должны.
Допустимо сортировать результирующий набор в столбце с помощью номер столбца в зависимости от его позиции в операторе SELECT. Фамилия — это первый столбец, так что это номер 1. ORDER BY 1 по-прежнему упорядочивает результаты по фамилии. Однако это запутанно, сложнее для чтения и требует изменения, если SELECT изменится. Это определенно не очень хорошая практика.
ВЫБЕРИТЕ [Фамилия],
[Имя],
[Второе имя]
ОТ [Человека].[Человека]
ГДЕ [PersonType] = 'EM'
ЗАКАЗАТЬ ПО 1;
ИДТИ
Это даст те же результаты, что и предыдущие два запроса.
Если не указать восходящий или нисходящий, результаты будут использовать по умолчанию в порядке возрастания.
SQL ORDER BY в порядке убывания
Что делать, если мы хотим, чтобы результирующий набор отсортировался в порядке убывания? Как мы видели, первый три примера упорядочены по столбцу LastName в порядке возрастания. Вернуться результаты по Фамилии в порядке убывания, просто укажите DESC ключевое слово после ORDER BY LastName, как показано в этом операторе SQL.
ВЫБЕРИТЕ [Фамилия],
[Имя],
[Второе имя]
ОТ [Человека].[Человека]
ГДЕ [PersonType] = 'EM'
ЗАКАЗАТЬ ПО [Фамилия] DESC;
ИДТИ
SQL ORDER BY для нескольких столбцов
До сих пор наши наборы результатов были отсортированы только по одному столбцу. Не были ограничивается этим.
Здесь мы сначала сортируем по фамилии, а затем по имени. Есть
трое сотрудников по фамилии Браун. Их имена: Эрик, Джо,
и «Кевин» отсортированы по возрастанию.
ВЫБЕРИТЕ [Фамилия],
[Имя],
[Второе имя]
ОТ [Человека].[Человека]
ГДЕ [PersonType] = 'EM'
ЗАКАЗАТЬ ПО [Фамилия],
[Имя];
ИДТИ
SQL ORDER BY для нескольких столбцов в порядке возрастания и убывания
Мы также можем сортировать по нескольким столбцам и смешивать восходящий и нисходящий порядок. Для сортировки по Фамилии в порядке возрастания и по Имени в порядке убывания мы просто поставить «DESC» после имени. Теперь у нас есть первые имена в порядке: «Кевин, ‘Джо’, и «Эрик».
ВЫБЕРИТЕ [Фамилия],
[Имя],
[Второе имя]
ОТ [Человека].[Человека]
ГДЕ [PersonType] = 'EM'
ЗАКАЗАТЬ ПО [Фамилия] ASC,
[Имя] DESC;
ИДТИ
Укажите количество возвращаемых записей с помощью ORDER BY
Чтобы указать количество возвращаемых отсортированных записей, мы можем использовать предложение TOP
в операторе SELECT вместе с ORDER BY, чтобы дать нам первое x количество записей
в наборе результатов.
Этот запрос отсортирует по фамилии и вернет первые 25 записей.
ВЫБЕРИТЕ ВЕРХНИЕ 25
[Фамилия],
[Имя],
[Второе имя]
ОТ [Человека].[Человека]
ГДЕ [PersonType] = 'EM'
ЗАКАЗАТЬ ПО [Фамилия] ASC;
ИДТИ
Ограничение количества строк, возвращаемых с помощью ORDER BY
Предложения OFFSET и SET также могут использоваться в ORDER BY для ограничения количества строк, возвращаемых запросом. OFFSET указывает, сколько строк нужно пропустить перед начинает возвращать строки. Например, OFFSET, равный 0, означает пропустить 0 строк и начать в первом ряду. FETCH необязательно указывает, сколько строк нужно вернуть.
Этот запрос требует вернуть первые 25 записей фамилий сотрудников, Имя и Отчество, начиная с первой записи:
DECLARE @PageNumber INT = 0
ОБЪЯВИТЬ @RowsOfPage INT = 25
ВЫБЕРИТЕ [Фамилия],
[Имя],
[Второе имя]
ОТ [Человека].[Человека]
ГДЕ [PersonType] = 'EM'
ЗАКАЗАТЬ ПО [Фамилия] ASC, [Имя] ASC
СМЕЩЕНИЕ @PageNumber РЯД
FETCH NEXT @RowsOfPage ТОЛЬКО СТРОКИ;
ИДТИ
Изменение OFFSET на 25 даст нам следующие 25 результатов.
DECLARE @PageNumber INT = 25
ОБЪЯВИТЬ @RowsOfPage INT = 25
ВЫБЕРИТЕ [Фамилия],
[Имя],
[Второе имя]
ОТ [Человека].[Человека]
ГДЕ [PersonType] = 'EM'
ЗАКАЗАТЬ ПО [Фамилия] ASC, [Имя] ASC
СМЕЩЕНИЕ @PageNumber РЯД
FETCH NEXT @RowsOfPage ТОЛЬКО СТРОКИ;
ИДТИ
Преимущество использования OFFSET и FETCH в том, что это позволяет через набор результатов, как мы видели бы в поиске Google. Здесь мы будем вернуть все записи, по 25 за раз в одном запросе:
DECLARE @PageNumber INT
ОБЪЯВИТЬ @RowsOfPage INT
ОБЪЯВИТЬ @MaxTablePage FLOAT
УСТАНОВИТЕ @PageNumber = 1
УСТАНОВИТЕ @RowsOfPage = 25
ВЫБЕРИТЕ @MaxTablePage = СЧЁТ (*)
ОТ [Человека].[Человека]
ГДЕ [PersonType] = 'EM'
УСТАНОВИТЬ @MaxTablePage = ПОТОЛОК(@MaxTablePage / @RowsOfPage)
ПОКА @MaxTablePage >= @PageNumber
НАЧИНАТЬ
ВЫБЕРИТЕ [Фамилия],
[Имя],
[Второе имя]
ОТ [Человека].[Человека]
ГДЕ [PersonType] = 'EM'
ЗАКАЗАТЬ ПО [Фамилия] ASC,
[FirstName] СМЕЩЕНИЕ ASC (@PageNumber - 1) * @RowsOfPage ROWS FETCH NEXT @RowsOfPage ROWS ONLY
УСТАНОВИТЬ @PageNumber = @PageNumber + 1
КОНЕЦ;
ИДТИ
Следующие шаги
Вот несколько подсказок MSSQLTips с дополнительными примерами использования предложения ORDER BY:
- ВЫБЕРИТЕ с ЗАКАЗАТЬ ПО
- SQL ORDER BY Пункт
- Примеры предложения SQL ORDER BY
- SQL WHERE IS NOT NULL для SELECT, INSERT, UPDATE и DELETE
- Расширенное использование SQL Server ORDER BY Пункт
- SQL Server SELECT Примеры
- Учебник по SQL-запросам
- Избегайте ORDER BY в представлениях SQL Server
- Построение индексов SQL Server в порядке возрастания и убывания
- Выполнение команд динамического SQL в SQL Server
- Пример курсора SQL Server
Об авторе
Джо Гэвин из района Большого Бостона.