2008 г.

Базы данных. Вводный курс

Сергей Кузнецов

19.2.3. Логические выражения раздела HAVING

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

Предикаты сравнения

Пример 19.1. Найти номера отделов, в которых работает ровно 30 служащих.

SELECT DEPT_NO
FROM EMP
WHERE DEPT_NO IS NOT NULL
GROUP BY DEPT_NO
HAVING COUNT(*) = 30;

Конечно, этот запрос можно сформулировать и без использования разделов GROUP BY и HAVING. Например, возможна следующая формулировка (пример 19.1.1):

SELECT DISTINCT DEPT_NO
FROM EMP
WHERE (SELECT COUNT (*)
   FROM EMP EMP1
   WHERE EMP1.DEPT_NO = EMP.DEPT_NO) = 30;

Обратите внимание, что в формулировке 15.1.1 отдельная проверка условия DEPT_NO IS NOT NULL не требуется.

Пример 19.2. Найти номера всех отделов, в которых средний размер зарплаты служащих превосходит 12000 руб.

   
SELECT DEPT_NO
FROM EMP
WHERE DEPT_NO IS NOT NULL
GROUP BY DEPT_NO
HAVING AVG(EMP_SAL) > 12000.00;

Очевидно, что и в этом случае возможна формулировка запроса без использования разделов GROUP BY и HAVING (пример 19.2.1):

SELECT DISTINCT DEPT_NO
FROM EMP
WHERE (SELECT AVG(EMP1.EMP_SAL)
   FROM EMP EMP1
   WHERE EMP1.DEPT_NO = EMP.DEPT_NO) > 12000.00;

Немного задержимся на этих примерах и обсудим, что означает различие в формулировках запросов. В соответствии с семантикой оператора SELECT, при выполнении запросов 15.1.1 и 15.2.1 для каждой строки таблицы EMP в цикле просмотра внешнего запроса будет выполняться подзапрос, который в случае наших примеров выберет из таблицы EMP (EMP1) все строки со значением столбца DEPT_NO, равным значению этого столбца в текущей строке внешнего цикла. Другими словами, для каждой строки внешнего цикла образуется группа, для нее проверяется условие выборки, и в списке выборки используется имя столбца этой неявной группировки. Из-за того, что группа образуется и оценивается для каждой строки таблицы EMP, мы вынуждены указать в разделе SELECT спецификацию DISTINCT.

Формулировки 15.1 и 15.2 обеспечивают более четкие указания для выполнения запроса. Нужно сразу сгруппировать таблицу EMP в соответствии со значениями столбца DEPT_NO, отобрать нужные группы, и для каждой отобранной группы вычислить значения выражений списка выборки. В этом случае семантика выполнения запроса не предписывает выполнения лишних действий. Конечно, в развитой реализации SQL компилятор должен суметь понять, что формулировки 15.1.1 и 15.2.1 эквивалентны формулировкам 15.1 и 15.2 соответственно, и избежать выполнения лишних действий.

Пример 19.3. Найти номера всех отделов, в которых суммарный объем зарплаты служащих меньше суммарного объема зарплаты всех руководителей отделов.

SELECT DEPT_NO
FROM EMP
WHERE DEPT_NO IS NOT NULL
GROUP BY DEPT_NO
HAVING SUM(EMP_SAL) < (SELECT SUM(EMP1.EMP_SAL)
   FROM EMP EMP1, DEPT
   WHERE EMP1.EMP_NO = DEPT_MNG);

И в этом случае возможна формулировка без использования разделов GROUP BY и HAVING (пример 19.3.1). Эта формулировка является более сложной, чем в случае двух предыдущих примеров, но и к ней применимы приведенные выше замечания.

SELECT DISTINCT DEPT_NO
FROM EMP
WHERE (SELECT SUM(EMP1.EMP_SAL)
   FROM EMP EMP1
   WHERE EMP1.DEPT_NO = EMP.DEPT_NO) < 
   (SELECT SUM(EMP1.EMP_SAL)
   FROM EMP EMP1, DEPT
   WHERE EMP1.EMP_NO = DEPT_MNG);

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

SELECT DEPT.DEPT_NO, EMP.EMP_NAME, COUNT(*), 
   MIN(EMP1.EMP_SAL), MAX(EMP1.EMP_SAL), AVG(EMP1.EMP_SAL)
