Главная » Microsoft SQL Server, Базы данных » Простые подзапросы

0

Порядок выполнения простых подзапросов приведен ниже.

1.              Простой подзапрос выполняется один раз.

1.              Результаты передаются внешнему запросу.

2.              Внешний запрос выполняется один раз.

Самый простой подзапрос возвращает одно (скалярное) значение, которое затем используется как выражение во внешнем подзапросе, например:

SELECT (SELECT 3) AS SubqueryValue Результат будет следующим:

SubqueryValue

3

Подзапрос (select 3) возвращает скалярное значение 3, которое передается внешней инструкции SELECT. После этого выполняется внешняя инструкция SELECT; при этом она уже имеет следующий вид:

SELECT 3 AS SubqueryValue

Естественно, подзапросы с запрограммированным значением не имеют никакой ценности. Реальные подзапросы извлекают данные из таблицы, например:

USE OBXKites SELECT ProductName FROM dbo.Product WHERE ProductCategorylD

= (Select ProductCategorylD

FROM dbo.ProductCategory

Where ProductCategoryName = ‘Kite’)

Выполняя этот запрос, SQL Server вычисляет результаты подзапроса, а затем передает полученное значение внешнему запросу (полученный вами уникальный идентификатор будет отличаться от приведенного в примере):

Select ProductCategorylD

FROM dbo.ProductCategory

Where ProductCategoryName = ‘Kite’

Результат будет следующим:

ProductCategorylD

C3 8D8113-2BED-4E2B-9ABF-A589E0818 06 9

После получения результата подзапроса внешний запрос будет иметь следующий вид:

SELECT ProductName FROM dbo.Product WHERE ProductCategorylD

= 1C3 8D8113-2BED-4E2B-9ABF-A58 9E0818 06 9′

Результат внешнего запроса:

ProductName

Basic Box Kite 21 inch Dragon Flight Sky Dancer Rocket Kite

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

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

Общие табличные выражения

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

Общие табличные выражения используют предложение WITH, которое определяет их. В предложении WITH находятся имя, псевдонимы столбцов и код SQL подзапроса СТЕ. После этого основной запрос может обратиться к СТЕ как к источнику данных:

WITH CTEName (параметры)

AS (простой_подзапрос)

SELECT…

FROM CTEName

Следующий код идентичен приведенному в предыдущем примере, только он имеет формат СТЕ. Именем табличного выражения является CTEQuery; оно возвращает столбец ProductionCategoryID и использует точно такую же инструкцию SELECT, как и подзапрос, приведенный в предыдущем примере:

WITH CTEQuery (ProductCategorylD)

AS (Select ProductCategorylD

FROM dbo.ProductCategory

Where ProductCategoryName = ’Kite’)

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

WITH CTEQuery (ProductCategorylD)

AS (Select ProductCategorylD

FROM dbo.ProductCategory

Where ProductCategoryName = ‘Kite’)

SELECT ProductName FROM dbo.Product WHERE ProductCategorylD

= (SELECT ProductCategorylD FROM CTEQuery)

Чтобы включить в один запрос множество СТЕ, их нужно последовательно определить перед основным запросом:

WITH CTElName (параметры)

AS (простой подзапрос)

WITH CTE2Name {параметры)

AS (простой подзапрос)

SELECT…

FROM CTElName JOIN CTE2Name ON . . .

Несмотря на то что СТЕ сами могут содержать сложные запросы, они имеют два ключевых ограничения.

?               В отличие от подзапросов они не могут быть вложены друг в друга.

?               Они не могут ссылаться на главный запрос. Подобно простым подзапросам, они должны быть состоятельны сами по себе.

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

За все время тестирования я не заметил разницы в производительности между простым подзапросом и СТЕ, — они оба компилируются в один и тот же план выполнения.

Использование скалярных подзапросов

Если подзапрос возвращает одно значение, то он может использоваться в любом месте инструкции SELECT, где может использоваться выражение, в частности в выражениях столбцов, а также в условиях JOIN, WHERE и HAVING. Обычные операторы (такие, как +, -, between и т.п.) будут работать со скалярным значением, возвращаемым подзапросом, однако при этом могут потребоваться функции преобразования типов, такие как cast () или convert ().

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

SELECT ProductCategoryName,

SUM(Quantity * UnitPrice) AS Sales,

Cast(SUM(Quantity * UnitPrice) /

(SELECT SUM(Quantity * UnitPrice)

FROM dbo.OrderDetail) *100 AS INT)

AS PercentOfSales FROM dbo.OrderDetail JOIN dbo.Product

ON OrderDetail.ProductID = Product.ProductID JOIN dbo.ProductCategory

ON Product.ProductCategorylD = ProductCategory.ProductCategorylD GROUP BY ProductCategoryName ORDER BY Count(*) DESC

