Select 1 from что это
Перейти к содержимому

Select 1 from что это

  • автор:

Урок 5. Ограничение и смещение

Кроме фильтров и сортировок, в SELECT-запросах можно также ограничивать выборку. Например, когда вам нужные не все данные, а скажем только первые 10 строк. Для этого существует конструкция LIMIT.

Работать в этом уроке будем с такой таблицей:

Таблица products

id name count price country
1 Телевизор 3 43200.00 RU
2 Микроволновая печь 4 3200.00 UA
3 Холодильник 3 12000.00 RU
4 Роутер 1 1340.00 US
5 Компьютер 0 26150.00 RU
6 Холодильник 2 2 14390.00 BL
7 Чайник 8 1200.00 RU
8 Дрон 1 45990.00 CH
9 Вентилятор 3 3000.00 RU

Давайте попробуем вывести 5 самых дорогих товаров. Сперва напишем базовый SQL-запрос:

SELECT * FROM products ORDER BY price DESC

И с помощью данного запроса мы получим все товары, отсортированные по цене в обратном порядке — ORDER BY price DESC. То есть дорогие товары будут сверху:

Таблица products с сортировкой

id name count price country
8 Дрон 1 45990.00 CH
1 Телевизор 3 43200.00 RU
5 Компьютер 0 26150.00 RU
6 Холодильник 2 2 14390.00 BL
3 Холодильник 3 12000.00 RU
2 Микроволновая печь 4 3200.00 UA
9 Вентилятор 3 3000.00 RU
4 Роутер 1 1340.00 US
7 Чайник 8 1200.00 RU

Теперь, если я оставлю верхние 5 записей, то как раз и получу 5 самых дорогих товаров:

SELECT * FROM products ORDER BY price DESC LIMIT 5

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

5 самых дорогих товара

id name count price country
8 Дрон 1 45990.00 CH
1 Телевизор 3 43200.00 RU
5 Компьютер 0 26150.00 RU
6 Холодильник 2 2 14390.00 BL
3 Холодильник 3 12000.00 RU

Теперь давайте получим 5 самых дорогих товаров, которые есть на складе. Для этого нужно написать такой SQL-запрос:

SELECT * FROM products WHERE count > 0 ORDER BY price DESC LIMIT 5

После его выполнения выдача немного изменится, так как товары которые закончились в итоговую таблицу не попадают:

5 самых дорогих товара, которые есть на складе

id name count price country
8 Дрон 1 45990.00 CH
1 Телевизор 3 43200.00 RU
6 Холодильник 2 2 14390.00 BL
3 Холодильник 3 12000.00 RU
2 Микроволновая печь 4 3200.00 UA

Обратите внимание на порядок следования блоков запроса:

1. Сперва идет SELECT FROM — выбрать ИЗ;
2. Затем блок условия WHERE;
3. После сортировка ORDER BY;
4. И завершает ограничение LIMIT.

Первый блок SELECT FROM обязательный, так как он говорит, что мы хотим сделать.

Остальные блоки опциональны. То есть мы можем убрать любой из них, главное, чтобы сохранялся порядок: (1) WHERE (2) ORDER BY (3) LIMIT.

Смещение с помощью OFFSET

Но вернемся снова к LIMIT. И помимо ограничения выборки, мы также можем делать смещение. Например, искать не первые 5 самых дорогих товаров, а следующую пятерку.

Для этого после LIMIT нужно добавить блок OFFSET:

SELECT * FROM products WHERE count > 0 ORDER BY price DESC LIMIT 5 OFFSET 5

И собственно конструкция OFFSET указывает на то, сколько записей нужно пропустить. После запуска мы получим 3 товара, так как изначально в таблице у нас 8 товаров, из которых 5 мы пропускаем с помощью OFFSET, а затем с помощью LIMIT выводим еще 5, но так как после пропуска остается всего 3 товара, то их мы и видим:

Таблица products после смещения и сортировки

id name count price country
6 Холодильник 2 2 14390.00 BL
3 Холодильник 3 12000.00 RU
2 Микроволновая печь 4 3200.00 UA

Постраничный просмотр

Благодаря этой особенности, с помощью LIMIT и OFFSET можно организовывать постраничный просмотр информации. Например, нам нужно выводить по два товара на странице, скажем на каком сайте или в программе.

Тогда в LIMIT мы прописываем двойку, а для OFFSET изначально ставим 0:

SELECT * FROM products WHERE count > 0 ORDER BY price DESC LIMIT 2 OFFSET 0

После запуска SQL-запроса мы увидим два самых дорогих товара, которые будут отображаться на первой странице:

Товары для первой страницы

id name count price country
8 Дрон 1 45990.00 CH
1 Телевизор 3 43200.00 RU

Далее нам нужно получить следующую пару. Для этого делаем пропуск первых двух товаров:

SELECT * FROM products WHERE count > 0 ORDER BY price DESC LIMIT 2 OFFSET 2
Товары для второй страницы

id name count price country
5 Компьютер 0 26150.00 RU
6 Холодильник 2 2 14390.00 BL

Теперь получим товары на третьей странице и для этого мы можем воспользоваться вот такой формулой:
(P — 1) x N, где P — это номер страницы, которая нам нужна, а N – это количество товаров, которые мы выводим на странице.

Значение N у нас фиксировано и указывается в блоке LIMIT. То есть сейчас это 2.
Значение P мы выбираем из набора целых чисел: 1, 2, 3 и тд.
Результат вычисления мы подставляем в блок OFFSET.

Сейчас мы делаем расчеты для третьей страницы, поэтому получаем:
(P — 1) x N = (3 — 1) x 2 = 4

Ставим четверку в OFFSET и получаем финальный SQL-запрос:

SELECT * FROM products WHERE count > 0 ORDER BY price DESC LIMIT 2 OFFSET 4

Который вернет следующую таблицу:

Товары для третьей страницы:

id name count price country
5 Компьютер 0 26150.00 RU
6 Холодильник 2 2 14390.00 BL

С основой мы закончили, но хочу отметить одну особенность конструкции OFFSET, а именно то, что OFFSET в MySQL является частью LIMIT.

То есть мы можем использовать или чистый LIMIT, или LIMIT в паре с OFFSET, но не можем использовать OFFSET сам по себе, так как это будет синтаксической ошибкой.

Также мы не можем менять LIMIT и OFFSET местами.

Что ж, на этом с базовыми возможностями ограничений и смещений мы заканчиваем, а в следующем уроке рассмотрим особенности применения LIMIT и OFFSET в PostgreSQL, MS SQLServer, Oracle и SQLite.

Следующий урок

Урок 6. TOP, LIMIT, FETCH и OFFSET в других базах

Изучаем особенности использования TOP, LIMIT, FETCH и OFFSET в PostgreSQL, MS SQLServer, Oracle и SQLite.

Полный курс с практикой

  • 57 уроков
  • 261 задание
  • Сертификат
  • Поддержка преподавателя
  • Доступ к курсу навсегда
  • Можно в рассрочку

SELECT 1 — какой смысл этого запроса?