FROM DEPT, EMP, EMP EMP1
WHERE DEPT.DEPT_NO = EMP1.DEPT_NO
GROUP BY DEPT.DEPT_NO, DEPT.DEPT_MNG, EMP.EMP_NO, EMP.EMP_NAME
HAVING DEPT.DEPT_MNG = EMP.EMP_NO;

Этот запрос иллюстрирует несколько интересных особенностей языка SQL. Во-первых, это первый пример запроса с соединениями, в котором присутствуют разделы GROUP BY и HAVING. Во-вторых, одно условие соединения находится в разделе WHERE, а другое – в разделе HAVING. На самом деле, можно было бы перенести в раздел WHERE и второе условие соединения, и, скорее всего, на практике использовалась бы формулировка, приведенная в примере 19.4.1:

SELECT DEPT.DEPT_NO, EMP.EMP_NAME, COUNT(*), 
   MIN(EMP1.EMP_SAL), MAX(EMP1.EMP_SAL), AVG(EMP1.EMP_SAL)
FROM DEPT, EMP, EMP EMP1
WHERE DEPT.DEPT_NO = EMP1.DEPT_NO 
   AND DEPT.DEPT_MNG = EMP.EMP_NO
GROUP BY DEPT.DEPT_NO, EMP.EMP_NAME;

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

Наконец, легко видеть, что, по существу, группировка производится по значениям столбца DEPT.DEPT_NO. Остальные столбцы, указанные в списке столбцов группировки, функционально определяются столбцом DEPT.DEPT_NO. Тем не менее, в первой формулировке мы включили в этот список столбцы DEPT.DEPT_MNG и EMP.EMP_NO, чтобы их имена можно было использовать в условии раздела HAVING, и столбец EMP.EMP_NAME, чтобы можно было использовать его имя в списке выборки раздела SELECT. Другими словами, мы вынуждены расширять запрос избыточными данными, чтобы выполнить формальные синтаксические требования языка. Как видно, во второй формулировке мы смогли удалить из списка группировки два столбца. Кстати, не следует думать, что многословие первой формулировки помешает СУБД выполнить запрос настолько же эффективно, как запрос во второй формулировке. Грамотно построенный оптимизатор SQL сам приведет первую формулировку ко второй.

И этот запрос можно сформулировать без использования раздела GROUP BY за счет использования подзапросов в списке раздела SELECT (пример 19.4.2):

SELECT DEPT.DEPT_NO, EMP.EMP_NAME, 
   (SELECT COUNT(*)
   FROM EMP
   WHERE EMP.DEPT_NO = DEPT.DEPT_NO),
   (SELECT MIN(EMP_SAL)
   FROM EMP
   WHERE EMP.DEPT_NO = DEPT.DEPT_NO),
   (SELECT MAX(EMP_SAL)
   FROM EMP
   WHERE EMP.DEPT_NO = DEPT.DEPT_NO),
   (SELECT AVG(EMP_SAL)
   FROM EMP
   WHERE EMP.DEPT_NO = DEPT.DEPT_NO)
FROM DEPT, EMP
WHERE DEPT.DEPT_MNG = EMP.EMP_NO;

Здесь мы снова имеем замаскированную группировку строк по значениям столбца DEPT.DEPT_NO и вычисление агрегатных функций для каждой группы. Формально группа строится каждый раз заново при вызове каждой агрегатной функции. Хороший компилятор SQL должен привести формулировку 15.4.2 к виду 15.4.1.

И последнее замечание. Во всех приведенных формулировках в результат не попадут данные об отделах, в которых отсутствует руководитель (столбец DEPT.DEPT_MNG может содержать неопределенное значение). Вообще говоря, это не противоречит условию запроса, но если бы мы хотели выдавать в результате NULL в качестве имени руководителя отдела с отсутствующим руководителем, то можно было немного усложнить формулировку запроса, например, следующим образом (пример 19.4.3):

         
SELECT DEPT.DEPT_NO, 
   CASE WHEN DEPT.DEPT_MNG IS NULL THEN NULL 
     ELSE (SELECT EMP.EMP_NAME
       FROM EMP
       WHERE EMP.EMP_NO = DEPT.DEPT_MNG),
     COUNT(*), MIN(EMP1.EMP_SAL),
     MAX(EMP1.EMP_SAL), AVG(EMP1.EMP_SAL)