Подзапрос SELECT SUM (Quantity * UnitPrice) FROM dbo. OrderDetail возвращает значение 172 9,8 95, которое затем передается столбцу PercentageOfSales внешнего запроса.

ProductCategoryName Sales                           PercentOfSales

Kite                            1499.902500               86.70

OBX                          64.687500                      3.74

Clothing                     113.600000                    6.57

Accessory                  10.530000                      0.61

Material                     5.265000                        0.30

Video                         35.910000                      2.08

Следующая инструкция SELECT извлекается из пользовательской функции fGetPrice () учебной базы данных OBXKites. Эта база данных имеет таблицу цен для конкретных дат, при

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

Функция fGetPriceO возвращает корректную цену для любого продукта на заданную дату с учетом любой скидки. Для достижения этой цели функция должна определить действующую цену для любой заданной даты. Например, если пользователю нужна цена на 16 июля 2002 года, а текущая цена вступила в силу 1 июля того же года, запросу нужно узнать ближайшую дату установки цены, используя выражение max (@orderdate). Как только подзапрос определит эту дату, внешний запрос извлечет нужную цену. С целью демонстрации примера некоторые переменные этой функции были заменены статическими значениями: SELECT @CurrPrice = Price * (l-@DiscountPercent)

FROM dbo.Price JOIN dbo.Product

ON Price.ProductID = Product.ProductID

WHERE ProductCode = 4001′

AND EffectiveDate =

(SELECT MAX(EffectiveDate)

FROM dbo.Price

JOIN dbo.Product

ON Price.ProductID = Product.ProductID WHERE ProductCode = ‘1001’

AND EffectiveDate <= ‘6/1/2001′)