Какой смысл несет SQL запрос SELECT 1 . Знаю, что он возвращает единицу, но зачем это нужно? Где его применяют на практике?

Отслеживать
8,331 4 4 золотых знака 26 26 серебряных знаков 48 48 бронзовых знаков
задан 3 дек 2011 в 9:54
13.6k 13 13 золотых знаков 62 62 серебряных знака 122 122 бронзовых знака

3 ответа 3

Сортировка: Сброс на вариант по умолчанию

Ну например, что бы проверить, работает ли база вообще. Потому что если она этот запрос не может обработать, то дела плохи.

Отслеживать
ответ дан 3 дек 2011 в 9:55
112k 6 6 золотых знаков 93 93 серебряных знака 159 159 бронзовых знаков

Вообще говоря, это вычисляемый столбец: select . А так в зависимости от контекста. Например, с предикатом [NOT] EXISTS, т.к. выводить что-нибудь в списке столбцов смысла не имеет.

Отслеживать
ответ дан 3 дек 2011 в 10:38
11.5k 16 16 серебряных знаков 16 16 бронзовых знаков

С помощью подобных запросов, можно почти бесплатно генерировать числовые последовательности.

Сам по себе — просто получить константу)

select n2.n * 4 + n1.n + 1 as n from ( select n = 0 union all select 1 union all select 2 union all select 3 ) n1, ( select n = 0 union all select 1 union all select 2 union all select 3 ) n2 

Примеры SELECT (Transact-SQL)

В этой статье приведены примеры использования инструкции SELECT .

В этой статье требуется AdventureWorks2022 пример базы данных, которую можно скачать на домашней странице примеров и проектов сообщества Microsoft SQL Server.

А. Использование SELECT для получения строк и столбцов

В следующем примере приведены три примера кода. В ходе выполнения первого примера кода возвращаются все строки (предложение WHERE не указано), а также все столбцы (используется звездочка, * ) таблицы Product базы данных AdventureWorks2022 .

USE AdventureWorks2022; GO SELECT * FROM Production.Product ORDER BY Name ASC; -- Alternate way. USE AdventureWorks2022; GO SELECT p.* FROM Production.Product AS p ORDER BY Name ASC; GO 

В ходе выполнения данного примера кода происходит выдача всех строк (предложение WHERE не задано) и подмножества столбцов ( Name , ProductNumber , ListPrice ) таблицы Product базы данных AdventureWorks2022 . Дополнительно выведено название столбца.

USE AdventureWorks2022; GO SELECT Name, ProductNumber, ListPrice AS Price FROM Production.Product ORDER BY Name ASC; GO 

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

USE AdventureWorks2022; GO SELECT Name, ProductNumber, ListPrice AS Price FROM Production.Product WHERE ProductLine = 'R' AND DaysToManufacture < 4 ORDER BY Name ASC; GO 

B. Использование SELECT с заголовками столбцов и вычислениями

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

USE AdventureWorks2022; GO SELECT p.Name AS ProductName, NonDiscountSales = (OrderQty * UnitPrice), Discounts = ((OrderQty * UnitPrice) * UnitPriceDiscount) FROM Production.Product AS p INNER JOIN Sales.SalesOrderDetail AS sod ON p.ProductID = sod.ProductID ORDER BY ProductName DESC; GO 

Данный запрос вычисляет доход от продажи по каждому виду продукции для каждого заказа.

USE AdventureWorks2022; GO SELECT 'Total income is', ((OrderQty * UnitPrice) * (1.0 - UnitPriceDiscount)), ' for ', p.Name AS ProductName FROM Production.Product AS p INNER JOIN Sales.SalesOrderDetail AS sod ON p.ProductID = sod.ProductID ORDER BY ProductName ASC; GO 

C. Использование DISTINCT с SELECT

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

USE AdventureWorks2022; GO SELECT DISTINCT JobTitle FROM HumanResources.Employee ORDER BY JobTitle; GO 

D. Создание таблиц с помощью SELECT INTO

В следующем примере в базе данных #Bicycles создается временная таблица tempdb .

USE tempdb; GO IF OBJECT_ID(N'#Bicycles', N'U') IS NOT NULL DROP TABLE #Bicycles; GO SELECT * INTO #Bicycles FROM AdventureWorks2022.Production.Product WHERE ProductNumber LIKE 'BK%'; GO 

В данном примере создается постоянная таблица NewProducts .

USE AdventureWorks2022; GO IF OBJECT_ID('dbo.NewProducts', 'U') IS NOT NULL DROP TABLE dbo.NewProducts; GO ALTER DATABASE AdventureWorks2022 SET RECOVERY BULK_LOGGED; GO SELECT * INTO dbo.NewProducts FROM Production.Product WHERE ListPrice > $25 AND ListPrice < $100; GO ALTER DATABASE AdventureWorks2022 SET RECOVERY FULL; GO 

Д. Использование сопоставленных вложенных запросов

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

В первом примере представлены семантически эквивалентные запросы для демонстрации различий в использовании ключевых слов EXISTS и IN . В обоих примерах приведены допустимые вложенные запросы, извлекающие по одному экземпляру продукции каждого наименования, для которых модель продукта — «long sleeve logo jersey» (кофта с длинными рукавами, с эмблемой), а значения столбцов ProductModelID таблиц Product и ProductModel совпадают.

USE AdventureWorks2022; GO SELECT DISTINCT Name FROM Production.Product AS p WHERE EXISTS ( SELECT * FROM Production.ProductModel AS pm WHERE p.ProductModelID = pm.ProductModelID AND pm.Name LIKE 'Long-Sleeve Logo Jersey%' ); GO -- OR USE AdventureWorks2022; GO SELECT DISTINCT Name FROM Production.Product WHERE ProductModelID IN ( SELECT ProductModelID FROM Production.ProductModel AS pm WHERE p.ProductModelID = pm.ProductModelID AND Name LIKE 'Long-Sleeve Logo Jersey%' ); GO 

В следующем примере используется и извлекается IN один экземпляр первого имени и имени семьи каждого сотрудника, для которого указан 5000.00 бонус в SalesPerson таблице, и для которого идентификаторы сотрудников совпадают в Employee таблицах и SalesPerson таблицах.

USE AdventureWorks2022; GO SELECT DISTINCT p.LastName, p.FirstName FROM Person.Person AS p INNER JOIN HumanResources.Employee AS e ON e.BusinessEntityID = p.BusinessEntityID WHERE 5000.00 IN ( SELECT Bonus FROM Sales.SalesPerson AS sp WHERE e.BusinessEntityID = sp.BusinessEntityID ); GO 

Предыдущий вложенный запрос в этом операторе нельзя оценивать независимо от внешнего запроса. Он требует значения параметра Employee.EmployeeID , однако это значение меняется, когда ядро СУБД SQL Server обрабатывает строки в Employee .

Коррелированный вложенный запрос также может использоваться в предложении HAVING внешнего запроса. В данном примере осуществляется поиск моделей продуктов, для которых максимальная цена в каталоге в два раза превышает среднюю цену по нему.

