Главная | Обратная связь | Поможем написать вашу работу!

Связанные подзапросы

Связанный подзапрос – это подзапрос, зависящий от информации, предоставляемой главным запросом.

В следующем примере в подзапросе определение связи между таблицами TARIFF во внутреннем подзапросе и AIRPORT использует псевдоним таблицы AFROM, определенный в главном запросе. Изменим условия предыдущего запроса – найдем номер авиалинии, аэропорт вылета, вид салона и стоимость самого дешевого билета для рейсов вылетающих из города «Пермь», т.е. от предыдущего запроса новый отличатся тем, что мы ищем только по тарифам связанным с аэропортами вылета находящимися в городе «Пермь». Для того чтобы это сделать нужно во внешний запрос добавить объединение с таблицей CITY и наложить условие (предложение WHERE) по названию города, во внутреннем запросе нужно искать минимум не из всех возможных строк таблицы TARIFF, а только из тех, у которых код аэропорта вылета (TR_AP_FROM) совпадает с допустимыми аэропортами.

Получаем следующий запрос:[44]

SELECT T. TR_AL_NUM AS "Номер Авиалинии",

AFROM. AP_NAME AS "Аэропорт вылета",

ATO. AP_NAME AS "Аэропорт прилета",

S. SL_NAME AS "Салон",

T. TR_COST AS "Стоимость"

FROM TARIFF T

INNER JOIN AIRPORT AFROM ON ( T. TR_AP_FROM = AFROM. AP_CODE )

INNER JOIN AIRPORT ATO ON ( T. TR_AP_TO = ATO. AP_CODE )

INNER JOIN SALON S ON ( T. TR_SL_TYPE = S. SL_TYPE )

INNER JOIN CITY C ON ( AFROM. AP_CT_CODE = C. CT_CODE )

WHERE ( C. CT_NAME = 'Пермь' )

AND ( T. TR_COST = (SELECT MIN( TR_COST ) FROM TARIFF

WHERE TR_AP_FROM = AFROM. AP_CODE ))

Другим примером может быть использование вложенного подзапроса с функцией COUNT. Найдем фамилию, имя, отчество членов экипажа совершивших в феврале 2008 года более одного полета, иными словами тех, для кого в таблице EQUIPAGE содержится более одной записи датируемой февралем.[45]

SELECT PR_NAME2 AS "Имя",

PR_NAME3 AS "Отчество",

PR_NAME AS "Фамилия"

FROM PERSON

WHERE (SELECT COUNT(*) FROM EQUIPAGE

WHERE EQ_FL_DATE BETWEEN '1.02.08' AND '29.02.08'

AND EQ_PR_CODE = PERSON. PR_CODE ) > 1

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

В результате получаем результат:

Листинг 23. Список членов экипажа совершивших в феврале 2008 года более одного полета

Имя	Отчество	Фамилия
Имя	Отчество	Фамилия
Евгений	Андреевнич	Болотов
Марина	Владимировна	Волченко
Анна	Валерьевна	Грунтович

ЗАМЕЧАНИЕ

Точно такого же результата можно было добиться построив запрос с использованием группировки по колонкам PR_NAME2, PR_NAME3, PR_NAME и применив HAVING COUNT(*) >1

Следующий запрос проиллюстрирует другой пример использования функции COUNT в подзапросе (в этом случае использование подзапроса является единственным способом получения результата). Итак получим фамилию, имя, отчество пассажиров, которые приобрели более одного билета в течении 2008 года, также выведем номер авиалинии и дату вылета из билета. Внутренний подзапрос подсчитает количество билетов за 2008 год для каждой личности, которые извлекаются внешним запросом. При этом внешний запрос выбирает только те строки, где количество найденное внутреннем запросом будет больше одного (иными словами у этой личности больше одного билета), а также производит внутреннее объединение таблиц PERSON и TICKET.. Внутренний и внешний запрос связаны при помощи приравнивания кода личности из внешнего запроса и кода личности указанного в билете из внутреннего запроса. Для того чтобы данные на билеты приобретенные одним пассажиром выводились вместе, результат упорядочиваем по фамилии и имени.

Получаем запрос: [46]

SELECT P. PR_NAME2 AS "Имя",

P. PR_NAME3 AS "Отчество",

P. PR_NAME AS "Фамилия",

T. TC_FL_NUM AS "Номер авиалинии",

T. TC_FL_DATE AS "Дата вылета"

FROM PERSON P

INNER JOIN TICKET T ON T. TC_PR_CODE = P. PR_CODE

WHERE T. TC_FL_DATE BETWEEN '1.01.08' AND '31.12.08'

AND (SELECT COUNT(*) FROM TICKET

WHERE ( TC_PR_CODE = P. PR_CODE )

AND ( TC_FL_DATE BETWEEN '1.01.08' AND '31.12.08' )) > 1

ORDER BY P. PR_NAME, P. PR_NAME2

Другой способ получить этот же результат – это воспользоваться вариантом IN. Как правило, связные подзапросы можно заменить на вариант IN, внутри которого находится подзапрос.

Для получения результата внутренний подзапрос должен найти коды всех пассажиров имеющих более одного билета в течении одного года, для этого нужно получить код личности (TC_TR_CODE) из таблицы TICKET сгруппировав результат по этому коду. Для выделения тех кодов, которые встречаются более одного раза, используем HAVING COUNT(*) >1. Внешний запрос выведет фамилии, имена, отчества, номера авиалинии и даты вылета для людей, коды которых были найдены во внутреннем запросе.

Запрос будет иметь вид: [47]

SELECT P. PR_NAME2 AS "Имя",

P. PR_NAME3 AS "Отчество",

P. PR_NAME AS "Фамилия",

T. TC_FL_NUM AS "Номер авиалинии",

T. TC_FL_DATE AS "Дата вылета"

FROM PERSON P

INNER JOIN TICKET T ON T. TC_PR_CODE = P. PR_CODE

WHERE T. TC_FL_DATE BETWEEN '1.01.08' AND '31.12.08'

AND T. TC_PR_CODE IN

(SELECT TC_PR_CODE FROM TICKET

WHERE TC_FL_DATE BETWEEN '1.01.08' AND '31.12.08'

GROUP BY TC_PR_CODE HAVING COUNT(*) > 1 )

ORDER BY P. PR_NAME, P. PR_NAME2

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

Листинг 24. Список пассажиров, которые приобрели более одного билета в течении 2008 года, также выводится номер авиалинии и дата вылета из билета. (с использованием GROUP BY и HAVING в подзапросе)

Имя	Отчество	Фамилия	Номер авиалинии	Дата вылета
Константин	Игоревич	Баталов		08.01.2008
Константин	Игоревич	Баталов		11.01.2008
Мария	Ивановна	Выгузова		08.01.2008
Мария	Ивановна	Выгузова		11.01.2008
Василий	Александрович	Глазов		08.01.2008
Василий	Александрович	Глазов		11.01.2008

⇐ Предыдущая 5 6 7 8 9 101112 13 14 Следующая ⇒