Прості підзапити

Порядок виконання простих підзапитів наведено нижче

1 Простий подзапрос виконується один раз

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

2 Зовнішній запит виконується один раз

Найпростіший подзапрос повертає одне (скалярний) значення, яке потім використовується як вираз у зовнішньому підзапиті, наприклад:

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

SubqueryValue

3

Підзапит (select 3) повертає скалярний значення 3, яке передається зовнішньої інструкції SELECT Після цього виконується зовнішня інструкція SELECT при цьому вона вже має наступний вигляд:

SELECT 3 AS SubqueryValue

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

USE OBXKites SELECT ProductName FROM dboProduct WHERE ProductCategorylD

= (Select ProductCategorylD

FROM dboProductCategory

Where ProductCategoryName = Kite)

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

Select ProductCategorylD

FROM dboProductCategory

Where ProductCategoryName = Kite

Результат буде наступним:

ProductCategorylD

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

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

SELECT ProductName FROM dboProduct WHERE ProductCategorylD

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

Результат зовнішнього запиту:

ProductName

Basic Box Kite 21 inch Dragon Flight Sky Dancer Rocket Kite

Якщо ви думаєте, що підзапити схожі на обєднання, то ви абсолютно праві Обидва є засобом обігу до безлічі джерел в одному запиті, і більшість запитів, що використовують обєднання, можна переписати як запити, що використовують підзапити

Використовуйте обєднання для збору даних з двох джерел та подальших маніпуляцій ними як єдиним цілим Якщо ж дані потрібно обробити до обєднання, то використовуйте підзапити

Загальні табличні вирази

Загальні табличні вирази (ETS) визначають, що повинно розглядатися як тимчасове уявлення, яке в подальшому буде використано в запиті в ролі звичайного уявлення Оскільки сте можна використовувати таким же способом, як і прості підзапити, я включив їх розгляд у цей розділ і буду наводити приклади простих підзапитів поряд з сте

Загальні табличні вирази використовують пропозицію WITH, яке визначає їх У пропозиції WITH знаходяться імя, псевдоніми стовпців і код SQL підзапиту сте Після цього основний запит може звернутися до сте як до джерела даних:

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

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

SELECT..

FROM CTEName

Наступний код ідентичний наведеному в попередньому прикладі, тільки він має формат сте Іменем табличного вираження є CTEQuery воно повертає стовпець ProductionCategoryID і використовує точно таку ж інструкцію SELECT, як і подзапрос, наведений в попередньому прикладі:

WITH CTEQuery (ProductCategorylD)

AS (Select ProductCategorylD

FROM dboProductCategory

Where ProductCategoryName = ’Kite)

Після того як загальне табличне вираз було визначено в реченні WITH, основна частина запиту може звертатися до нього по імені, як до звичайного табличному джерела, наприклад таблиці або поданням Наведемо повний приклад, що містить як основний запит, так і сте:

WITH CTEQuery (ProductCategorylD)

AS (Select ProductCategorylD

FROM dboProductCategory

Where ProductCategoryName = Kite)

SELECT ProductName FROM dboProduct 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 dboOrderDetail) *100 AS INT)

AS PercentOfSales FROM dboOrderDetail JOIN dboProduct

ON OrderDetailProductID = ProductProductID JOIN dboProductCategory

ON ProductProductCategorylD = ProductCategoryProductCategorylD GROUP BY ProductCategoryName ORDER BY Count(*) DESC

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

ProductCategoryName Sales                           PercentOfSales

Kite                            1499902500               8670

OBX                          64687500                      374

Clothing                     113600000                    657

Accessory                  10530000                      061

Material                     5265000                        030

Video                         35910000                      208

Наступна інструкція SELECT витягується з користувача функції fGetPrice () навчальної бази даних OBXKites Ця база даних має таблицю цін для конкретних дат, при

цьому магазин може попередньо визначати деякі ціни для майбутніх дат, щоб не вводити їх в ніч напередодні зміни На додаток ця модель ціноутворення дозволяє зберігати історію цін на товари

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

FROM dboPrice JOIN dboProduct

ON PriceProductID = ProductProductID

WHERE ProductCode = 4001

AND EffectiveDate =

(SELECT MAX(EffectiveDate)

FROM dboPrice

JOIN dboProduct

ON PriceProductID = ProductProductID WHERE ProductCode = 1001

AND EffectiveDate &lt= 6/1/2001)