FROM DEPT, EMP, EMP EMP1
WHERE DEPT.DEPT_NO = EMP1.DEPT_NO
GROUP BY DEPT.DEPT_NO;

Предикат between

Пример 19.5. Найти номера отделов и минимальный и максимальный размер зарплаты служащих для отделов, в которых средний размер зарплаты служащих не меньше среднего размера зарплаты служащих во всей компании и не больше 30000 руб.

SELECT DEPT_NO, MIN(EMP_SAL), MAX(EMP_SAL)
FROM EMP
WHERE DEPT_NO IS NOT NULL
GROUP BY DEPT_NO
HAVING AVG(EMP_SAL) BETWEEN
   (SELECT AVG(EMP_SAL)
   FROM EMP) AND 30000.00;

Еще раз приведем возможную формулировку этого запроса без использования разделов GROUP BY и HAVING (пример 19.5.1):

         
SELECT DISTINCT DEPT_NO, (SELECT MIN(EMP1.EMP_SAL)
   FROM EMP EMP1
   WHERE EMP1.DEPT_NO = EMP.DEPT_NO),
   (SELECT MAX(EMP1.EMP_SAL)
   FROM EMP EMP1
   WHERE EMP1.DEPT_NO = EMP.DEPT_NO)
FROM EMP
WHERE (SELECT AVG(EMP1.EMP_SAL)
     FROM EMP EMP1
     WHERE EMP1.DEPT_NO = EMP.DEPT_NO) BETWEEN
   (SELECT AVG(EMP_SAL)
     FROM EMP) AND 30000.00;

Как видно, отказ от использования раздела GROUP BY приводит к размножению однотипных подзапросов, строящих одну и ту же группу строк, над которой вычисляется агрегатная функция.

Предикат null

Пример 19.6. Найти номера и число служащих отделов, данные о руководителях которых не содержат номер отдела (конечно, в этом случае нас интересуют только те отделы, у которых имеется руководитель).

SELECT DEPT.DEPT_NO, COUNT(*)
FROM DEPT, EMP EMP1, EMP EMP2
WHERE DEPT.DEPT_NO = EMP2.DEPT_NO
 AND DEPT.DEPT_MNG = EMP1.EMP_NO
GROUP BY DEPT.DEPT_NO, EMP1.DEPT_NO
HAVING EMP1.DEPT_NO IS NULL;

Как и в примере 19.4, условие раздела HAVING можно переместить в раздел WHERE и получить вторую формулировку (пример 19.6.1):

SELECT DEPT.DEPT_NO, COUNT(*)
FROM DEPT, EMP EMP1, EMP EMP2
WHERE DEPT.DEPT_NO = EMP2.DEPT_NO AND
   DEPT.DEPT_MNG = EMP1.EMP_NO AND
   EMP1.DEPT_NO IS NULL
GROUP BY DEPT.DEPT_NO;

Кстати, в этом случае, поскольку в запросе присутствует только один вызов агрегатной функции, формулировка без использования раздела GROUP BY оказывается более понятной и не менее эффективной (даже при следовании предписанной семантике выполнения оператора SELECT), что показывает пример 19.6.2:

SELECT DEPT.DEPT_NO, (SELECT COUNT(*)
   FROM EMP
   WHERE DEPT.DEPT_NO = EMP.DEPT_NO)
FROM DEPT, EMP
WHERE DEPT.DEPT_MNG = EMP.EMP_NO AND
      EMP.DEPT_NO IS NULL;

Предикат in

Пример 19.7. Найти номера отделов, в которых средний размер зарплаты служащих равен максимальному размеру зарплаты служащих какого-либо другого отдела.

SELECT DEPT.DEPT_NO
FROM DEPT, EMP
WHERE DEPT.DEPT_NO = EMP.DEPT_NO
GROUP BY DEPT.DEPT_NO
HAVING AVG(EMP.EMP_SAL) IN
   (SELECT MAX(EMP1.EMP_SAL)
     FROM EMP, DEPT DEPT1
     WHERE EMP.DEPT_NO = DEPT1.DEPT_NO
     AND DEPT1.DEPT_NO <> DEPT.DEPT_NO
     GROUP BY DEPT.DEPT_NO);

Этот запрос, помимо прочего, демонстрирует наличие в условии раздела HAVING вложенного подзапроса с корреляцией. Как и раньше, можно избавиться от разделов GROUP BY и HAVING во внешнем запросе (пример 19.7.1):

