Книги и статьи по SQL Rambler's Top100 Switch language to: English 29 октября 2020 г. 5:14:48


www.sql-ex.ru
Skip Navigation Links  

 

Print  Версия для печати

На главную страницу

Коррелирующие подзапросы

Моисеенко С.И.

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

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

SELECT id_psg, MAX(date) FROM pass_in_trip GROUP BY id_psg

Однако тут нет рейса. Если мы попытаемся включить рейс в список вывода:

SELECT id_psg, trip_no, MAX(date) FROM pass_in_trip GROUP BY id_psg

то получим сообщение об ошибке, т.к. номер рейса не используется в агрегатной функции и не входит список столбцов группировки. Если сделать последнее:

SELECT id_psg, trip_no, MAX(date) FROM pass_in_trip GROUP BY id_psg, trip_no

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

SELECT id_psg, trip_no, [date] FROM pass_in_trip pt_1
  WHERE [date]=(
      SELECT MAX([date]) FROM pass_in_trip pt_2
         WHERE pt_1.id_psg=pt_2.id_psg)

дает то, что нужно:

id_psg trip_no date
10 1187 2003-04-14 00:00:00.000
9 1182 2003-04-13 00:00:00.000
8 1187 2003-04-14 00:00:00.000
6 1123 2003-04-08 00:00:00.000
5 1145 2003-04-25 00:00:00.000
3 1145 2003-04-05 00:00:00.000
3 1123 2003-04-05 00:00:00.000
2 1124 2003-04-02 00:00:00.000
1 1100 2003-04-29 00:00:00.000

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

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

SELECT pt_1.id_psg, trip_no, [date]
   FROM pass_in_trip pt_1
      JOIN (
      SELECT id_psg, MAX([date]) md
         FROM pass_in_trip GROUP BY id_psg) pt_2
      ON pt_1.id_psg=pt_2.id_psg AND [date]=md

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

» Приведенные здесь примеры можно выполнить непосредственно на сайте, установив флажок "Без проверки" на странице с упражнениями на SELECT.

На главную страницу

Print  Версия для печати


Использование любых материалов данного сайта возможно только
при условии обязательного размещения прямой ссылки на сайт
http://www.sqlbooks.ru
на каждой странице, где размещены используемые материалы.

 Начало   Статьи    Книги 
Рейтинг@Mail.ru Rambler's Top100 Alt Упражнения по SQL: обучение, тестирование, сертификация по языку SQL Copyright c 2002-2006. All rights reserved.