USE AdventureWorks2022; GO SELECT p1.ProductModelID FROM Production.Product AS p1 GROUP BY p1.ProductModelID HAVING MAX(p1.ListPrice) >= ( SELECT AVG(p2.ListPrice) * 2 FROM Production.Product AS p2 WHERE p1.ProductModelID = p2.ProductModelID ); GO 

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

USE AdventureWorks2022; GO SELECT DISTINCT pp.LastName, pp.FirstName FROM Person.Person pp INNER JOIN HumanResources.Employee e ON e.BusinessEntityID = pp.BusinessEntityID WHERE pp.BusinessEntityID IN ( SELECT SalesPersonID FROM Sales.SalesOrderHeader WHERE SalesOrderID IN ( SELECT SalesOrderID FROM Sales.SalesOrderDetail WHERE ProductID IN ( SELECT ProductID FROM Production.Product p WHERE ProductNumber = 'BK-M68B-42' ) ) ); GO 

F. Использование GROUP BY

В следующем примере находится общий объем продаж для каждого заказа в базе данных.

USE AdventureWorks2022; GO SELECT SalesOrderID, SUM(LineTotal) AS SubTotal FROM Sales.SalesOrderDetail GROUP BY SalesOrderID ORDER BY SalesOrderID; GO 

Так как в запросе используется предложение GROUP BY , то для каждого заказа выводится только одна строка, содержащая общий объем продаж.

G. Использование GROUP BY с несколькими группами

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

USE AdventureWorks2022; GO SELECT ProductID, SpecialOfferID, AVG(UnitPrice) AS [Average Price], SUM(LineTotal) AS SubTotal FROM Sales.SalesOrderDetail GROUP BY ProductID, SpecialOfferID ORDER BY ProductID; GO 

H. Использование GROUP BY и WHERE

В следующем примере после извлечения строк, содержащих цены каталога, превышающие $1000 , происходит их разделение на группы.

USE AdventureWorks2022; GO SELECT ProductModelID, AVG(ListPrice) AS [Average List Price] FROM Production.Product WHERE ListPrice > $1000 GROUP BY ProductModelID ORDER BY ProductModelID; GO 

I. Использование GROUP BY с выражением

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

USE AdventureWorks2022; GO SELECT AVG(OrderQty) AS [Average Quantity], NonDiscountSales = (OrderQty * UnitPrice) FROM Sales.SalesOrderDetail GROUP BY (OrderQty * UnitPrice) ORDER BY (OrderQty * UnitPrice) DESC; GO 

J. Использование GROUP BY с ORDER BY

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

USE AdventureWorks2022; GO SELECT ProductID, AVG(UnitPrice) AS [Average Price] FROM Sales.SalesOrderDetail WHERE OrderQty > 10 GROUP BY ProductID ORDER BY AVG(UnitPrice); GO 

K. Использование предложения HAVING

В первом из приведенных ниже примеров показывается использование предложения HAVING с агрегатной функцией. В нем производится группировка строк таблицы SalesOrderDetail по коду продукта, а также удаляются строки, соответствующие продуктам, для которых средний объем заказа не превышает пяти. Во втором примере показывается использование предложения HAVING без агрегатной функции.

USE AdventureWorks2022; GO SELECT ProductID FROM Sales.SalesOrderDetail GROUP BY ProductID HAVING AVG(OrderQty) > 5 ORDER BY ProductID; GO 

В данном запросе внутри предложения LIKE используется предложение HAVING .

USE AdventureWorks2022; GO SELECT SalesOrderID, CarrierTrackingNumber FROM Sales.SalesOrderDetail GROUP BY SalesOrderID, CarrierTrackingNumber HAVING CarrierTrackingNumber LIKE '4BD%' ORDER BY SalesOrderID ; GO 

L. Использование HAVING и GROUP BY

В следующем примере показано использование предложений GROUP BY , HAVING , WHERE и ORDER BY в одной инструкции SELECT . В результате его выполнения в группах и сводных значениях не учитываются строки, соответствующие продуктам с ценами выше $25 и средним объемом заказов ниже 5. Также осуществляется сортировка результатов по ProductID .

USE AdventureWorks2022; GO SELECT ProductID FROM Sales.SalesOrderDetail WHERE UnitPrice < 25.00 GROUP BY ProductID HAVING AVG(OrderQty) >5 ORDER BY ProductID; GO 

M. Использование HAVING с СУММ и AVG

В следующем примере производится группировка строк таблицы SalesOrderDetail по коду продукта, а затем выводятся только те группы, для которых общий объем продаж составляет более $1000000.00 , а средний объем заказа не превышает 3 .

USE AdventureWorks2022; GO SELECT ProductID, AVG(OrderQty) AS AverageQuantity, SUM(LineTotal) AS Total FROM Sales.SalesOrderDetail GROUP BY ProductID HAVING SUM(LineTotal) > $1000000.00 AND AVG(OrderQty) < 3; GO 

Чтобы просмотреть продукты с общим объемом продаж, превышающих $2000000.00 , используйте следующий запрос:

USE AdventureWorks2022; GO SELECT ProductID, Total = SUM(LineTotal) FROM Sales.SalesOrderDetail GROUP BY ProductID HAVING SUM(LineTotal) > $2000000.00; GO 

Если вы хотите убедиться в наличии не менее 1500 элементов, участвующих в вычислениях для каждого продукта, используйте HAVING COUNT(*) > 1500 для устранения продуктов, возвращающих итоги для меньшего количества 1500 проданных элементов. Этот запрос выглядит следующим образом.

USE AdventureWorks2022; GO SELECT ProductID, SUM(LineTotal) AS Total FROM Sales.SalesOrderDetail GROUP BY ProductID HAVING COUNT(*) > 1500; GO 

О. Использование указания оптимизатора INDEX

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

USE AdventureWorks2022; GO SELECT pp.FirstName, pp.LastName, e.NationalIDNumber FROM HumanResources.Employee AS e WITH (INDEX (AK_Employee_NationalIDNumber)) INNER JOIN Person.Person AS pp ON e.BusinessEntityID = pp.BusinessEntityID WHERE LastName = 'Johnson'; GO -- Force a table scan by using INDEX = 0. USE AdventureWorks2022; GO SELECT pp.LastName, pp.FirstName, e.JobTitle FROM HumanResources.Employee AS e WITH (INDEX = 0) INNER JOIN Person.Person AS pp ON e.BusinessEntityID = pp.BusinessEntityID WHERE LastName = 'Johnson'; GO 

M. Использование OPTION и подсказок GROUP

В следующем примере демонстрируется совместное использование предложений OPTION (GROUP) и GROUP BY .

USE AdventureWorks2022; GO SELECT ProductID, OrderQty, SUM(LineTotal) AS Total FROM Sales.SalesOrderDetail WHERE UnitPrice < $5.00 GROUP BY ProductID, OrderQty ORDER BY ProductID, OrderQty OPTION (HASH GROUP, FAST 10); GO 

O. Использование указания запроса UNION

В следующем примере используется указание запроса MERGE UNION .

