Предикат distinct

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

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

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

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

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

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

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)

Случаи, в которых без порождаемых таблиц обойтись невозможно

На самом деле, пример 15.14демонстрирует лишь возможность альтернативных формулировок запросов с использованием ссылок на порождаемые таблицы в разделе FROM. Но в некоторых случаях без подобных конструкций просто невозможно обойтись. Вот простой пример.

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;

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

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

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.15.1. (html,txt)

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

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;

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

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

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;

Пример 15.16.1. (html,txt)