Сортировка возвращаемого результата
Результат может быть гораздо более полезным если добавить возможность упорядочивания или сортировки информации. Информация может сортироваться по алфавиту, по численному порядку, хронологическому порядку, в режиме по возрастанию или убыванию. Более того данные можно отсортировать используя комбинацию столбцов и можно использовать столбцы которые не возвращаются запросом. Сортировка результата обычно происходи после того как результат команды SELECT готов. Параметры сортировки не влияют на данные в результате, они влияют лишь на то как результат отобразится. Одни и теже строки могут быть получены как в результате запроса с сортировкой, так и в результате запроса без сортировки. Сортировка результата обеспечивается предикатом ORDER BY
Предикат ORDER BY
Предикат ORDER BY всегда находится в конце команды SELECT. Полный синтаксис команды SELECT будет изменяться по мере изучения возможностей команды, но ни одна директива не может быть после ORDER BY. Синтаксис SELECT с ключевым словом ORDER BY выглядит следующим образом
Сортировка по возрастанию или убыванию
Сортировка по возрастанию более естественна для большинства типов данных и поэтому используется по умолчанию в команде ORDER BY. Сортировка по возрастанию для чисел значит от меньшего к большему, для даты – от более старой к более новой и символьные данные сортируются по в зависимости от позиции символа в алфавите. Первая часть предиката ORDER BY значит что результат может сортироваться по одному или нескольким столбцам или выражениям
ORDER BT col(s)|expr
Предположим что нам нужен отчёт в котором должны быть указаны фамилия сотрудника, дата приёма на работу и зарплата. Данные должны быть отсортированы по алфавиту и включать только торговых представителей и менеджеров по маркетингу. Такой запрос можно получить выполнив
select last_name, hire_date, salary from employees
where job_id in (‘SA_REP’,’MK_MAN’)
order by last_name desc nulls last;
Выбранные данные можно сортировать используя любые столбцы таблицы в секции FROM, включая столбцы которые не перечислены в списке SELECT. Добавляя ключевое слово DESC к столбцу или выражению в секции ORDER BY строки сортируются по убыванию. Параметр NULLS LAST указывает, что если в строках результата присутствуют значения NULL то строки со значением NULL будут отображены в конце результата, после всех других отсортированных значений. Для указания сортировки таким образом чтобы значения NULL были вначале – используется ключевое слово NULL FIRST. Также данные можно сортировать основываясь на выражениях, как показано в следующем примере
select last_name, salary, hire_date, sysdate-hire_date tenure
from employees order by tenure;
Наименьшее значение TENURE отображается первым в результате, так как ORDER BY указывает БД отсортировать результат используя псевдоним. Обратите внимание, что результат можно сортировать по выражению без псевдонима, но использование псевдонима делает запрос более читабельным.
По умолчанию используются следующие параметры ORDER BY. Самым важным является то, что если явно не задать режим DESC — предполагается сортировка по возрастанию. Если в результате есть значения NULL то по умолчанию используется NULLS LAST при сортировке ASC и NULLS FIRST при сортировке DESC. Если не указать директиву ORDER BY в запросе, то одинаковый запрос может вернуть одинаковые данные, но в разном порядке, таким образом нельзя строить предположения о порядке возвращаемых данных без указания порядка сортировки.
Сортировка по позиции
В Oracle доступен альтернативный,более короткий метод указания столбца или выражения для сортировки. Вместо указания имени столбца используется его позиция в списке SELECT. Рассмотрим пример
select last_name, hire_date, salary from employees order by 2;
В ORDER BY директиве указано число 2. Это эквивалентно указанию поля HIRE_DATE, так как второй столбец в списке SELECT является столбцом HIRE_DATE. Сортировка по позиции доступна только для столбцов которые указаны в списке столбцов команды SELECT.
Составная сортировка
Результат может быть отсортирован по нескольким столбцам используя составную сортировку. Несколько столбцов могут быть указаны (неважно по имени или по позиции) как составной ключ сортировки, путём перечисления их в секции ORDER BY через запятую. Для выборки JOB_ID, LAST_NAME, SALARY и HIRE_DATE из таблицы EMPLOYEES и сортировки данных по убыванию в зависимости от значения столбца JO_ID, затем по возрастанию по LAST_NAME и наконец по убыванию зарплаты можно выполнить запрос такого вида
select job_id, last_name, salary, hire_date from employees
where job_id in (‘SA_REP’,’MK_MAN’) order by job_id desc nulls last, last_name, 3 desc;
- Получение, ограничение и сортировка данных используя SQL — Итоги
- Подстановка амперсанта
- Ограничение строк возвращаемых результатом
- Создание простой таблицы
- Представления
Произведение сортировки с помощью предложения ORDER BY (визуальные инструменты для баз данных)
Предложение ORDER BY позволяет сортировать результаты запроса по одному или нескольким столбцам. Его можно определить, выбрав параметры на панели «Подробности критериев».
Для сортировки результатов запроса с помощью предложения ORDER BY:
- Откройте запрос или создайте новый.
- На панели критериевщелкните в столбце Тип сортировки строку, соответствующую столбцу, по которому нужно отсортировать результаты запроса.
- В раскрывающемся списке выберите По возрастанию или По убыванию .
Очистка элемента Тип сортировки для столбца удаляет этот столбец из предложения ORDER BY.
Обратите внимание, что во время работы с панелью критериев предложение UNION запроса изменяется в соответствии с последними действиями.
При сортировке результатов по нескольким столбцам в столбце Порядок сортировки укажите порядок, в котором будут просматриваться столбцы при сортировке. Дополнительные сведения см. в разделе Как производить сортировку нескольких столбцов в запросе.
SELECT — предложение ORDER BY (Transact-SQL)
Сортирует данные, возвращенные запросом в SQL Server. Это предложение используется для следующих целей:
- Упорядочение результирующего набора запроса по заданному списку столбцов и (дополнительно) ограничение числа возвращаемых строк указанным диапазоном. Порядок, в котором строки возвращаются в результирующем наборе, не гарантируется, если не указано предложение ORDER BY.
- Определение порядка, в котором значения ранжирующей функции применяются к результирующему набору.
ORDER BY не поддерживается в инструкциях SELECT/INTO или CREATE TABLE AS SELECT (CTAS) в Azure Synapse Analytics или Системе платформы аналитики (PDW).
Синтаксис
-- Syntax for SQL Server and Azure SQL Database ORDER BY order_by_expression [ COLLATE collation_name ] [ ASC | DESC ] [ . n ] [ ] ::= < OFFSET < integer_constant | offset_row_count_expression > < ROW | ROWS >[ FETCH < FIRST | NEXT > < ROW | ROWS >ONLY ] > -- Syntax for Azure Synapse Analytics and Parallel Data Warehouse [ ORDER BY < order_by_expression [ ASC | DESC ] >[ . n ] ] Сведения о синтаксисе Transact-SQL для SQL Server 2014 (12.x) и более ранних версиях см . в документации по предыдущим версиям.
Аргументы
order_by_expression
Указывает столбец или выражение, по которому производится сортировка результирующего набора запроса. Столбец сортировки может быть указан с помощью имени или псевдонима столбца или неотрицательного целого числа, представляющего позицию столбца в списке выбора.
Можно указать несколько столбцов сортировки. Имена столбцов должны быть уникальными. Последовательность столбцов сортировки в предложении ORDER BY определяет организацию упорядоченного результирующего набора. Иными словами, результирующий набор сортируется по первому столбцу, затем упорядоченный список сортируется по второму и т. д.
Имена столбцов, на которые содержатся ссылки в предложении ORDER BY, должны однозначно соответствовать столбцу или псевдониму столбца в списке выбора либо столбцу, определенному в таблице, указанной в предложении FROM. Если предложение ORDER BY ссылается на псевдоним столбца в списке выбора, псевдоним должен использоваться отдельно, а не как часть выражения в предложении ORDER BY, например:
SELECT SCHEMA_NAME(schema_id) AS SchemaName FROM sys.objects ORDER BY SchemaName; -- correct SELECT SCHEMA_NAME(schema_id) AS SchemaName FROM sys.objects ORDER BY SchemaName + ''; -- wrong COLLATE collation_name
Указывает, что операция ORDER BY должна выполняться в соответствии с параметрами сортировки, указанными в аргументе collation_name, но не в соответствии с параметрами сортировки столбца, определенными в таблице или представлении. Аргументом collation_name может быть либо имя параметров сортировки Windows, либо имя параметров сортировки SQL. Дополнительные сведения см. в статье Collation and Unicode Support. Аргумент COLLATE применяется только к столбцам типа char, varchar, nchar и nvarchar.
ASC | DESC
Указывает порядок сортировки значений в указанном столбце — по возрастанию или по убыванию. Значение ASC сортирует от низких значений к высоким. Значение DESC сортирует от высоких значений к низким. Порядок сортировки по умолчанию — ASC. Значения NULL рассматриваются как минимально возможные значения.
OFFSET < integer_constant | offset_row_count_expression > < ROW | ROWS >
Указывает число сток, которые необходимо пропустить, прежде чем будет начат возврат строк из выражения запроса. Это значение может быть целочисленной константой или выражением, значение которого больше нуля или равно нулю.
Область применения: SQL Server 2012 (11.x) и выше, а также База данных SQL Azure.
offset_row_count_expression может быть переменной, параметром или вложенным запросом, возвращающим скалярную константу. При использовании вложенного запроса он не должен ссылаться на какие-либо столбцы, определенные в области внешнего запроса. Иными словами, он не может коррелировать с внешним запросом.
ROW и ROWS являются синонимами и оставлены для совместимости со стандартом ANSI.
В плане выполнения запроса значение смещения строки отображается в атрибуте Offset оператора запроса TOP.
FETCH < FIRST | NEXT >< integer_constant | fetch_row_count_expression > < ROW | ROWS >ONLY
Указывает число строк, возвращаемых после обработки предложения OFFSET. Это значение может быть целочисленной константой или выражением, значение которого больше единицы или равно единице.
Область применения: SQL Server 2012 (11.x) и выше, а также База данных SQL Azure.
fetch_row_count_expression может быть переменной, параметром или вложенным запросом, возвращающим скалярную константу. При использовании вложенного запроса он не должен ссылаться на какие-либо столбцы, определенные в области внешнего запроса. Иными словами, он не может коррелировать с внешним запросом.
FIRST и NEXT являются синонимами и предусмотрены для совместимости со стандартом ANSI.
ROW и ROWS являются синонимами и оставлены для совместимости со стандартом ANSI.
В плане выполнения запроса значение смещения строки отображается в атрибуте Rows или Top оператора запроса TOP.
Рекомендации
Избегайте указания столбцов в предложении ORDER BY по их порядковому номеру в списке выбора. Например, хотя инструкция SELECT ProductID, Name FROM Production.Production ORDER BY 2 верна, она будет не очень понятна другим пользователям по сравнению с тем случаем, когда столбцы указаны по именам. Кроме того, если список выбора изменится, в частности изменится порядок столбца или будут добавлены новые столбцы, то это потребует изменения предложения ORDER BY во избежание непредвиденных результатов.
В инструкции SELECT TOP (N) всегда указывайте предложение ORDER BY. Это единственный способ предсказуемым образом отметить строки, которые были обработаны предложением TOP. Дополнительные сведения см. в разделе TOP (Transact-SQL).
Совместимость
При использовании в инструкции SELECT. INTO или INSERT. SELECT предложения ORDER BY для вставки строк из другого источника вставка строк в указанном порядке не гарантируется.
Использование OFFSET и FETCH в представлении не приведет к изменению его свойства Updateability.
Ограничения
Нет ограничения на число столбцов в предложении ORDER BY, однако общий размер столбцов, перечисленных в нем, не может превышать 8060 байт.
Столбцы типа ntext, text, image, geography, geometry и xml не могут использоваться в предложении ORDER BY.
Нельзя указывать целое число или константу, если аргумент order_by_expression присутствует в ранжирующей функции. Дополнительные сведения см. в статье Предложение OVER (Transact-SQL).
Если в качестве имени таблицы в предложении FROM используется псевдоним, то только псевдоним может быть использован для обозначения столбца этой таблицы в предложении ORDER BY.
Имена и псевдонимы столбцов, указанные в предложении ORDER BY, должны быть определены в списке выбора, если инструкция SELECT содержит одно из следующих предложений или операторов:
- UNION, оператор
- Оператор EXCEPT
- INTERSECT, оператор
- SELECT DISTINCT
Кроме того, если в инструкцию входит оператор UNION, EXCEPT или INTERSECT, то имена и псевдонимы столбцов должны быть указаны в списке выбора первого (слева) запроса.
В запросе, содержащем оператор UNION, EXCEPT или INTERSECT, предложение ORDER BY допускается только в конце инструкции. Это ограничение применяется только при использовании операторов UNION, EXCEPT и INTERSECT в запросах верхнего уровня, но не во вложенных запросах. См подраздел «Примеры» ниже.
Предложение ORDER BY недопустимо в представлениях, встроенных функциях, производных таблицах и вложенных запросах, если также не указаны предложения TOP либо OFFSET и FETCH. В этих объектах предложение ORDER BY используется только для определения строк, возвращаемых предложением TOP или OFFSET и FETCH. Предложение ORDER BY не гарантирует упорядочивания результатов при запросе этих конструкций, если оно не указано в самом запросе.
Предложения OFFSET и FETCH не поддерживаются в индексированных представлениях и представлениях, определенных с предложением CHECK OPTION.
Предложения OFFSET и FETCH могут быть использованы в любом запросе, допускающем применение TOP и ORDER BY, со следующими ограничениями.
- Предложение OVER не поддерживает OFFSET и FETCH.
- Предложения OFFSET и FETCH не могут быть указаны прямо в инструкциях INSERT, UPDATE, MERGE и DELETE, но могут быть указаны во вложенных запросах, определяемых этими инструкциями. Например, в инструкции INSERT INTO SELECT предложения OFFSET и FETCH могут быть указаны в инструкции SELECT.
- В запросе, содержащем оператор UNION, EXCEPT или INTERSECT, предложения OFFSET и FETCH могут быть указаны только в конечном запросе, который определяет порядок следования результатов запроса.
- TOP нельзя сочетать с OFFSET и FETCH в одном выражении запроса (в той же области запроса).
Использование OFFSET и FETCH для ограничения числа возвращаемых строк
Для разбиения на страницы и ограничения числа строк, передаваемых клиентскому приложению, рекомендуется пользоваться предложениями OFFSET и FETCH, а не предложением TOP.
Применение в качестве решения для разбиения на страницы предложений OFFSET и FETCH потребует однократного выполнения запроса для каждой «страницы» данных, возвращаемых клиентскому приложению. Например, чтобы вернуть результаты запроса блоками по 10 строк, необходимо выполнить запрос для получения строк с 1 по 10, затем еще раз для получения строк с 11 по 20 и так далее. Каждый запрос выполняется независимо и никаким образом не связан с другими запросами. Это означает, что в отличие от использования курсора, где запрос выполняется всего один раз, а текущее состояние хранится на сервере, за отслеживание состояния отвечает клиентское приложение. Чтобы добиться стабильных результатов между запросами с предложениями OFFSET и FETCH, должны выполняться следующие условия.
- Базовые данные, используемые запросом, должны быть неизменными. Иными словами, либо строки, обработанные запросом, не должны обновляться, либо все запросы страниц выполняемого запроса должны выполняться в одной транзакции, использующей моментальный снимок или сериализуемую изоляцию транзакции. Дополнительные сведения об уровнях изоляции транзакции см. в разделе SET TRANSACTION ISOLATION LEVEL (Transact-SQL).
- Предложение ORDER BY содержит столбец или сочетание столбцов, которые гарантированно уникальны.
См. пример «Выполнение нескольких запросов в одной транзакции» в подразделе «Примеры» ниже в этом разделе.
Если согласованность планов выполнения важна для решения разбиения на страницы, подумайте над использованием указания запросов OPTIMIZE FOR для параметров OFFSET и FETCH. См. пункт «Указание выражений для значений OFFSET и FETCH» в подразделе «Примеры» ниже в этом разделе. Дополнительные сведения об OPTIMZE FOR см. в статье Указания запросов (Transact-SQL).
Примеры
| Категория | Используемые элементы синтаксиса |
|---|---|
| Основной синтаксис | ORDER BY |
| Указание порядка по возрастанию или по убыванию | DESC * ASC |
| Указание параметров сортировки | COLLATE |
| Указание условного порядка | CASE, выражение |
| Использование ORDER BY в ранжирующей функции | Ранжирующие функции |
| Ограничение числа возвращаемых строк | СМЕЩЕНИЕ * ПОЛУЧЕНИЕ |
| Использование ORDER BY с UNION, EXCEPT и INTERSECT | UNION |
Основной синтаксис
В примерах этого раздела показана базовая функциональность предложения ORDER BY с использованием минимально необходимого синтаксиса.
А. Указание единственного столбца, определенного в списке выбора
В следующем примере производится упорядочение результирующего набора по числовому столбцу ProductID . Поскольку конкретный порядок сортировки не указан, используется порядок по умолчанию (по возрастанию).
USE AdventureWorks2022; GO SELECT ProductID, Name FROM Production.Product WHERE Name LIKE 'Lock Washer%' ORDER BY ProductID; B. Указание столбца, не определенного в списке выбора
В следующем примере результирующий набор упорядочивается по столбцу, не включенному в список выбора, но определенному в таблице, указанной в предложении FROM.
USE AdventureWorks2022; GO SELECT ProductID, Name, Color FROM Production.Product ORDER BY ListPrice; C. Указание псевдонима в качестве столбца сортировки
В следующем примере в качестве столбца сортировки указывается псевдоним столбца SchemaName .
USE AdventureWorks2022; GO SELECT name, SCHEMA_NAME(schema_id) AS SchemaName FROM sys.objects WHERE type = 'U' ORDER BY SchemaName; D. Указание выражения в качестве столбца сортировки
В следующем примере в качестве столбца сортировки используется выражение. Выражение определено с использованием функции DATEPART, чтобы сортировать результирующий набор по году поступления сотрудника на работу.
USE AdventureWorks2022; GO SELECT BusinessEntityID, JobTitle, HireDate FROM HumanResources.Employee ORDER BY DATEPART(year, HireDate); Указание порядка сортировки по возрастанию и убыванию
А. Указание порядка по убыванию
В следующем примере производится упорядочение результирующего набора по числовому столбцу ProductID в убывающем порядке.
USE AdventureWorks2022; GO SELECT ProductID, Name FROM Production.Product WHERE Name LIKE 'Lock Washer%' ORDER BY ProductID DESC; B. Указание порядка по возрастанию
В следующем примере производится упорядочение результирующего набора по столбцу Name в возрастающем порядке. Символьные значения сортируются в алфавитном порядке, а не в числовом. Иными словами, отсортированное значение 10 находится перед 2.
USE AdventureWorks2022; GO SELECT ProductID, Name FROM Production.Product WHERE Name LIKE 'Lock Washer%' ORDER BY Name ASC ; C. Указание порядка и по возрастанию, и по убыванию
В следующем примере производится упорядочение результирующего набора по двум столбцам. Результирующий набор запроса сначала сортируется по возрастанию по столбцу FirstName , а затем в убывающем порядке по столбцу LastName .
USE AdventureWorks2022; GO SELECT LastName, FirstName FROM Person.Person WHERE LastName LIKE 'R%' ORDER BY FirstName ASC, LastName DESC ; Указание параметров сортировки
Следующий пример показывает, как задание параметров сортировки в предложении ORDER BY может изменить порядок, в котором возвращаются результаты запроса. Созданная таблица содержит столбец, определенный без учета регистра и параметров сортировки диакритических знаков. Вставленные значения имеют различные сочетания регистра и диакритических знаков. Поскольку параметры сортировки в предложении ORDER BY не заданы, первый запрос при упорядочении значений использует порядок сортировки столбца. Во втором запросе в предложении ORDER BY указаны параметры сортировки без учета регистра символов и диакритических знаков, поэтому строки возвращаются в другом порядке.
USE tempdb; GO CREATE TABLE #t1 (name NVARCHAR(15) COLLATE Latin1_General_CI_AI) GO INSERT INTO #t1 VALUES(N'Sánchez'),(N'Sanchez'),(N'sánchez'),(N'sanchez'); -- This query uses the collation specified for the column 'name' for sorting. SELECT name FROM #t1 ORDER BY name; -- This query uses the collation specified in the ORDER BY clause for sorting. SELECT name FROM #t1 ORDER BY name COLLATE Latin1_General_CS_AS; Указание условного порядка
В следующем примере выражение CASE используется в предложении ORDER BY, чтобы условно определить порядок сортировки строк на основе значения заданного столбца таблицы. В первом примере вычисляется значение столбца SalariedFlag таблицы HumanResources.Employee . Сотрудники, для которых столбец SalariedFlag имеет значение 1, возвращаются в порядке BusinessEntityID (по убыванию). Сотрудники, для которых столбец SalariedFlag имеет значение 0, возвращаются в порядке BusinessEntityID (по возрастанию). Во втором примере результирующий набор упорядочивается по столбцу TerritoryName , если столбец CountryRegionName содержит значение «США», и по столбцу CountryRegionName в остальных строках.
SELECT BusinessEntityID, SalariedFlag FROM HumanResources.Employee ORDER BY CASE SalariedFlag WHEN 1 THEN BusinessEntityID END DESC ,CASE WHEN SalariedFlag = 0 THEN BusinessEntityID END; GO SELECT BusinessEntityID, LastName, TerritoryName, CountryRegionName FROM Sales.vSalesPerson WHERE TerritoryName IS NOT NULL ORDER BY CASE CountryRegionName WHEN 'United States' THEN TerritoryName ELSE CountryRegionName END; Использование ORDER BY в ранжирующей функции
В следующем примере предложение ORDER BY используется в ранжирующих функциях ROW_NUMBER, RANK, DENSE_RANK и NTILE.
USE AdventureWorks2022; GO SELECT p.FirstName, p.LastName ,ROW_NUMBER() OVER (ORDER BY a.PostalCode) AS "Row Number" ,RANK() OVER (ORDER BY a.PostalCode) AS "Rank" ,DENSE_RANK() OVER (ORDER BY a.PostalCode) AS "Dense Rank" ,NTILE(4) OVER (ORDER BY a.PostalCode) AS "Quartile" ,s.SalesYTD, a.PostalCode FROM Sales.SalesPerson AS s INNER JOIN Person.Person AS p ON s.BusinessEntityID = p.BusinessEntityID INNER JOIN Person.Address AS a ON a.AddressID = p.BusinessEntityID WHERE TerritoryID IS NOT NULL AND SalesYTD <> 0; Ограничение числа возвращаемых строк
В следующих примерах предложения OFFSET и FETCH ограничивают число строк, возвращаемых запросом.
Область применения: SQL Server 2012 (11.x) и выше, а также База данных SQL Azure.
А. Указание целочисленных констант в качестве значений OFFSET и FETCH
В следующем примере в качестве значений предложений OFFSET и FETCH указана целочисленная константа. Первый запрос возвращает все строки, отсортированные по столбцу DepartmentID . Сравните результаты, возвращенные этим запросом, с результатами двух следующих запросов. В следующем запросе предложение OFFSET 5 ROWS используется для пропуска первых 5 строк и возврата оставшихся. Конечный запрос содержит предложение OFFSET 0 ROWS , чтобы начать с первой строки, а затем предложение FETCH NEXT 10 ROWS ONLY , ограничивающее число возвращаемых строк до 10 из сортированного результирующего набора.
USE AdventureWorks2022; GO -- Return all rows sorted by the column DepartmentID. SELECT DepartmentID, Name, GroupName FROM HumanResources.Department ORDER BY DepartmentID; -- Skip the first 5 rows from the sorted result set and return all remaining rows. SELECT DepartmentID, Name, GroupName FROM HumanResources.Department ORDER BY DepartmentID OFFSET 5 ROWS; -- Skip 0 rows and return only the first 10 rows from the sorted result set. SELECT DepartmentID, Name, GroupName FROM HumanResources.Department ORDER BY DepartmentID OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY; B. Указание переменных в качестве значений OFFSET и FETCH
В следующем примере объявлены переменные @RowsToSkip и @FetchRows . Затем эти переменные указаны в предложениях OFFSET и FETCH.
USE AdventureWorks2022; GO -- Specifying variables for OFFSET and FETCH values DECLARE @RowsToSkip TINYINT = 2 , @FetchRows TINYINT = 8; SELECT DepartmentID, Name, GroupName FROM HumanResources.Department ORDER BY DepartmentID ASC OFFSET @RowsToSkip ROWS FETCH NEXT @FetchRows ROWS ONLY; C. Указание выражений в качестве значений OFFSET и FETCH
В следующем примере выражение @StartingRowNumber — 1 определяет значение OFFSET, а выражение @EndingRowNumber — @StartingRowNumber + 1 — значение FETCH. Кроме этого, приведено указание запроса OPTIMIZE FOR. Это указание можно использовать, чтобы предоставить конкретное значение для локальной переменной при компиляции и оптимизации запросов. Значение используется только в процессе оптимизации запроса, но не в процессе выполнения. Дополнительные сведения см. в разделе Указания запросов (Transact-SQL).
USE AdventureWorks2022; GO -- Specifying expressions for OFFSET and FETCH values DECLARE @StartingRowNumber TINYINT = 1 , @EndingRowNumber TINYINT = 8; SELECT DepartmentID, Name, GroupName FROM HumanResources.Department ORDER BY DepartmentID ASC OFFSET @StartingRowNumber - 1 ROWS FETCH NEXT @EndingRowNumber - @StartingRowNumber + 1 ROWS ONLY OPTION ( OPTIMIZE FOR (@StartingRowNumber = 1, @EndingRowNumber = 20) ); D. Указание вложенного запроса, возвращающего скалярную константу, в качестве значений OFFSET и FETCH
В следующем примере вложенный запрос, возвращающий скалярную константу, используется для определения значения для предложения FETCH. Вложенный запрос возвращает единственное значение из столбца PageSize в таблице dbo.AppSettings .
-- Specifying a constant scalar subquery USE AdventureWorks2022; GO CREATE TABLE dbo.AppSettings (AppSettingID INT NOT NULL, PageSize INT NOT NULL); GO INSERT INTO dbo.AppSettings VALUES(1, 10); GO DECLARE @StartingRowNumber TINYINT = 1; SELECT DepartmentID, Name, GroupName FROM HumanResources.Department ORDER BY DepartmentID ASC OFFSET @StartingRowNumber ROWS FETCH NEXT (SELECT PageSize FROM dbo.AppSettings WHERE AppSettingID = 1) ROWS ONLY; Д. Выполнение нескольких запросов в одной транзакции
В следующем примере показан один из методов реализации решения разбиения на страницы, который обеспечивает стабильные результаты, возвращаемые во всех запросах. Этот запрос выполняется в одной транзакции уровня изоляции моментального снимка, а столбец, указанный в предложении ORDER BY, гарантирует уникальность столбца.
USE AdventureWorks2022; GO -- Ensure the database can support the snapshot isolation level set for the query. IF (SELECT snapshot_isolation_state FROM sys.databases WHERE name = N'AdventureWorks2022') = 0 ALTER DATABASE AdventureWorks2022 SET ALLOW_SNAPSHOT_ISOLATION ON; GO -- Set the transaction isolation level to SNAPSHOT for this query. SET TRANSACTION ISOLATION LEVEL SNAPSHOT; GO -- Beginning the transaction. BEGIN TRANSACTION; GO -- Declare and set the variables for the OFFSET and FETCH values. DECLARE @StartingRowNumber INT = 1 , @RowCountPerPage INT = 3; -- Create the condition to stop the transaction after all rows have been returned. WHILE (SELECT COUNT(*) FROM HumanResources.Department) >= @StartingRowNumber BEGIN -- Run the query until the stop condition is met. SELECT DepartmentID, Name, GroupName FROM HumanResources.Department ORDER BY DepartmentID ASC OFFSET @StartingRowNumber - 1 ROWS FETCH NEXT @RowCountPerPage ROWS ONLY; -- Increment @StartingRowNumber value. SET @StartingRowNumber = @StartingRowNumber + @RowCountPerPage; CONTINUE END; GO COMMIT TRANSACTION; GO Использование ORDER BY с UNION, EXCEPT и INTERSECT
Если запрос содержит оператор UNION, EXCEPT или INTERSECT, предложение ORDER BY должно быть указано в конце инструкции, а результаты объединенного запроса должны быть отсортированы. В следующем примере возвращаются все продукты желтого или красного цвета, отсортированные в общем списке по столбцу ListPrice .
USE AdventureWorks2022; GO SELECT Name, Color, ListPrice FROM Production.Product WHERE Color = 'Red' -- ORDER BY cannot be specified here. UNION ALL SELECT Name, Color, ListPrice FROM Production.Product WHERE Color = 'Yellow' ORDER BY ListPrice ASC; Примеры: Azure Synapse Analytics и система платформы аналитики (PDW)
В следующем примере демонстрируется упорядочение результирующего набора по числовому столбцу EmployeeKey в возрастающем порядке.
-- Uses AdventureWorks SELECT EmployeeKey, FirstName, LastName FROM DimEmployee WHERE LastName LIKE 'A%' ORDER BY EmployeeKey; В следующем примере производится упорядочение результирующего набора по числовому столбцу EmployeeKey в убывающем порядке.
-- Uses AdventureWorks SELECT EmployeeKey, FirstName, LastName FROM DimEmployee WHERE LastName LIKE 'A%' ORDER BY EmployeeKey DESC; В следующем примере производится упорядочение результирующего набора по столбцу LastName .
-- Uses AdventureWorks SELECT EmployeeKey, FirstName, LastName FROM DimEmployee WHERE LastName LIKE 'A%' ORDER BY LastName; В следующем примере производится упорядочение по двум столбцам. Этот запрос сначала сортирует в возрастающем порядке по столбцу FirstName , а затем сортирует общие значения FirstName в убывающем порядке по столбцу LastName .
-- Uses AdventureWorks SELECT EmployeeKey, FirstName, LastName FROM DimEmployee WHERE LastName LIKE 'A%' ORDER BY LastName, FirstName; Сортировка результатов SQL-запроса
Большинство системы управления базами данных предоставляют специальные средства позволяющие совершенствовать вывод запросов.
Предположим, что вы хотите выполнять простые числовые вычисления данных, чтобы затем помещать их в форму больше соответствующую вашим потребностям. SQL позволяет вам помещать скалярные выражения и константы среди выбранных полей. Например, необходимо посмотреть будущую цену товара с учетом повышения ее в два раза. Для этого воспользуемся следующим запросом:
SELECT GTame,GTypeNum, GPrice*2 FROM Goods
В результате получим:
| GName | GTypeNum | |
| Сахар | 1 | 24 |
| Мука | 1 | 18 |
| Мыло | 3 | 10 |
| Пиво | 2 | 18 |
| Фен «Philips» | 4 | 260 |
| Печенье | 1 | 30 |
| Консервы | 1 | 34 |
| Вино | 2 | 70 |
В результате запроса последний столбец без наименования, потому что это столбец данных, созданный запросом, а не просто извлеченный из таблицы. Такие столбцы появляются каждый раз, когда используются функции, константы или выражения в предложении SELECT, когда столбцы не берутся из таблиц и не имеют никаких имен. Перепишем предыдущий запрос в следующем виде:
SELECT GTame, GTypeNum, 'удвоенная цена', GPrice*2 FROM Goods
В результате получим:
| GName | GTypeNum | ||
| Сахар | 1 | удвоенная цена | 24 |
| Мука | 1 | удвоенная цена | 18 |
| Мыло | 3 | удвоенная цена | 10 |
| Пиво | 2 | удвоенная цена | 18 |
| Фен «Philips» | 4 | удвоенная цена | 260 |
| Печенье | 1 | удвоенная цена | 30 |
| Консервы | 1 | удвоенная цена | 34 |
| Вино | 2 | удвоенная цена | 70 |
Для того, чтобы упорядочить строки результата запроса используют команду ORDER BY. После этой команды указываются имена (либо имя) столбцов, по которым нужно упорядочить строки. Для упорядочения по возрастанию используется инструкция ASC, по убыванию – инструкция DESC. Например, выведем список товаров, упорядоченных в алфавитном порядке по наименованию:
SELECT * FROM Goods Order by GName ASC
В результате получим:
| Gnum | GName | GTypeNum | GPrice |
| 8 | Вино | 2 | 35 |
| 7 | Консервы | 1 | 17 |
| 2 | Мука | 1 | 9 |
| 3 | Мыло | 3 | 5 |
| 6 | Печенье | 1 | 15 |
| 4 | Пиво | 2 | 9 |
| 1 | Сахар | 1 | 12 |
| 5 | Фен «Philips» | 4 | 130 |
При упорядочении по возрастанию инструкцию ASC можно не указывать (подразумевается по умолчанию). Если необходимо произвести сортировку по нескольким полям, то их перечисляют через запятую после команды ORDER BY.