USE AdventureWorks2022; GO SELECT BusinessEntityID, JobTitle, HireDate, VacationHours, SickLeaveHours FROM HumanResources.Employee AS e1 UNION SELECT BusinessEntityID, JobTitle, HireDate, VacationHours, SickLeaveHours FROM HumanResources.Employee AS e2 OPTION (MERGE UNION); GO 

P. Использование UNION

При выполнении следующего примера в результирующий набор включается содержимое столбцов ProductModelID и Name таблиц ProductModel и Gloves .

USE AdventureWorks2022; GO IF OBJECT_ID('dbo.Gloves', 'U') IS NOT NULL DROP TABLE dbo.Gloves; GO -- Create Gloves table. SELECT ProductModelID, Name INTO dbo.Gloves FROM Production.ProductModel WHERE ProductModelID IN (3, 4); GO -- Here is the simple union. USE AdventureWorks2022; GO SELECT ProductModelID, Name FROM Production.ProductModel WHERE ProductModelID NOT IN (3, 4) UNION SELECT ProductModelID, Name FROM dbo.Gloves ORDER BY Name; GO 

В. Использование SELECT INTO с UNION

При выполнении следующего примера предложение INTO во второй инструкции SELECT указывает, что в таблице с именем ProductResults содержится итоговый результирующий набор объединения заданных столбцов таблиц ProductModel и Gloves . Таблица Gloves была создана в результате выполнения первой инструкции SELECT .

USE AdventureWorks2022; GO IF OBJECT_ID('dbo.ProductResults', 'U') IS NOT NULL DROP TABLE dbo.ProductResults; GO IF OBJECT_ID('dbo.Gloves', 'U') IS NOT NULL DROP TABLE dbo.Gloves; GO -- Create Gloves table. SELECT ProductModelID, Name INTO dbo.Gloves FROM Production.ProductModel WHERE ProductModelID IN (3, 4); GO USE AdventureWorks2022; GO SELECT ProductModelID, Name INTO dbo.ProductResults FROM Production.ProductModel WHERE ProductModelID NOT IN (3, 4) UNION SELECT ProductModelID, Name FROM dbo.Gloves; GO SELECT ProductModelID, Name FROM dbo.ProductResults; 

R. Использование UNION двух операторов SELECT с ORDER BY

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

USE AdventureWorks2022; GO IF OBJECT_ID('dbo.Gloves', 'U') IS NOT NULL DROP TABLE dbo.Gloves; GO -- Create Gloves table. SELECT ProductModelID, Name INTO dbo.Gloves FROM Production.ProductModel WHERE ProductModelID IN (3, 4); GO /* INCORRECT */ USE AdventureWorks2022; GO SELECT ProductModelID, Name FROM Production.ProductModel WHERE ProductModelID NOT IN (3, 4) ORDER BY Name UNION SELECT ProductModelID, Name FROM dbo.Gloves; GO /* CORRECT */ USE AdventureWorks2022; GO SELECT ProductModelID, Name FROM Production.ProductModel WHERE ProductModelID NOT IN (3, 4) UNION SELECT ProductModelID, Name FROM dbo.Gloves ORDER BY Name; GO 

S. Использование UNION трех инструкций SELECT для отображения эффектов ALL и круглых скобок

В следующих примерах используются UNION для объединения результатов трех таблиц, которые имеют одинаковые пять строк данных. В первом примере используется предложение UNION ALL , в результате чего выдаются все 15 строк. Второй пример используется без ALL исключения повторяющихся UNION строк из объединенных результатов трех SELECT операторов и возвращает пять строк.

В третьем примере с первым предложением ALL используется ключевое слово UNION , а во втором предложении UNION вместо ключевого слова ALL используются скобки. Второй UNION обрабатывается сначала, так как он находится в скобках, и возвращает пять строк, так как ALL параметр не используется и дубликаты удаляются. Эти пять строк объединяются с результатами первого SELECT с помощью UNION ALL ключевое слово. В данном случае повторяющиеся строки двух множеств, состоящих из пяти строк, не удаляются. Окончательный результат состоит из 10 строк.

USE AdventureWorks2022; GO IF OBJECT_ID('dbo.EmployeeOne', 'U') IS NOT NULL DROP TABLE dbo.EmployeeOne; GO IF OBJECT_ID('dbo.EmployeeTwo', 'U') IS NOT NULL DROP TABLE dbo.EmployeeTwo; GO IF OBJECT_ID('dbo.EmployeeThree', 'U') IS NOT NULL DROP TABLE dbo.EmployeeThree; GO SELECT pp.LastName, pp.FirstName, e.JobTitle INTO dbo.EmployeeOne FROM Person.Person AS pp INNER JOIN HumanResources.Employee AS e ON e.BusinessEntityID = pp.BusinessEntityID WHERE LastName = 'Johnson'; GO SELECT pp.LastName, pp.FirstName, e.JobTitle INTO dbo.EmployeeTwo FROM Person.Person AS pp INNER JOIN HumanResources.Employee AS e ON e.BusinessEntityID = pp.BusinessEntityID WHERE LastName = 'Johnson'; GO SELECT pp.LastName, pp.FirstName, e.JobTitle INTO dbo.EmployeeThree FROM Person.Person AS pp INNER JOIN HumanResources.Employee AS e ON e.BusinessEntityID = pp.BusinessEntityID WHERE LastName = 'Johnson'; GO -- Union ALL SELECT LastName, FirstName, JobTitle FROM dbo.EmployeeOne UNION ALL SELECT LastName, FirstName, JobTitle FROM dbo.EmployeeTwo UNION ALL SELECT LastName, FirstName, JobTitle FROM dbo.EmployeeThree; GO SELECT LastName, FirstName, JobTitle FROM dbo.EmployeeOne UNION SELECT LastName, FirstName, JobTitle FROM dbo.EmployeeTwo UNION SELECT LastName, FirstName, JobTitle FROM dbo.EmployeeThree; GO SELECT LastName, FirstName, JobTitle FROM dbo.EmployeeOne UNION ALL ( SELECT LastName, FirstName, JobTitle FROM dbo.EmployeeTwo UNION SELECT LastName, FirstName, JobTitle FROM dbo.EmployeeThree ); GO 

Связанный контент

  • CREATE TRIGGER (Transact-SQL)
  • CREATE VIEW (Transact-SQL)
  • DELETE (Transact-SQL)
  • EXECUTE (Transact-SQL)
  • Выражения (Transact-SQL)
  • INSERT (Transact-SQL)
  • LIKE (Transact-SQL)
  • Операторы set — UNION (Transact-SQL)
  • Операторы set — EXCEPT и INTERSECT (Transact-SQL)
  • UPDATE (Transact-SQL)
  • WHERE (Transact-SQL)
  • PathName (Transact-SQL)
  • SELECT — предложение INTO (Transact-SQL)

Обратная связь

Были ли сведения на этой странице полезными?

Инструкция SELECT: расширенные возможности

Следующие подразделы описывают другие предложения оператора SELECT, которые могут быть использованы в запросах, а также агрегатные функции и наборы операторов. Напомню, к данному моменту мы рассмотрели использование предложения WHERE, а в этой статье мы рассмотрим предложения GROUP BY, ORDER BY и HAVING, и предоставим некоторые примеры использования этих предложений в сочетании с агрегатными функциями, которые поддерживаются в Transact-SQL.

