Группировка и условия раздела HAVING, порождаемые и соединенные таблицы

Ссылки на порождаемые таблицы в разделе 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);

В этом запросе порождаемая таблица MNG содержит по одной строке для каждого служащего, являющегося руководителем отдела. Первый столбец этой таблицы - DEPT_NO_1 - содержит номер отдела, которым руководит данный служащий. В столбце DEPT_NO_2 хранятся номера отделов, в которых числятся руководители отделов, а в столбцах 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);

А вот как можно сформулировать тот же запрос с использованием раздела 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 демонстрирует лишь возможность альтернативных формулировок запросов с использованием ссылок на порождаемые таблицы в разделе 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;

И в этом случае выражение запросов, содержащееся в разделе 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;

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

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