При вызове функции Select dbo.fGetPrice(‘1001′# ‘5/1/2001′,NULL)

подзапрос определяет, что датой вступления в силу цены является ’05/01/2001′. После этого внешний запрос может найти корректную цену, основываясь на идентификаторе товара и дате вступления в силу цены. После того как функция fGetPrice () вычислит скидку, она вернет значение @CurrPrice вызывающей инструкции SELECT.

14,95

Использование подзапросов в качестве списков

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

Оператор IN возвращает истинное значение (true), если значение столбца найдено в списке, возвращенном подзапросом; таким же образом действует выражение WHERE… IN при работе с запрограммированным списком:

SELECT *

FROM dbo.Contact

WHERE HomeRegion IN (‘NC’, ‘SC’, ‘GA’, ‘AL’, ‘VA’)

Подзапрос, возвращающий список, служит механизмом динамического формирования списка для условия WHERE . . .IN:

SELECT *

FROM dbo.Contact

WHERE Region IN {Подзапрос, возвращающий список состояний)

Приведенный в следующем примере запрос дает ответ на вопрос: “Что еще покупают в магазине ОВХ Kites при покупке воздушного змея?” В этом запросе мы будем использовать исключительно подзапросы — никаких объединений. Все эти подзапросы являются простыми, в том смысле, что каждый из них можно выполнить обособленно как запрос.

Этот запрос найдет все заказы, содержащие воздушных змеев, и передаст их идентификаторы главному запросу. В получении ответа на вопрос будут задействованы четыре таблицы: Product Category, Product, OrderDetail и Order. Вложенные подзапросы выполняются от самого внутреннего к внешнему.

1.              Подзапрос находит идентификатор категории для воздушных змеев (Product Category ID).

2.              Подзапрос находит список продуктов, попадающих в категорию воздушных змеев (т.е. с найденным идентификатором).

3.              Подзапрос находит список заказов, содержащих воздушных змеев.

4.              Подзапрос находит список товаров, содержащихся в заказах с воздушными змеями.

5.              Внешний запрос извлекает названия этих товаров.

SELECT ProductName FROM dbo.Product WHERE ProductID IN — 4. Поиск всех товаров, содержащихся в заказах с воздушными змеями (SELECT ProductID

FROM dbo.OrderDetail

WHERE OrderlD IN — 3. Поиск заказов с воздушными змеями (SELECT OrderlD — Find the Orders with Kites FROM dbo.OrderDetail

WHERE ProductID IN — 2. Поиск товаров категории воздушных змеев (SELECT ProductID FROM dbo.Product WHERE ProductCategorylD =

– 1. Поиск категории воздушных змеев (SELECT ProductCategorylD FROM dbo.ProductCategory WHERE ProductCategoryName = ‘Kite’ ) ) ) )

При желании вы можете выделить любой из подзапросов в окне Query и запус- Совет       тить его как обособленный запрос, нажав клавишу <F5>.

Подзапрос 1 находит идентификатор категории змеев (ProductCategorylD) и возвращает одно значение.

Подзапрос 2 использует возвращенное подзапросом 1 значение в предложении WHERE для составления списка товаров, у которых поле ProductCategorylD имеет это значение.

Подзапрос 3 использует подзапрос 2 в качестве списка в предложении WHERE и ищет все строки таблицы OrderDetail, содержащие любой из идентификаторов товаров (ProductID), возвращенных подзапросом 2.

Подзапрос 4 использует подзапрос 3 в качестве списка в предложении WHERE и ищет все строки таблицы OrderDetail, идентификатор заказа (OrderlD) которых содержится в списке, возвращенном запросом 3.

Внешний запрос использует подзапрос 4 в качестве списка в условии предложения WHERE и находит названия всех товаров, идентификаторы которых (ProductID) вернул подзапрос 4. Результат получается следующим:

Falcon F-16

Dragon Flight

OBX Car Bumper Sticker

Short Streamer

Cape Hatteras T-Shirt

Sky Dancer

Go Fly a Kite T-Shirt Long Streamer Rocket Kite OBX T-Shirt

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

?               Идентификатор товара (ProductID) должен находиться в заказе, содержащем список змеев.

?               Идентификатор товара не должен содержаться в списке змеев.

Мы знаем, что подзапрос 2 возвращает список идентификаторов всех воздушных змеев. Если добавить копию этого подзапроса в оператор NOT IN внешнего запроса, из списка будут исключены все воздушные змеи:

SELECT ProductName FROM dbo.Product WHERE ProductID IN — 4. Поиск всех товаров, содержащихся в заказах с воздушными змеями (SELECT ProductID

FROM dbo.OrderDetail

WHERE OrderlD IN — 3. Поиск заказов с воздушными змеями (SELECT OrderlD — Find the Orders with Kites FROM dbo.OrderDetail

WHERE ProductID IN — 2. Поиск товаров категории воздушных змеев (SELECT ProductID FROM dbo.Product WHERE ProductCategorylD =

– 1. Поиск категории воздушных змеев (SELECT ProductCategorylD FROM dbo.ProductCategory WHERE ProductCategoryName = 1 Kite1 ) ) ) )

– продолжаем внешний запрос AND ProductID NOT IN (SELECT ProductID

FROM dbo.Product WHERE ProductCategorylD =

(SELECT ProductCategorylD

FROM dbo.ProductCategory WHERE ProductCategoryName = ‘Kite’))

Результат запроса будет следующим:

ProductName

OBX Car Bumper Sticker Short Streamer

Cape Hatteras T-Shirt Go Fly a Kite T-Shirt Long Streamer OBX T-Shirt

Для сравнения приведем еще один запрос, выполняющий ту же задачу, но написанный с помощью объединений. Таблица Product дважды участвует в ссылках, поэтому вторая ссылка имеет именованный диапазон Kite. Как и в предыдущих подзапросах, первая версия возвращает в результатах весь список товаров, а вторая исключает из него воздушных змеев:

SELECT Distinct Product.ProductName FROM dbo.Product

JOIN dbo.OrderDetail OrderRow

ON Product.ProductID = OrderRow.ProductID JOIN dbo.OrderDetail KiteRow

ON OrderRow.OrderID = KiteRow.OrderID JOIN dbo.Product Kite

ON KiteRow.ProductID = Kite.ProductID JOIN dbo.ProductCategory ON Kite.ProductCategorylD

= ProductCategory.ProductCategorylD WHERE ProductCategoryName = ‘Kite 1

Единственным изменением, которое нужно внести для исключения воздушных змеев, является дополнительное условие в объединении с таблицей ProductCategory. Ранее это было объединение равенства между таблицами Product и ProductCategory. Добавление условия 0-объединения (! =) между таблицами Product и ProductCategory устраняет все товары, попадающие в категорию воздушных змеев, как показано в следующем примере:

SELECT Distinct Product.ProductName FROM dbo.Product

JOIN dbo.OrderDetail OrderRow

ON Product.ProductID = OrderRow.ProductID JOIN dbo.OrderDetail KiteRow

ON OrderRow.OrderID = KiteRow.OrderID JOIN dbo.Product Kite

ON KiteRow.ProductID = Kite.ProductID JOIN dbo.ProductCategory ON Kite.ProductCategorylD

= ProductCategory.ProductCategorylD AND Product.ProductCategorylD !=Kite.ProductCategorylD WHERE ProductCategoryName = ‘Kite’

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

SQL является очень гибким языком — обычно в нем существуют десятки способов задать один и тот же вопрос. Выбор метода в первую очередь зависит от предпочитаемого вами стиля — текст должен быть читаемым и логически корректным. И уже затем рассматривается вопрос быстродействия. Медленные, но правильные запросы всегда лучше быстрых, но некорректных.

Приведем еще один пример, в котором творческий подход к созданию подзапроса помогает справиться с задачей. SQL с легкостью справляется с отбором десятка наибольших или наименьших значений; гораздо сложнее обстоят дела с отбором среднего диапазона. В наши

дни, когда поиск в Web возвращает сотни, если не тысячи ссылок, поиск строк, например со 101-й по 125-ю, может оказаться довольно полезным.

В приведенном ниже примере мы снова будем использовать учебную базу данных OBXKites. Мы отберем пятерку товаров, начиная с 26-го. Подзапрос ищет первые 25 товаров, после чего они пропускаются в основном запросе с помощью предложения WHERE NOT IN:

USE OBXKites

SELECT TOP 5 ProductName, ProductID FROM dbo.Product WHERE ProductID NOT IN

(SELECT TOP 2 5 ProductID FROM dbo.Product ORDER BY ProductID)

ORDER BY ProductID

Получим следующий результат:

ProductName                 ProductCode

Handle                            1026

Third Line Release         1027

High Performance Line 1028 Kite Bag              1029

Kite Repair Kit         1030

Использование подзапросов в качестве таблиц

Так же как представление может быть использовано вместо таблицы в предложении FROM инструкции SELECT, подзапрос в форме управляемой таблицы может заменить таблицу, если он имеет именованный диапазон. Этот метод часто используется для разбиения сложных задач на несколько меньших.

Использование подзапроса в качестве управляемой таблицы — это отличное решение задач консолидации данных. При создании итогового запроса все столбцы должны участвовать в консолидации тем или иным образом либо в предложении GROUP BY, либо в итоговой функции (SUMM (), MIN (), COUNT (), МАХ () или AVERAGE ()). Это соглашение усложняет получение дополнительной информации, в частности описаний. В то же время выполнение итоговых функций в подзапросах и передача найденных строк внешнему запросу в качестве управляемых таблиц позволяет последнему получить любые нужные столбцы.

ДшшшителЬа! Подробную информацию об итоговых функциях и предложении group by вы ^информация найдете в главе 11.

На вопрос “Какое количество каждого товара было продано?” легко ответить, если включить в результат только один столбец из таблицы Product:

SELECT ProductCode, SUM(Quantity) AS QuantitySold FROM dbo.OrderDetail JOIN dbo.Product

ON OrderDetail.ProductID = Product.ProductID GROUP BY ProductCode

Получим следующий результат:

ProductCode QuantitySold

1002                     47.00

1003                     5.00

1004                      2.00

1012                      5.00

В результат был включен код товара (ProductCode), но не были включены ни названия, ни описания. Естественно, можно группировать значения по столбцам, но такой подход будет слишком грубым. Приведенный ниже запрос выполняет консолидацию значений в подзапросах, которые затем объединяются с таблицей Product. Таким образом, мы получаем доступ ко всем столбцам, не выполняя дополнительной работы.

SELECT Product.ProductCode, Product.ProductName,

Sales.QuantitySold

FROM dbo.Product

JOIN (SELECT ProductID, SUM(Quantity) AS QuantitySold FROM dbo.OrderDetail GROUP BY ProductID) Sales ON Product.ProductID = Sales.ProductID

ORDER BY ProductCode

Если вы используете конструктор запросов утилиты Management Studio, то управляемую таблицу можно добавить к запросу. На рис. 10.1 показан приведенный выше запрос, спроектированный в графическом интерфейсе этой утилиты.

Рис. 10.1. Управляемые таблицы можно добавить в запрос в конструкторе запросов с помощью пункта Add Derived Table контекстного меню

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

ProductCode ProductName                                         QuantitySold

1002       Dragon Flight                                                47.00

1003       Sky Dancer                                                    5.00

1004       Rocket Kite                                                     2.00

1012       Falcon F-16                                                   5.00

Еще один пример использования управляемой таблицы позволяет ответить на вопрос “Сколько детей родила каждая из матерей?” с помощью учебной базы данных Family:

USE Family

SELECT PersonID, FirstName, LastName, Children FROM dbo.Person

JOIN (SELECT MotherID, COUNT(*) AS Children FROM dbo.Person WHERE MotherlD IS NOT NULL GROUP BY MotherlD) ChildCount ON Person.PersonID = ChildCount.MotherlD ORDER BY Children DESC

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

PersonID FirstName LastName Children

6Audry                       Halloway 4

8Melanie                     Campbell 3

12                                    Alysia         Halloway                3

20                                     Grace         Halloway                2

Источник: Нильсен, Пол. Microsoft SQL Server 2005. Библия пользователя. : Пер. с англ. — М. : ООО “И.Д. Вильямс”, 2008. — 1232 с. : ил. — Парал. тит. англ.

По теме:

  • Комментарии