Предложение GROUP BY

Предложение GROUP BY группирует выбранный набор строк для получения набора сводных строк по значениям одного или нескольких столбцов или выражений. Простой случай применения предложения GROUP BY показан в примере ниже:

USE SampleDb; SELECT Job FROM Works_On GROUP BY Job;

В этом примере происходит выборка и группирование должностей сотрудников. Результат выполнения этого запроса:

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

В примере выше предложение GROUP BY создает отдельную группу для всех возможных значений (включая значение NULL) столбца Job.

Использование столбцов в предложении GROUP BY должно отвечать определенным условиям. В частности, каждый столбец в списке выборки запроса также должен присутствовать в предложении GROUP BY. Это требование не распространяется на константы и столбцы, являющиеся частью агрегатной функции. (Агрегатные функции рассматриваются в следующем подразделе.) Это имеет смысл, т.к. только для столбцов в предложении GROUP BY гарантируется одно значение для каждой группы.

Таблицу можно сгруппировать по любой комбинации ее столбцов. В примере ниже демонстрируется группирование строк таблицы Works_on по двум столбцам:

USE SampleDb; SELECT ProjectNumber, Job FROM Works_On GROUP BY ProjectNumber, Job;

Результат выполнения этого запроса:

Группирование сотрудников по номеру проекта и должности

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

Агрегатные функции

Агрегатные функции используются для получения суммарных значений. Все агрегатные функции можно разделить на следующие категории:

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

Здесь мы рассмотрим первые три типа агрегатных функций.

Обычные агрегатные функции

Язык Transact-SQL поддерживает следующие шесть агрегатных функций: MIN, MAX, SUM, AVG, COUNT, COUNT_BIG.

Все агрегатные функции выполняют вычисления над одним аргументом, который может быть или столбцом, или выражением. (Единственным исключением является вторая форма двух функций: COUNT и COUNT_BIG, а именно COUNT(*) и COUNT_BIG(*) соответственно.) Результатом вычислений любой агрегатной функции является константное значение, отображаемое в отдельном столбце результата.

Агрегатные функции указываются в списке столбцов инструкции SELECT, который также может содержать предложение GROUP BY. Если в инструкции SELECT отсутствует предложение GROUP BY, а список столбцов выборки содержит, по крайней мере, одну агрегатную функцию, тогда он не должен содержать простых столбцов (кроме как столбцов, служащих аргументами агрегатной функции). Поэтому код в примере ниже неправильный:

USE SampleDb; SELECT LastName, MIN(Id) FROM Employee;

Здесь столбец LastName таблицы Employee не должен быть в списке выборки столбцов, поскольку он не является аргументом агрегатной функции. С другой стороны, список выборки столбцов может содержать имена столбцов, которые не являются аргументами агрегатной функции, если эти столбцы служат аргументами предложения GROUP BY.

Аргументу агрегатной функции может предшествовать одно из двух возможных ключевых слов:

ALL

Указывает, что вычисления выполняются над всеми значениями столбца. Это значение по умолчанию.

DISTINCT

Указывает, что для вычислений применяются только уникальные значения столбца.

Агрегатные функции MIN и MAX

Агрегатные функции MIN и MAX вычисляют наименьшее и наибольшее значение столбца соответственно. Если запрос содержит предложение WHERE, функции MIN и MAX возвращают наименьшее и наибольшее значение строк, отвечающих указанным условиям. В примере ниже показано использование агрегатной функции MIN:

USE SampleDb; -- Вернет 2581 SELECT MIN(Id) AS 'Минимальное значение Id' FROM Employee;

Возвращенный в примере выше результат не очень информативный. Например, неизвестна фамилия сотрудника, которому принадлежит этот номер. Но получить эту фамилию обычным способом невозможно, потому что, как упоминалось ранее, явно указать столбец LastName не разрешается. Для того чтобы вместе с наименьшим табельным номером сотрудника также получить и фамилию этого сотрудника, используется подзапрос. В примере ниже показано использование такого подзапроса, где вложенный запрос содержит инструкцию SELECT из предыдущего примера:

USE SampleDb; SELECT Id, LastName FROM Employee WHERE MIN(Id) FROM Employee);

Результат выполнения запроса:

Использование агрегатной функции MIN в подзапросе

Использование агрегатной функции MAX показано в примере ниже:

USE SampleDb; -- 29346 SELECT Id, LastName FROM Employee WHERE MAX(Id) FROM Employee);

В качестве аргумента функции MIN и MAX также могут принимать строки и даты. В случае строкового аргумента значения сравниваются, используя фактический порядок сортировки. Для всех аргументов временных данных типа "дата" наименьшим значением столбца будет наиболее ранняя дата, а наибольшим - наиболее поздняя.

С функциями MIN и MAX можно применять ключевое слово DISTINCT. Перед применением агрегатных функций MIN и MAX из столбцов их аргументов исключаются все значения NULL.

Агрегатная функция SUM

Агрегатная функция SUM вычисляет общую сумму значений столбца. Аргумент этой агрегатной функции всегда должен иметь числовой тип данных. Использование агрегатной функции SUM показано в примере ниже:

USE SampleDb; SELECT SUM (Budget) 'Суммарный бюджет' FROM Project;

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

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

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

USE SampleDb; SELECT SUM (Budget) 'Суммарный бюджет' FROM Project GROUP BY();

Рекомендуется использовать этот синтаксис в предложении GROUP BY, поскольку таким образом группирование определяется явно.

Использование параметра DISTINCT устраняет все повторяющиеся значения в столбце перед применением функции SUM. Аналогично удаляются все значения NULL перед применением этой агрегатной функции.

Агрегатная функция AVG

Агрегатная функция AVG возвращает среднее арифметическое значение для всех значений столбца. Аргумент этой агрегатной функции всегда должен иметь числовой тип данных. Перед применением функции AVG все значения NULL удаляются из ее аргумента.

Использование агрегатной функции AVG показано в примере ниже:

USE SampleDb; -- Вернет 133833 SELECT AVG (Budget) 'Средний бюджет на проект' FROM Project;

Здесь происходит вычисление среднего арифметического значения бюджета для всех бюджетов.

Агрегатные функции COUNT и COUNT_BIG

Агрегатная функция COUNT имеет две разные формы:

COUNT([DISTINCT] col_name) COUNT(*) 

Первая форма функции подсчитывает количество значений в столбце col_name. Если в запросе используется ключевое слово DISTINCT, перед применением функции COUNT удаляются все повторяющиеся значения столбца. При подсчете количества значений столбца эта форма функции COUNT не принимает во внимание значения NULL.

Использование первой формы агрегатной функции COUNT показано в примере ниже:

USE SampleDb; SELECT ProjectNumber, COUNT(DISTINCT Job) 'Работ в проекте' FROM Works_on GROUP BY ProjectNumber;

Здесь происходит подсчет количества разных должностей для каждого проекта. Результат выполнения этого запроса:

Результат использования функции COUNT

Как можно видеть в результате выполнения запроса, представленного в примере, значения NULL функцией COUNT не принимались во внимание. (Сумма всех значений столбца должностей получилась равной 7, а не 11, как должно быть.)

Вторая форма функции COUNT, т.е. функция COUNT(*) подсчитывает количество строк в таблице. А если инструкция SELECT запроса с функцией COUNT(*) содержит предложение WHERE с условием, функция возвращает количество строк, удовлетворяющих указанному условию. В отличие от первого варианта функции COUNT вторая форма не игнорирует значения NULL, поскольку эта функция оперирует строками, а не столбцами. В примере ниже демонстрируется использование функции COUNT(*):

USE SampleDb; SELECT Job AS 'Тип работ', COUNT(*) 'Нужно работников' FROM Works_on GROUP BY Job;

Здесь происходит подсчет количества должностей во всех проектах. Результат выполнения запроса:

Использование второй формы функции COUNT

Функция COUNT_BIG аналогична функции COUNT. Единственное различие между ними заключается в типе возвращаемого ими результата: функция COUNT_BIG всегда возвращает значения типа BIGINT, тогда как функция COUNT возвращает значения данных типа INTEGER.

Статистические агрегатные функции

Следующие функции составляют группу статистических агрегатных функций:

VAR

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

VARP

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

STDEV

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

STDEVP

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

Агрегатные функции, определяемые пользователем

Компонент Database Engine также поддерживает реализацию функций, определяемых пользователем. Эта возможность позволяет пользователям дополнить системные агрегатные функции функциями, которые они могут реализовывать и устанавливать самостоятельно. Эти функции представляют специальный класс определяемых пользователем функций и подробно рассматриваются позже.

Предложение HAVING

В предложении HAVING определяется условие, которое применяется к группе строк. Таким образом, это предложение имеет такой же смысл для групп строк, что и предложение WHERE для содержимого соответствующей таблицы. Синтаксис предложения HAVING следующий:

HAVING condition 

Здесь параметр condition представляет условие и содержит агрегатные функции или константы.

Использование предложения HAVING совместно с агрегатной функцией COUNT(*) показано в примере ниже:

USE SampleDb; -- Вернет 'p3' SELECT ProjectNumber FROM Works_on GROUP BY ProjectNumber HAVING COUNT(*) < 4;

В этом примере посредством предложения GROUP BY система группирует все строки по значениям столбца ProjectNumber. После этого подсчитывается количество строк в каждой группе и выбираются группы, содержащие менее четырех строк (три или меньше).

Предложение HAVING можно также использовать без агрегатных функций, как это показано в примере ниже:

USE SampleDb; -- Вернет 'Консультант' SELECT Job FROM Works_on GROUP BY Job HAVING Job LIKE 'К%';

В этом примере происходит группирование строк таблицы Works_on по должности и устранение тех должностей, которые не начинаются с буквы "К".

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

Предложение ORDER BY

Предложение ORDER BY определяет порядок сортировки строк результирующего набора, возвращаемого запросом. Это предложение имеет следующий синтаксис:

Порядок сортировки задается в параметре col_name. Параметр col_number является альтернативным указателем порядка сортировки, который определяет столбцы по порядку их вхождения в список выборки инструкции SELECT (1 - первый столбец, 2 - второй столбец и т.д.). Параметр ASC определяет сортировку в восходящем порядке, а параметр DESC - в нисходящем. По умолчанию применяется параметр ASC.

Имена столбцов в предложении ORDER BY не обязательно должны быть указаны в списке столбцов выборки. Но это не относится к запросам типа SELECT DISTINCT, т.к. в таких запросах имена столбцов, указанные в предложении ORDER BY, также должны быть указаны в списке столбцов выборки. Кроме этого, это предложение не может содержать имен столбцов из таблиц, не указанных в предложении FROM.

Как можно видеть по синтаксису предложения ORDER BY, сортировка результирующего набора может выполняться по нескольким столбцам. Такая сортировка показана в примере ниже:

USE SampleDb; SELECT * FROM Employee WHERE Id < 20000 ORDER BY LastName, FirstName;

В этом примере происходит выборка номеров отделов и фамилий и имен сотрудников для сотрудников, чей табельный номер меньше чем 20 000, а также с сортировкой по фамилии и имени. Результат выполнения этого запроса:

Сортировка с помощью предложения ORDER BY

Столбцы в предложении ORDER BY можно указывать не по их именам, а по порядку в списке выборки. Соответственно, предложение в примере выше можно переписать таким образом:

USE SampleDb; SELECT * FROM Employee WHERE Id < 20000 ORDER BY 3, 2;

Такой альтернативный способ указания столбцов по их позиции вместо имен применяется, если критерий упорядочивания содержит агрегатную функцию. (Другим способом является использование наименований столбцов, которые тогда отображаются в предложении ORDER BY.) Однако в предложении ORDER BY рекомендуется указывать столбцы по их именам, а не по номерам, чтобы упростить обновление запроса, если в списке выборки придется добавить или удалить столбцы. Указание столбцов в предложении ORDER BY по их номерам показано в примере ниже:

USE SampleDb; SELECT ProjectNumber, COUNT(*) 'Количество сотрудников' FROM Works_on GROUP BY ProjectNumber ORDER BY 2 DESC;

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

Язык Transact-SQL при сортировке в возрастающем порядке помещает значения NULL в начале списка, и в конце списка - при убывающем.

Использование предложения ORDER BY для разбиения результатов на страницы

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

Для поддержки создания страниц на стороне сервера в SQL Server 2012 вводится два новых предложения инструкции SELECT: OFFSET и FETCH. Применение этих двух предложений демонстрируется в примере ниже. Здесь из базы данных AdventureWorks2012 (которую вы можете найти в исходниках) извлекается идентификатор бизнеса, название должности и день рождения всех сотрудников женского пола с сортировкой результата по названию должности в возрастающем порядке. Результирующий набор строк разбивается на 10-строчные страницы и отображается третья страница:

USE AdventureWorks2012; SELECT BusinessEntityID, JobTitle, BirthDate FROM HumanResources.Employee WHERE Gender = 'F' ORDER BY JobTitle OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

В предложении OFFSET указывается количество строк результата, которые нужно пропустить в отображаемом результате. Это количество вычисляется после сортировки строк предложением ORDER BY. В предложении FETCH NEXT указывается количество удовлетворяющих условию WHERE и отсортированных строк, которое нужно возвратить. Параметром этого предложения может быть константа, выражение или результат другого запроса. Предложение FETCH NEXT аналогично предложению FETCH FIRST.

Основной целью при создании страниц на стороне сервера является возможность реализация общих страничных форм, используя переменные. Эту задачу можно выполнить посредством пакета SQL Server.

Инструкция SELECT и свойство IDENTITY

Свойство IDENTITY позволяет определить значения для конкретного столбца таблицы в виде автоматически возрастающего счетчика. Это свойство могут иметь столбцы численного типа данных, такого как TINYINT, SMALLINT, INT и BIGINT. Для такого столбца таблицы компонент Database Engine автоматически создает последовательные значения, начиная с указанного стартового значения. Таким образом, свойство IDENTITY можно использовать для создания однозначных числовых значений для выбранного столбца.

