Иллюстрированный самоучитель по SQL для начинающих

Использование вложенных запросов

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

SELECT BONUS_PCT
FROM BONUSRATE
WHERE MIN_AMOUNT <=
(SELECT SUM (NET_AMOUNT)
FROM TRANSMASTER
WHERE EMPID = 133)
AND MAX_AMOUNT >=
(SELECT SUM (NET_AMOUNT)
FROM TRANSMASTER
WHERE EMPID =133);

Этот запрос интересен тем, что в нем содержатся два подзапроса, для которых приходится использовать логическую связку AND. В подзапросах применяется итоговый оператор SUM, и он возвращает единственное значение – общую сумму продаж за месяц для сотрудника с идентификационным номером 133. Затем это значение сравнивается со значениями в столбцах MIN_AMOUNT и MAX_AMOUNT из таблицы BONUSRATE, и в результате получается процент премии для этого сотрудника.

Если идентификатор продавца, хранящийся в столбце EMPID, вам не известен, но известна фамилия, то такой же ответ можно получить, используя более сложный запрос:

SELECT BONUS_PCT
FROM BONUSRATE
WHERE MIN_AMOUNT <=
(SELECT SUM (NET_AMOUNT)
FROM TRANSMASTER WHERE EMPID =
(SELECT EMPID
FROM EMPLOYEE
WHERE EMPLNAME = 'Coffin'))
AND MAX_AMOUNT >=
(SELECT SUM (NET_AMOUNT)
FROM TRANSMASTER WHERE EMPID =
(SELECT EMPID
FROM EMPLOYEE
WHERE EMPLNAME = 'Coffin'));

В этом примере, чтобы получить процент премии для сотрудника по фамилии Коффин, используются подзапросы, вложенные в другие подзапросы, а те, в свою очередь, вложены в замыкающий запрос. Эта структура работает только тогда, когда вам наверняка известно, что в компании работает один-единственный сотрудник с этой фамилией. А если вы знаете, что имеются несколько сотрудников с фамилией Коффин? Тогда в предложение WHERE из подзапроса самого нижнего уровня можно добавлять все новые и новые условия, пока не появится уверенность, что будет выбрана единственная строка таблицы EMPLOYEE.

Подзапросы в предложении HAVING

Коррелированный подзапрос можно задавать не только в предложении WHERE, но и в предложении HAVING. Как уже говорилось в главе 9, перед этим предложением обычно находится предложение GROUP BY. Предложение HAVING действует как фильтр, который должен ограничивать группы, созданные предложением GROUP BY. Группы, которые не удовлетворяют условию предложения HAVING, в результат не попадут.

Если предложение HAVING используется таким образом, то оно проверяется для каждой группы, созданной предложением GROUP BY. Если же предложения GROUP BY нет, то предложение HAVING проверяется для всего набора строк, переданного предложением WHERE. Тогда этот набор считается одной группой. А если нет ни предложения WHERE, ни предложения GROUP BY, то условие предложения HAVING проверяется уже для всей таблицы:

SELECT TM1.EMPID
FROM TRANSMASTER TM1
GROUP BY TM1.EMPID
HAVING MAX (TM1.NET_AMOUNT) >= ALL
(SELECT 2 * AVG (TM2.NET_AMOUNT)
FROM TRANSMASTER TM2
WHERE TM1.EMPID <> TM2.EMPID);

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

1. Строки таблицы TRANSMASTER группируются внешним запросом по значениям столбца EMPID. Это делается с помощью предложений SELECT, FROM и GROUP BY.
2. Получившиеся группы фильтруются предложением HAVING. В нем для каждой из групп вычисляется (с помощью функции МАХ) максимум значений из столбца NET_AMOUNT, которые находятся в строках этой группы.
3. Внутренний запрос дважды проверяет среднее значение NET_AMOUNT для всех тех строк, в которых значения столбца EMPID отличаются от значения этого столбца в текущей группе внешнего запроса. Обратите внимание, что в последней строке запроса приходится указывать два значения, взятые из разных EMPID. Поэтому в предложениях FROM из внешнего и внутреннего запросов приходится для таблицы TRANSMASTER указывать два разных псевдонима.
4. Эти псевдонимы затем используются в сравнении, расположенном в последней строке запроса. Цель их использования состоит в том, чтобы показать – обращение должно идти к значению столбца ЕМРID из текущей строки внутреннего подзапроса (ТМ2.ЕМРID), а также к значению того же столбца, но на этот раз из текущей группы внешнего подзапроса (TM1.EMPID).