SELECT DEPT.DEPT_NO
FROM DEPT
WHERE (SELECT AVG(EMP_SAL)
   FROM EMP
   WHERE EMP.DEPT_NO = DEPT.DEPT_NO) IN
   (SELECT MAX(EMP1.EMP_SAL)
   FROM EMP, DEPT DEPT1
   WHERE EMP.DEPT_NO = DEPT1.DEPT_NO
     AND DEPT1.DEPT_NO <> DEPT.DEPT_NO
   GROUP BY DEPT.DEPT_NO);

Но в данном случае мы не можем отказаться от раздела GROUP BY во втором вложенном запросе, поскольку без этого невозможно получить множество значений результатов вызова агрегатной функции.

Предикат like

Пример 19.8. Во всех отделах найти имена и число служащих, у которых в данном отделе имеются однофамильцы и фамилии которых начинаются со строки символов, изображающей фамилию руководителя отдела.

SELECT EMP_NAME, COUNT(*)
FROM EMP, DEPT
WHERE EMP.DEPT_NO = DEPT.DEPT_NO 
GROUP BY DEPT.DEPT_NO, EMP_NAME
HAVING COUNT(*) > 1 
   AND EMP.EMP_NAME LIKE (SELECT EMP1.EMP_NAME
     FROM EMP EMP1
     WHERE EMP1.EMP_NO = DEPT.DEPT_MNG) || '%';

Конечно, и в этом случае условие с предикатом LIKE можно переместить из раздела HAVING в раздел WHERE. Этот запрос можно переформулировать в виде, лишенном разделов GROUP BY и HAVING (пример 19.8.1), но вряд ли это разумно, поскольку формулировка является менее понятной и существенно более сложной.

SELECT EMP_NAME, (SELECT COUNT(*)
   FROM EMP EMP1
   WHERE EMP1.DEPT_NO = EMP.DEPT_NO
     AND EMP1.EMP_NAME = EMP.EMP_NAME
     AND EMP1.EMP_NO <> EMP.EMP_NO) + 1
FROM EMP
WHERE (SELECT COUNT(*)
   FROM EMP EMP1
   WHERE EMP1.DEPT_NO = EMP.DEPT_NO
     AND EMP1.EMP_NAME = EMP.EMP_NAME
     AND EMP1.EMP_NO <> EMP.EMP_NO) > 1 
 AND EMP_NAME LIKE (SELECT EMP1.EMP_NAME
   FROM EMP EMP1, DEPT
   WHERE EMP.DEPT_NO = DEPT.DEPT_NO
   AND EMP1.EMP_NO = DEPT.DEPT_MNG) || '%';

Предикат exists

Пример 19.9. Найти номера отделов, в которых средний размер зарплаты служащих равен максимальному размеру зарплаты служащих какого-либо другого отдела (другая формулировка для примера 19.7).

SELECT DEPT.DEPT_NO
FROM DEPT, EMP
WHERE DEPT.DEPT_NO = EMP.DEPT_NO
GROUP BY DEPT.DEPT_NO
HAVING EXISTS (SELECT *
   FROM EMP EMP1
   WHERE EMP1.DEPT_NO <> DEPT.DEPT_NO
   GROUP BY EMP1.DEPT_NO 
   HAVING MAX (EMP1.EMP_SAL)= AVG (EMP.EMP_SAL));

В этой формулировке основной интерес представляет подзапрос, в котором корреляция с внешним запросом происходит через вызов агрегатной функции от группы строк внешнего запроса. Здесь также можно избавиться от разделов GROUP BY и HAVING во внешнем запросе (пример 19.9.1):

         
SELECT DEPT.DEPT_NO
FROM DEPT
WHERE EXISTS (SELECT EMP.DEPT_NO
   FROM EMP
   WHERE EMP.DEPT_NO <> DEPT.DEPT_NO
   GROUP BY EMP.DEPT_NO 
   HAVING MAX (EMP.EMP_SAL)= 
     (SELECT AVG (EMP1.EMP_SAL)
       FROM EMP EMP1
       WHERE EMP1.DEPT_NO = DEPT.DEPT_NO));

Предикат unique

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