Таблица может содержать только один столбец со свойством IDENTITY. Владелец таблицы имеет возможность указать начальное значение и шаг приращения, как это показано в примере ниже:

USE SampleDb; CREATE TABLE Product ( Id INT IDENTITY(10000, 1) NOT NULL, Name NVARCHAR(30) NOT NULL, Price MONEY ) INSERT INTO Product(Name, Price) VALUES ('Товар1', 10), ('Товар2', 15), ('Товар3', 8), ('Товар4', 15), ('Товар5', 40); -- Вернет 10004 SELECT IDENTITYCOL FROM Product WHERE Name = 'Товар5'; -- Аналог предыдущей инструкции SELECT $identity FROM Product WHERE Name = 'Товар5';

В этом примере сначала создается таблица Product, содержащая столбец Id со свойством IDENTITY. Значения в столбце Id создаются автоматически системой, начиная с 10 000 и увеличиваясь с единичным шагом для каждого последующего значения: 10 000, 10 001, 10 002 и т.д.

Со свойством IDENTITY связаны некоторые системные функции и переменные. Например, в коде примера используется системная переменная $identity. Как можно видеть по результатам выполнения этого кода, эта переменная автоматически ссылается на свойство IDENTITY. Вместо нее можно также использовать системную функцию IDENTITYCOL.

Начальное значение и шаг приращения столбца со свойством IDENTITY можно узнать с помощью функций IDENT_SEED и IDENT_INCR соответственно. Применяются эти функции следующим образом:

USE SampleDb; SELECT IDENT_SEED('Product'), IDENT_INCR('Product')

Как уже упоминалось, значения IDENTITY устанавливаются автоматически системой. Но пользователь может указать явно свои значения для определенных строк, присвоив параметру IDENTITY_INSERT значение ON перед вставкой явного значения:

SET IDENTITY INSERT table name ON 

Поскольку с помощью параметра IDENTITY_INSERT для столбца со свойством IDENTITY можно установить любое значение, в том числе и повторяющееся, свойство IDENTITY обычно не обеспечивает принудительную уникальность значений столбца. Поэтому для принудительного обеспечения уникальности значений столбца следует применять ограничения UNIQUE или PRIMARY KEY.

При вставке значений в таблицу после присвоения параметру IDENTITY_INSERT значения on система создает следующее значение столбца IDENTITY, увеличивая наибольшее текущее значение этого столбца.

Оператор CREATE SEQUENCE

Применение свойства IDENTITY имеет несколько значительных недостатков, наиболее существенными из которых являются следующие:

  • применение свойства ограничивается указанной таблицей;
  • новое значение столбца нельзя получить иным способом, кроме как применив его;
  • свойство IDENTITY можно указать только при создании столбца.

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

Последовательности создаются с помощью инструкции CREATE SEQUENCE. Инструкция CREATE SEQUENCE определена в стандарте SQL и поддерживается другими реляционными системами баз данных, такими как IBM DB2 и Oracle.

В пример ниже показано создание последовательности в SQL Server:

USE SampleDb; CREATE SEQUENCE dbo.Sequence1 AS INT START WITH 1 INCREMENT BY 5 MINVALUE 1 MAXVALUE 256 CYCLE;

В примере выше значения последовательности Sequence1 создаются автоматически системой, начиная со значения 1 с шагом 5 для каждого последующего значения. Таким образом, в предложении START указывается начальное значение, а в предложении INCREMENT - шаг. (Шаг может быть как положительным, так и отрицательным.)

В следующих двух, необязательных, предложениях MINVALUE и MAXVALUE указываются минимальное и максимальное значение объекта последовательности. (Обратите внимание, что значение MINVALUE должно быть меньшим или равным начальному значению, а значение MAXVALUE не может быть большим, чем верхний предел типа данных, указанных для последовательности.) В предложении CYCLE указывается, что последовательность повторяется с начала по превышению максимального (или минимального для последовательности с отрицательным шагом) значения. По умолчанию это предложение имеет значение NO CYCLE, что означает, что превышение максимального или минимального значения последовательности вызывает исключение.

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

Новые значения последовательности создаются с помощью выражения NEXT VALUE FOR, применение которого показано в примере ниже:

USE SampleDb; -- Вернет 1 SELECT NEXT VALUE FOR dbo.sequence1; -- Вернет 6 (следующий шаг) SELECT NEXT VALUE FOR dbo.sequence1;

С помощью выражения NEXT VALUE FOR можно присвоить результат последовательности переменной или ячейке столбца. В примере ниже показано использование этого выражения для присвоения результатов столбцу:

USE SampleDb; CREATE TABLE Product ( Id INT NOT NULL, Name NVARCHAR(30) NOT NULL, Price MONEY ) INSERT INTO Product VALUES (NEXT VALUE FOR dbo.sequence1, 'Товар1', 10); INSERT INTO Product VALUES (NEXT VALUE FOR dbo.sequence1, 'Товар2', 15); -- . 

В примере выше сначала создается таблица Product, состоящая из четырех столбцов. Далее, две инструкции INSERT вставляют в эту таблицу две строки. Первые две ячейки первого столбца будут иметь значения 11 и 16.

В примере ниже показано использование представления каталога sys.sequences для просмотра текущего значения последовательности, не используя его:

USE SampleDb; SELECT current_value FROM sys.sequences WHERE name = 'Sequence1';

Обычно выражение NEXT VALUE FOR применяется в инструкции INSERT, чтобы система вставляла созданные значения. Это выражение также можно использовать, как часть многострочного запроса с помощью предложения OVER.

Для изменения свойства существующей последовательности применяется инструкция ALTER SEQUENCE. Одно из наиболее важных применений этой инструкции связано с параметром RESTART WITH, который переустанавливает указанную последовательность. В примере ниже показано использование инструкции ALTER SEQUENCE для переустановки почти всех свойств последовательности Sequence1:

USE SampleDb; ALTER SEQUENCE dbo.sequence1 RESTART WITH 100 INCREMENT BY 50 MINVALUE 50 MAXVALUE 200 NO CYCLE;

Удаляется последовательность с помощью инструкции DROP SEQUENCE.

Операторы работы с наборами

Кроме операторов, рассмотренных ранее, язык Transact-SQL поддерживает еще три оператора работы с наборами: UNION, INTERSECT и EXCEPT.

Оператор UNION

Оператор UNION объединяет результаты двух или более запросов в один результирующий набор, в который входят все строки, принадлежащие всем запросам в объединении. Соответственно, результатом объединения двух таблиц является новая таблица, содержащая все строки, входящие в одну из исходных таблиц или в обе эти таблицы.

Общая форма оператора UNION выглядит таким образом:

select_1 UNION [ALL] select_2 <[UNION [ALL] select_3]>. 