При виклику функції Select dbofGetPrice (1001 # 5/1/2001,NULL)

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

14,95

Використання підзапитів в якості списків

Підзапити постають у всій своїй красі, коли їх починають використовувати як списків При цьому деяке значення, як правило, стовпця зовнішнього запиту, порівнюється зі списком, повернутим підзапитом, за допомогою оператора IN Підзапит повинен повертати тільки один стовпець, в іншому випадку відбудеться помилка

Оператор IN повертає істинне значення (true), якщо значення стовпця знайдено в списку, повернутому підзапитом таким же чином діє вираз WHERE .. IN при роботі із запрограмованим списком:

SELECT *

FROM dboContact

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

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

SELECT *

FROM dboContact

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

Наведений у наступному прикладі запит дає відповідь на питання: Що ще купують в магазині ОВХ Kites при покупці повітряного змія” У цьому запиті ми будемо використовувати виключно підзапити – ніяких обєднань Всі ці підзапити є простими, в тому сенсі, що кожен з них можна виконати відокремлено як запит

Цей запит знайде всі замовлення, що містять повітряних зміїв, і передасть їх ідентифікатори головному запитом У отриманні відповіді на питання будуть задіяні чотири таблиці: Product Category, Product, OrderDetail і Order Вкладені підзапити виконуються від самого внутрішнього до зовнішнього

1 Підзапит знаходить ідентифікатор категорії для повітряних зміїв (Product Category ID)

2 Підзапит знаходить список продуктів, що потрапляють в категорію повітряних зміїв (тобто з знайденим ідентифікатором)

3 Підзапит знаходить список замовлень, що містять повітряних зміїв

4 Підзапит знаходить список товарів, які містяться у замовленнях з повітряними зміями

5 Зовнішній запит витягує назви цих товарів

SELECT ProductName FROM dboProduct WHERE ProductID IN – 4 Пошук всіх товарів, які містяться у замовленнях з повітряними зміями (SELECT ProductID

FROM dboOrderDetail

WHERE OrderlD IN – 3 Пошук замовлень з повітряними зміями (SELECT OrderlD – Find the Orders with Kites FROM dboOrderDetail

WHERE ProductID IN – 2 Пошук товарів категорії повітряних зміїв (SELECT ProductID FROM dboProduct WHERE ProductCategorylD =

– 1 Пошук категорії повітряних зміїв (SELECT ProductCategorylD FROM dboProductCategory WHERE ProductCategoryName = Kite))))

При бажанні ви можете виділити будь-який з підзапитів у вікні Query і запус-Рада тить його як відокремлений запит, натиснувши клавішу

Підзапит 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 dboProduct WHERE ProductID IN – 4 Пошук всіх товарів, які містяться у замовленнях з повітряними зміями (SELECT ProductID

FROM dboOrderDetail

WHERE OrderlD IN – 3 Пошук замовлень з повітряними зміями (SELECT OrderlD – Find the Orders with Kites FROM dboOrderDetail

WHERE ProductID IN – 2 Пошук товарів категорії повітряних зміїв (SELECT ProductID FROM dboProduct WHERE ProductCategorylD =

– 1 Пошук категорії повітряних зміїв (SELECT ProductCategorylD FROM dboProductCategory WHERE ProductCategoryName = 1 Kite1 ) ) ) )

– Продовжуємо зовнішній запит AND ProductID NOT IN (SELECT ProductID

FROM dboProduct WHERE ProductCategorylD =

(SELECT ProductCategorylD

FROM dboProductCategory 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 ProductProductName FROM dboProduct

JOIN dboOrderDetail OrderRow

ON ProductProductID = OrderRowProductID JOIN dboOrderDetail KiteRow

ON OrderRowOrderID = KiteRowOrderID JOIN dboProduct Kite

ON KiteRowProductID = KiteProductID JOIN dboProductCategory ON KiteProductCategorylD

= ProductCategoryProductCategorylD WHERE ProductCategoryName = Kite 1

Єдиною зміною, яке потрібно внести для виключення повітряних зміїв, є додаткова умова в обєднанні з таблицею ProductCategory Раніше це було обєднання рівності між таблицями Product і ProductCategory Додавання умови 0-обєднання ( =) Між таблицями Product і ProductCategory усуває всі товари, що потрапляють в категорію повітряних зміїв, як показано в наступному прикладі:

SELECT Distinct ProductProductName FROM dboProduct

JOIN dboOrderDetail OrderRow

ON ProductProductID = OrderRowProductID JOIN dboOrderDetail KiteRow

ON OrderRowOrderID = KiteRowOrderID JOIN dboProduct Kite

ON KiteRowProductID = KiteProductID JOIN dboProductCategory ON KiteProductCategorylD

= ProductCategoryProductCategorylD AND ProductProductCategorylD =KiteProductCategorylD WHERE ProductCategoryName = Kite

Ми розглянули два безлічі запитів, що використовують різний синтаксис, але дають відповідь на одне і те ж питання Який з них краще Все залежить від вас Залежно від складності підзапити можуть виконуватися швидше, оскільки вони відбирають на кожному кроці меншу кількість рядків Більш складні підзапити мають тенденцію виконуватися швидше, ніж великі запити з обєднаннями

SQL є дуже гнучким мовою – звичайно в ньому існують десятки способів задати один і той же питання Вибір методу в першу чергу залежить від предпочитаемого вами стилю – текст повинен бути читабельним і логічно коректним І вже потім розглядається питання швидкодії Повільні, але правильні запити завжди краще швидких, але некоректних

Наведемо ще один приклад, в якому творчий підхід до створення підзапиту допомагає впоратися із завданням SQL з легкістю справляється з відбором десятка найбільших або найменших значень набагато складніше йдуть справи з відбором середнього діапазону У наші

дні, коли пошук в Web повертає сотні, якщо не тисячі посилань, пошук рядків, наприклад зі 101-й по 125-ю, може виявитися досить корисним

У наведеному нижче прикладі ми знову будемо використовувати навчальну базу даних OBXKites Ми відберемо пятірку товарів, починаючи з 26-го Підзапит шукає перші 25 товарів, після чого вони пропускаються в основному запиті за допомогою пропозиції WHERE NOT IN:

USE OBXKites

SELECT TOP 5 ProductName, ProductID FROM dboProduct WHERE ProductID NOT IN

(SELECT TOP 2 5 ProductID FROM dboProduct 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 dboOrderDetail JOIN dboProduct

ON OrderDetailProductID = ProductProductID GROUP BY ProductCode

Отримаємо наступний результат:

ProductCode QuantitySold

1002&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp 4700

1003&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp 500

1004                      200

1012                      500

У результат був включений код товару (ProductCode), але не були включені ні назви, ні опису Природно, можна групувати значення за стовпцями, але такий підхід буде дуже грубим Наведений нижче запит виконує консолідацію значень в підзапитах, які потім обєднуються з таблицею Product Таким чином, ми отримуємо доступ до всіх стовпцях, не виконуючи додаткової роботи

SELECT ProductProductCode, ProductProductName,

SalesQuantitySold

FROM dboProduct

JOIN (SELECT ProductID, SUM(Quantity) AS QuantitySold FROM dboOrderDetail GROUP BY ProductID) Sales ON ProductProductID = SalesProductID

ORDER BY ProductCode

Якщо ви використовуєте конструктор запитів утиліти Management Studio, то керовану таблицю можна додати до запиту На рис 101 показаний Наведений вище запит, спроектований в графічному інтерфейсі цієї утиліти

Рис 101 Керовані таблиці можна додати в запит в конструкторі запитів за допомогою пункту Add Derived Table контекстного меню

Цей запит швидкий і ефективний він виконує необхідну консолідацію даних, при цьому всі стовпці можна додати в результат Результат запиту, наведеного як приклад:

ProductCode ProductName                                         QuantitySold

1002&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp Dragon Flight                                                4700

1003&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp Sky Dancer                                                    500

1004&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp Rocket Kite                                                     200

1012&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp Falcon F-16                                                   500

Ще один приклад використання керованої таблиці дозволяє відповісти на питання Скільки дітей народила кожна з матерів” За допомогою навчальної бази даних Family:

USE Family

SELECT PersonID, FirstName, LastName, Children FROM dboPerson

JOIN (SELECT MotherID, COUNT(*) AS Children FROM dboPerson WHERE MotherlD IS NOT NULL GROUP BY MotherlD) ChildCount ON PersonPersonID = ChildCountMotherlD 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 с : Ил – Парал тит англ

Схожі статті:


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

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*