Еще один способ формулировки запросов

Ссылки на порождаемые таблицы в разделе FROM

Предикат distinct

Пример 15.13. Найти номера отделов, которые можно отличить от любого другого отдела по дате рождения руководителя и среднему размеру зарплаты.

SELECT DEPT.DEPT_NO
FROM DEPT, EMP EMP1, EMP EMP2
WHERE DEPT.DEPT_NO = EMP1.DEPT_NO AND
DEPT.DEPT_MNG = EMP2.EMP_NO

GROUP BY DEPT.DEPT_NO, EMP2.EMP_BDATE
HAVING (EMP2.EMP_BDATE, AVG (EMP1.EMP_SAL)) DISTINCT FROM
(SELECT EMP2.EMP_BDATE, AVG (EMP1.EMP_SAL)
FROM DEPT DEPT1, EMP EMP1, EMP EMP2
WHERE DEPT1.DEPT_NO = EMP1.DEPT_NO AND
DEPT1.DEPT_MNG = EMP2.EMP_NO AND
DEPT1.DEPT_NO <> DEPT.DEPT_NO

GROUP BY DEPT.DEPT_NO, EMP2.EMP_BDATE);

В этом разделе мы приведем несколько примеров запросов, в разделе FROM которых содержатся выражения запросов (ссылки на порождаемые таблицы, см. разд. 13.3 Лекции 13).

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

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

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);

В этом запросе порождаемая таблица MNG содержит по одной строке для каждого служащего, являющегося руководителем отдела. Первый столбец этой таблицы – DEPT_NO_1 – содержит номер отдела, которым руководит данный служащий. В столбце DEPT_NO_1 хранятся номера отделов, в которых числятся руководители отделов, а в столбцах EMP_NAME и EMP_SAL содержатся имя служащего-руководителя отдела и размер его заработной платы соответственно.

Конечно, этот запрос можно сформулировать и без использования ссылки на порождаемую таблицу в разделе FROM, например, следующим образом (пример 15.14a):

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);

А вот как можно сформулировать тот же запрос с использованием раздела WITH (пример 15.14b):

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 демонстрирует лишь возможность альтернативных формулировок запросов с использованием ссылок на порождаемые таблицы в разделе FROM. Но в некоторых случаях без подобных конструкций просто невозможно обойтись. Вот простой пример.

Пример 15.15. Найти общее число сотрудников и максимальный размер зарплаты в отделах с одинаковым максимальным размером зарплаты.

SELECT SUM (TOTAL_EMP), MAX_SAL

FROM (SELECT MAX (EMP_SAL), COUNT (*)
FROM EMP
WHERE DEPT_NO IS NOT NULL
GROUP BY DEPT_NO) AS DEPT_MAX_SAL (MAX_SAL, TOTAL_EMP)
GROUP BY MAX_SAL;

И в этом случае выражение запросов, содержащееся в разделе FROM, можно перенести в раздел WITH (пример 15.15a):

WITH DEPT_MAX_SAL (MAX_SAL, TOTAL_EMP) AS
(SELECT MAX (EMP_SAL), COUNT (*)
FROM EMP
WHERE DEPT_NO IS NOT NULL
GROUP BY DEPT_NO)
SELECT SUM (TOTAL_EMP), MAX_SAL

FROM DEPT_MAX_SAL
GROUP BY MAX_SAL;

Здесь мы не можем обойтись “одноуровневой” конструкцией запроса, поскольку требуется двойная группировка, причем вторая группировка должна быть получена в соответствии с результатами первой. Еще один пример.

Пример 15.16. Найти число проектов, дату их завершения и средний размер зарплаты сотрудников, участвующих в проекте, для проектов с одной и той же датой завершения и одним и тем же средним размером зарплаты сотрудников, участвующих в проекте.

SELECT COUNT (*), PRO_EDATE, AVG_SAL
FROM (SELECT PRO_EDATE, AVG (EMP_SAL)
FROM (SELECT PRO_SDATE + PRO_DURAT, PRO_NO
FROM PRO) AS PRO1 (PRO_EDATE, PRO_NO), EMP
WHERE PRO1.PRO_NO = EMP.PRO_NO
GROUP BY PRO1.PRO_NO) AS PRO_AVG_SAL (PRO_EDATE, AVG_SAL)
GROUP BY PRO_EDATE, AVG_SAL;

Заметим, что выражение запросов на третьей и четвертой строках примера нужно только по той причине, что нам требуется группировка по дате окончания проектов, соответствующий столбец в таблице PRO отсутствует, а в списке группировки можно использовать только имена столбцов. Для упрощения вида формулировки это выражение разумно вынести в раздел WITH (пример 15.16a):

WITH PRO1 (PRO_EDATE, PRO_NO) AS
(SELECT PRO_SDATE + PRO_DURAT, PRO_NO
FROM PRO)
SELECT COUNT (*), PRO_EDATE, AVG_SAL
FROM (SELECT PRO_EDATE, AVG (EMP_SAL)
FROM PRO1, EMP
WHERE PRO1.PRO_NO = EMP.PRO_NO
GROUP BY PRO1.PRO_NO) AS PRO_AVG_SAL (PRO_EDATE, AVG_SAL)
GROUP BY PRO_EDATE, AVG_SAL;

Дата добавления: 2014-01-03