Параметры select_1, select_2, . представляют собой инструкции SELECT, которые создают объединение. Если используется параметр ALL, отображаются все строки, включая дубликаты. В операторе UNION параметр ALL имеет то же самое значение, что и в списке выбора SELECT, но с одним отличием: для списка выбора SELECT этот параметр применяется по умолчанию, а для оператора UNION его нужно указывать явно.

В своей исходной форме база данных SampleDb не подходит для демонстрации применения оператора UNION. Поэтому в этом разделе создается новая таблица EmployeeEnh, которая идентична существующей таблице Employee, но имеет дополнительный столбец City. В этом столбце указывается место жительства сотрудников.

Создание таблицы EmployeeEnh предоставляет нам удобный случай продемонстрировать использование предложения INTO в инструкции SELECT. Инструкция SELECT INTO выполняет две операции. Сначала создается новая таблица со столбцами, перечисленными в списке выбора SELECT. Потом строки исходной таблицы вставляются в новую таблицу. Имя новой таблицы указывается в предложении INTO, а имя таблицы-источника указывается в предложении FROM.

В примере ниже показано создание таблицы EmployeeEnh из таблицы Employee:

USE SampleDb; SELECT * INTO EmployeeEnh FROM Employee; ALTER TABLE EmployeeEnh ADD City NCHAR(40) NULL;

В этом примере инструкция SELECT INTO создает таблицу EmployeeEnh, вставляет в нее все строки из таблицы-источника Employee, после чего инструкция ALTER TABLE добавляет в новую таблицу столбец City. Но добавленный столбец City не содержит никаких значений. Значения в этот столбец можно вставить посредством среды Management Studio или же с помощью следующего кода:

USE SampleDb; UPDATE EmployeeEnh SET City = 'Казань' WHERE EmployeeEnh SET City = 'Москва' WHERE EmployeeEnh SET City = 'Екатеринбург' WHERE EmployeeEnh SET City = 'Санкт-Петербург' WHERE EmployeeEnh SET City = 'Краснодар' WHERE EmployeeEnh SET City = 'Казань' WHERE EmployeeEnh SET City = 'Пермь' WHERE >Теперь мы готовы продемонстрировать использование инструкции UNION. В примере ниже показан запрос для создания соединения таблиц EmployeeEnh и Department, используя эту инструкцию:
USE SampleDb; SELECT City AS 'Город' FROM EmployeeEnh UNION SELECT Location FROM Department;

Результат выполнения этого запроса:

Объединение двух таблиц с помощью оператора UNION

Объединять с помощью инструкции UNION можно только совместимые таблицы. Под совместимыми таблицами имеется в виду, что оба списка столбцов выборки должны содержать одинаковое число столбцов, а соответствующие столбцы должны иметь совместимые типы данных. (В отношении совместимости типы данных INT и SMALLINT не являются совместимыми.)

Результат объединения можно упорядочить, только используя предложение ORDER BY в последней инструкции SELECT, как это показано в примере ниже. Предложения GROUP BY и HAVING можно применять с отдельными инструкциями SELECT, но не в самом объединении.

USE SampleDb; -- Вернет 18316, 28559 SELECT Id FROM Employee WHERE DepartamentNumber = 'd1' UNION SELECT EmpId FROM Works_on WHERE EnterDate > '01.01.2008' ORDER BY 1;

Запрос в этом примере осуществляет выборку сотрудников, которые или работают в отделе d1, или начали работать над проектом до 1 января 2008 г.

Оператор UNION поддерживает параметр ALL. При использовании этого параметра дубликаты не удаляются из результирующего набора. Вместо оператора UNION можно применить оператор OR, если все инструкции SELECT, соединенные одним или несколькими операторами UNION, ссылаются на одну и ту же таблицу. В таком случае набор инструкций SELECT заменяется одной инструкцией SELECT с набором операторов OR.

Операторы INTERSECT и EXCEPT

Два других оператора для работы с наборами, INTERSECT и EXCEPT, определяют пересечение и разность соответственно. Под пересечением в данном контексте имеется набор строк, которые принадлежат к обеим таблицам. А разность двух таблиц определяется как все значения, которые принадлежат к первой таблице и не присутствуют во второй. В примере ниже показано использование оператора INTERSECT:

USE SampleDb; -- Вернет только 28559 SELECT Id FROM Employee WHERE DepartamentNumber = 'd1' INTERSECT SELECT EmpId FROM Works_on WHERE EnterDate > '01.01.2008';

Язык Transact-SQL не поддерживает использование параметра ALL ни с оператором INTERSECT, ни с оператором EXCEPT. Использование оператора EXCEPT показано в примере ниже:

USE SampleDb; -- Вернет 10102, 25348 SELECT Id FROM Employee WHERE DepartamentNumber = 'd3' EXCEPT SELECT EmpId FROM Works_on WHERE EnterDate > '01.01.2008';

Следует помнить, что эти три оператора над множествами имеют разный приоритет выполнения: оператор INTERSECT имеет наивысший приоритет, за ним следует оператор EXCEPT, а оператор UNION имеет самый низкий приоритет. Невнимательность к приоритету выполнения при использовании нескольких разных операторов для работы с наборами может повлечь неожиданные результаты.

Выражения CASE

В области прикладного программирования баз данных иногда требуется модифицировать представление данных. Например, людей можно подразделить, закодировав их по их социальной принадлежности, используя значения 1, 2 и 3, обозначив так мужчин, женщин и детей соответственно. Такой прием программирования может уменьшить время, необходимое для реализации программы. Выражение CASE языка Transact-SQL позволяет с легкостью реализовать такой тип кодировки.

В отличие от большинства языков программирования, CASE не является инструкцией, а выражением. Поэтому выражение CASE можно использовать почти везде, где язык Transact-SQL позволяет применять выражения. Выражение CASE имеет две формы:

  • простое выражение CASE;
  • поисковое выражение CASE.

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

Инструкция с простым выражением CASE сначала ищет в списке всех выражений в предложении WHEN первое выражение, совпадающее с выражением expression_1, после чего выполняет соответствующее предложение THEN. В случае отсутствия в списке WHEN совпадающего выражения, выполняется предложение ELSE.

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

В данном случае выполняется поиск первого отвечающего требованиям условия, после чего выполняется соответствующее предложение THEN. Если ни одно из условий не отвечает требованиям, выполняется предложение ELSE. Применение поискового выражения CASE показано в примере ниже:

USE SampleDb; SELECT ProjectName, CASE WHEN Budget > 0 AND Budget 100000 AND Budget 150000 AND Budget 

Результат выполнения этого запроса:

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

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

В примере ниже показан другой способ применения выражения CASE, где предложение WHEN содержит вложенные запросы, составляющие часть выражения:

USE SampleDb; SELECT ProjectName, CASE WHEN p1.Budget < (SELECT AVG(p2.Budget) FROM Project p2) THEN 'ниже среднего' WHEN p1.Budget <=(SELECT AVG(p2.Budget) FROM Project p2) THEN 'равен среднему' WHEN p1.Budget >(SELECT AVG(p2.Budget) FROM Project p2) THEN 'выше среднего' END 'Категория бюджета' FROM Project p1;

Результат выполнения этого запроса следующий:

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

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