Язык запросов SQL

         

Коррелированные подзапросы перед



Коррелированные подзапросы, перед которыми стоит ключевое слово 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.

Содержание раздела