SELECT DEPT.DEPT_NO, AVG (EMP.EMP_SAL)
FROM DEPT, EMP
WHERE DEPT.DEPT_NO = EMP.DEPT_NO
GROUP BY DEPT.DEPT_NO
HAVING UNIQIUE (SELECT AVG (EMP1.EMP_SAL)
   FROM EMP EMP1
   WHERE EMP1.DEPT_NO IS NOT NULL
   GROUP BY EMP1.DEPT_NO 
   HAVING AVG (EMP1.EMP_SAL) = AVG (EMP.EMP_SAL));

Вот альтернативная формулировка этого запроса с использованием предиката NOT EXISTS (пример 19.10.1):

SELECT DEPT.DEPT_NO, AVG (EMP.EMP_SAL)
FROM DEPT, EMP
WHERE DEPT.DEPT_NO = EMP.DEPT_NO
GROUP BY DEPT.DEPT_NO
HAVING NOT EXISTS (SELECT EMP1.DEPT_NO
   FROM EMP EMP1
   WHERE EMP1.DEPT_NO <> DEPT.DEPT_NO
   GROUP BY EMP1.DEPT_NO 
   HAVING AVG (EMP1.EMP_SAL)= AVG (EMP.EMP_SAL));

Предикаты сравнения с квантором

Пример 19.11. Найти номера отделов и средний возраст служащих для таких отделов, что найдется хотя бы один другой отдел, средний возраст служащих которого больше, чем в данном.

SELECT DEPT_NO, AVG (CURRENT_DATE – EMP_BDATE)
FROM EMP
WHERE DEPT_NO IS NOT NULL
GROUP BY DEPT_NO
HAVING AVG (CURRENT_DATE – EMP_BDATE)< SOME
   (SELECT AVG (CURRENT_DATE – EMP1.EMP_BDATE) 
     FROM EMP EMP1
     WHERE EMP1.DEPT_NO IS NOT NULL
     GROUP BY EMP1.DEPT_NO);

Напомним, что «ниладическая» функция CURRENT_DATE выдает текущую дату, и, следовательно, значением выражения CURRENT_DATE – EMP_BDATE является интервал, представляющий текущий возраст служащего. На наш взгляд, формулировка этого запроса несколько упрощается, если пользоваться предикат предикатом EXISTS (пример 19.11.1):

SELECT DEPT_NO, AVG (CURRENT_DATE – EMP_BDATE)
FROM EMP
WHERE DEPT_NO IS NOT NULL
GROUP BY DEPT_NO
HAVING EXISTS (SELECT EMP1.DEPT_NO
   FROM EMP EMP1
   WHERE EMP1.DEPT_NO IS NOT NULL
   GROUP BY EMP1.DEPT_NO
   HAVING AVG (CURRENT_DATE – EMP1.EMP_BDATE) >
     AVG (CURRENT_DATE – EMP.EMP_BDATE));

Пример 19.12. Найти номера отделов и средний возраст служащих для отделов с минимальным средним возрастом служащих.

SELECT DEPT_NO, AVG (CURRENT_DATE – EMP_BDATE)
FROM EMP
WHERE DEPT_NO IS NOT NULL
GROUP BY DEPT_NO
HAVING AVG (CURRENT_DATE – EMP_BDATE) <= ALL
   (SELECT AVG (CURRENT_DATE – EMP_BDATE)
     FROM EMP
     WHERE DEPT_NO IS NOT NULL
     GROUP BY DEPT_NO);

Этот запрос легко формулируется в более понятном виде с использованием предиката NOT EXISTS (пример 19.12.1):

SELECT DEPT_NO, AVG (CURRENT_DATE – EMP_BDATE)
FROM EMP
WHERE DEPT_NO IS NOT NULL
GROUP BY DEPT_NO
HAVING NOT EXISTS (SELECT EMP1.DEPT_NO
   FROM EMP EMP1
   WHERE EMP1.DEPT_NO IS NOT NULL
   GROUP BY EMP1.DEPT_NO
   HAVING AVG (CURRENT_DATE – EMP1.EMP_BDATE) <
     AVG (CURRENT_DATE – EMP.EMP_BDATE));

Предикат distinct

Пример 19.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);

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

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

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

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

Пример 19.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, например, следующим образом (пример 19.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);

А вот как можно сформулировать тот же запрос с использованием раздела WITH (пример 19.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);

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

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

Пример 19.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 (пример 19.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;

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

Пример 19.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 (пример 19.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;

Назад Содержание Вперёд