Случаи, в которых без порождаемых таблиц обойтись невозможно
На самом деле, пример 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)