Коррелированные подзапросы перед
Коррелированные подзапросы, перед которыми стоит ключевое слово IN
Выше, в разделе "Подзапросы, перед которыми стоит ключевое слово IN", рассказывалось, каким образом некоррелированный подзапрос можно использовать вместе с предикатом IN. А чтобы увидеть, каким образом этот предикат может использоваться, наоборот, коррелированным подзапросом, задайте тот же самый вопрос, что и в случае с предикатом EXISTS. Итак, какие фамилии и телефонные номера у представителей для контакта во всех организациях-покупателях продукции Zetec в Калифорнии? Ответ можно получить с помощью коррелированного подзапроса с IN:
SELECT *
FROM CONTACT
WHERE 'CA' IN
(SELECT CustState
FROM CUSTOMER
WHERE CONTACT.CustID = CUSTOMER.CustID) ;
Оператор выполняется с каждой записью таблицы CONTACT. Если значение столбца CustID этой записи совпадает с соответствующим значением столбца таблицы CUSTOMER, то значение CUSTOMER.CustState сравнивается со значением 'СА. Результатом выполнения подзапроса является список, в котором содержится не более одного элемента. Ваш этот единственный элемент представляет собой 'СА', то выполняется условие предложения WHERE из замыкающего оператора и строка добавляется в выводимую запросом таблицу.
Коррелированные подзапросы, перед которыми стоят операторы сравнения
Как будет показано в следующем примере, перед коррелированным подзапросом может стоять также любой из шести операторов сравнения.
Компания Zetec выплачивает каждому своему продавцу премию, которая зависит от общей суммы, вырученной им от продаж за месяц. Чем выше эта сумма, тем выше процент премии. Список этих процентов хранится в таблице BONUSRATE (ставка премии) со столбцами MIN_AMOUNT (нижняя граница), МАХ_AMOUNT (верхняя граница) (процент премии).
| MIN_AMOUNT | MAX_AMOUNT | BONUS_PCT |
| ---------------- | ---------------- | -------------- |
| 0.00 | 24999.99 | 0 |
| 25000.00 | 49999.99 | 0.001 |
| 50000.00 | 99999.99 | 0.002 |
| 100000.00 | 249999.99 | 0.003 |
| 250000 .00 | 499999.99 | 0.004 |
| 500000.00 | 749999.99 | 0.005 |
| 750000.00 | 999999.99 | 0.006 |
Если у продавца ежемесячная сумма продаж составляет 100000-249999,99 долл., то он получает премию в размере 0,3% от этой суммы.
Продажи записываются в главную таблицу сделок TRANSMASTER.
| TRANSMASTER | ||
| ----------------- | ||
| Столбец | Тип | Ограничения |
| --------- | ---- | ---------------- |
| TRANSID (идентификатор сделки) |
INTEGER | PRIMARY KEY |
| CUSTID (идентификатор покупателя) |
INTEGER | FOREIGN KEY |
| EMPID (идентификатор сотрудника) |
INTEGER | FOREIGN KEY |
| TRANSDATE (дата сделки) |
DATE | |
| NET_AMOUNT (облагаемая налогом сумма) |
NUMERIC | |
| FREIGHT (стоимость перевозки) |
NUMERIC | |
| TAX (налог) |
NUMERIC | |
| INVOICETOTAL (итоговая сумма счета-фактуры) |
NUMERIC |
Премии начисляются на основе суммы значений из столбца 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.
