loading..
Русский    English
19:10
листать

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

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

Пример 8.9.1

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

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

Консоль
Выполнить
  1. SELECT id_psg, MAX(date) 
  2. FROM pass_in_trip
  3. GROUP BY id_psg;

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

Консоль
Выполнить
  1. SELECT id_psg, trip_no, MAX(date) 
  2. FROM pass_in_trip
  3. GROUP BY id_psg;
то получим сообщение об ошибке, так как номер рейса не используется в агрегатной функции и не входит список столбцов группировки. Если включить номер рейса в этот список:

Консоль
Выполнить
  1. SELECT id_psg, trip_no, MAX(date) 
  2. FROM pass_in_trip
  3. GROUP BY id_psg, trip_no;
мы получим последний полет пассажира каждым рейсом, которым он летал. Это совсем не та задача, которую мы пытаемся решить. Применение коррелирующего подзапроса

Консоль
Выполнить
  1. SELECT id_psg, trip_no, [date] 
  2. FROM pass_in_trip pt_1
  3. WHERE [date] = (SELECT MAX([date]) 
  4.  FROM pass_in_trip pt_2 
  5.  WHERE pt_1.id_psg = pt_2.id_psg
  6.  );
дает то, что нужно:

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 с приведенным в самом начале подзапросом, который вычисляет максимальные даты по каждому пассажиру:

Консоль
Выполнить
  1. SELECT pt_1.id_psg, trip_no, [date] 
  2. FROM pass_in_trip pt_1 JOIN 
  3.  (SELECT id_psg, MAX([date]) md 
  4.  FROM pass_in_trip 
  5.  GROUP BY id_psg
  6.  ) pt_2 ON pt_1.id_psg = pt_2.id_psg AND 
  7.  [date] = md;

Внимание:

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


Рекомендуемые упражнения: 58
Развернуть всё
Свернуть всё

Содержание:
Тэги:
ALL AND AUTO_INCREMENT AVG battles CASE CAST CHAR CHARINDEX CHECK classes COALESCE CONSTRAINT Convert COUNT CROSS APPLY CTE DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DELETE DISTINCT DML EXCEPT EXISTS EXTRACT FOREIGN KEY FROM FULL JOIN GROUP BY Guadalcanal HAVING IDENTITY IN INFORMATION_SCHEMA INNER JOIN insert INTERSECT IS NOT NULL IS NULL ISNULL laptop LEFT LEFT OUTER JOIN LEN maker Больше тэгов
Учебник обновлялся
несколько дней назад
©SQL-EX,2008 [Развитие] [Связь] [О проекте] [Ссылки] [Team]
Перепечатка материалов сайта возможна только с разрешения автора.