Еще один способ формулировки запросов
Прежде всего, на простом примере покажем, как использование ссылок на порождаемые таблицы расширяет возможности формулировки запросов.
SELECT MNG.DEPT_NO, MNG.MNG_NAME FROM (SELECT DEPT.DEPT_NO, EMP.DEPT_NO, EMP_NAME, EMP_SAL FROM DEPT, EMP WHERE DEPT.DEPT_MNG = EMP.EMP_NO) AS MNG (DEPT_NO_1, DEPT_NO_2, MNG_NAME, MNG_SAL) WHERE DEPT_NO_1 = DEPT_NO_2 AND MNG_SAL = (SELECT MAX (EMP_SAL) FROM EMP WHERE EMP.DEPT_NO = DEPT_NO_1);
Пример 15.14. Найти номера отделов и имена руководителей отделов, которые числятся в тех же отделах, которыми руководят, и получают зарплату, размер которой является максимальным для сотрудников данного отдела. (html, txt)
В этом запросе порождаемая таблица MNG содержит по одной строке для каждого служащего, являющегося руководителем отдела. Первый столбец этой таблицы - DEPT_NO_1 - содержит номер отдела, которым руководит данный служащий. В столбце DEPT_NO_1 хранятся номера отделов, в которых числятся руководители отделов, а в столбцах EMP_NAME и EMP_SAL содержатся имя служащего-руководителя отдела и размер его заработной платы соответственно.
Конечно, этот запрос можно сформулировать и без использования ссылки на порождаемую таблицу в разделе FROM, например, следующим образом (пример 15.14.1):
SELECT DEPT.DEPT_NO, EMP.EMP_NAME FROM DEPT, EMP WHERE DEPT.DEPT_MNG = EMP.EMP_NO AND DEPT.DEPT_NO = EMP.DEPT_NO AND EMP.EMP_SAL = (SELECT MAX(EMP_SAL) FROM EMP WHERE EMP.DEPT_NO = DEPT.DEPT_NO);
Пример 15.14.1.
(html, txt)
А вот как можно сформулировать тот же запрос с использованием раздела WITH (пример 15.14.2):
WITH MNG (DEPT_NO_1, DEPT_NO_2, MNG_NAME, MNG_SAL) AS (SELECT DEPT.DEPT_NO, EMP.DEPT_NO, EMP_NAME, EMP_SAL FROM DEPT, EMP WHERE DEPT.MNG_NO = EMP.EMP_NO), MAX_DEPT_SAL (MAX_SAL, DEPT_NO) AS (SELECT MAX (EMP_SAL), DEPT_NO FROM EMP WHERE DEPT_NO IS NOT NULL GROUP BY DEPT_NO) SELECT DEPT_NO_1, MNG_NAME FROM MNG WHERE DEPT_NO_1 = DEPT_NO_2 AND MNG_SAL = (SELECT MAX_SAL FROM MAX_DEPT_SAL WHERE MAX_DEPT_SAL.DEPT_NO = DEPT_NO_1);
Пример 15.14.2.
(html, txt)