Итак, наконец, мы почти приблизились к завершению оператора SELECT! Еще немного и можно будет сказать, что мы все обсудили! Но, а пока рассмотрим, как выбрать данные при группировке, то есть выводить не все данные, а только те, которые нас интересуют. Ранее для отбора строк по условию мы пользовались выражением WHERE. Для отбора групп по условию существует оператор HAVING. Его синтаксис аналогичен выражению WHERE и мало того их можно использовать вместе! Давайте рассмотрим следующий запрос:
Какова средняя стоимость заказа для каждого служащего из числа тех, у которых общая стоимость заказов превышает $300?
SELECT REP, AVG(AMOUNT) FROM ORDERS GROUP BY REP HAVING SUM(AMOUNT) > 300 /
Вот и результат:
SQL> SELECT REP, AVG(AMOUNT) 2 FROM ORDERS 3 GROUP BY REP 4 HAVING SUM(AMOUNT) > 300 5 / REP AVG(AMOUNT) --------------------------------------- ----------- 103 301,22 105 148,125 107 228,5933333 108 311,2064285 110 327,25 5 rows selected
Как видно выражение HAVING SUM(AMOUNT) > 300 сработало как условие при группировке строк! А теперь посмотрим на рисунок.
Здесь видно, если условие SUM(AMOUNT) > 300 ложно, то эта группа из результирующего набора отбрасывается. Если истинно, то группа попадает в результирующий набор! Ничего сложного!
Давайте рассмотрим еще один пример: Для каждого офиса, в котором работают два и более человек, вычислить общий плановый и фактический объем продаж для всех служащих офиса.
SELECT CITY, SUM(QUOTA), SUM(SALESREPS.SALES) FROM OFFICES, SALESREPS WHERE OFFICE = REP_OFFICE GROUP BY CITY HAVING COUNT(*) >= 2 /
Получаем:
SQL> SELECT CITY, SUM(QUOTA), SUM(SALESREPS.SALES) 2 FROM OFFICES, SALESREPS 3 WHERE OFFICE = REP_OFFICE 4 GROUP BY CITY 5 HAVING COUNT(*) >= 2 6 / CITY SUM(QUOTA) SUM(SALESREPS.SALES) ------------------------------ ---------- -------------------- Контрогайка 700 835,915 Красный Мотоцикл 575 692,637 Чугуевск 1175 1121,084
Здесь хорошо видно, что присутствуют оба выражения и WHERE и HAVING, каждый выполняет свою функцию в запросе. Обратите так же внимание, что в предложении HAVING используются агрегатные функции при формировании условия поиска! Давайте опишем, как этот запрос выполняется:
Вот так строится этот запрос, если вы все это поняли. Замечательно. Тогда посмотрите кое что посложнее:
Показать цену, количество на складе и общее количество заказанных единиц для каждого наименования товара, если для него общее количество заказанных единиц превышает 75 процентов от количества товара на складе.
SELECT DESCRIPTION, PRICE, QTY_ON_HAND, SUM(QTY) FROM PRODUCTS, ORDERS WHERE MFR = MFR_ID GROUP BY MFR_ID, PRODUCT_ID, DESCRIPTION, PRICE, QTY_ON_HAND HAVING SUM(QTY) > (0.75 * QTY_ON_HAND) ORDER BY QTY_ON_HAND DESC /
Получаем следующий набор:
SQL> SELECT DESCRIPTION, PRICE, QTY_ON_HAND, SUM(QTY) 2 FROM PRODUCTS, ORDERS 3 WHERE MFR = MFR_ID 4 GROUP BY MFR_ID, PRODUCT_ID, DESCRIPTION, PRICE, QTY_ON_HAND 5 HAVING SUM(QTY) > (0.75 * QTY_ON_HAND) 6 ORDER BY QTY_ON_HAND DESC 7 / DESCRIPTION PRICE QTY_ON_HAND SUM(QTY) ------------------ ---------- -------------- ---------- Лампа настольная 55 277 223 Рейка деревянная 107 207 223 Носки черные 76 167 223 Рейка пластмассовая 117 139 223 Труба алюминиевая 355 38 32 Карандаш простой 25 37 223 Подушка ватная 177 37 32 Нож специальный 475 32 30 Монитор LG 2500 28 223 Наушники SONY 975 28 30 Коробка картонная 2,75 25 223 Рубероид рулоны 250 24 30 Электродвигатель 243 15 26 Бочка пластмассовая 350 14 60 Доска профильная 4500 12 60 Телевизор SAMSUNG 4500 12 60 Профиль специальный 1875 9 30 Осветитель ртутный 1425 5 30 Тарелка фарфоровая 180 0 2 19 rows selected
А вот здесь, ничего разжевывать не буду, это вам задание на дом! Напишите план запроса и пришлите на мой ящик, а я посмотрю кто как понимает данный материал! :) Ограничения, накладываемые на предложение HAVING те же, что и ограничения для запросов с группировкой, которые мы рассматривали в предыдушем шаге, но есть ряд дополнений. Предложение HAVING должно содержать как минимум одну агрегатную функцию, если это не так, то лучше применять предложение WHERE. Так как предложение WHERE применимо к отдельным строкам, а предложение HAVING к группам строк и об этом не стоит забывать! А, остальное все как обычно! :) Да, что касается NULL в условии поиска HAVING, они точно такие же, как и для предложения WHERE, т.е. "Если условие поиска имеет значение NULL, группа строк исключается и строка в результатах запроса для нее не генерируется". К слову предложение HAVING, в принципе можно применять и без GROUP BY, но тогда результат запроса рассматривается как одна группа состоящая из всех строк. На практике это применяется довольно редко. Вот так работает HAVING и все, что к нему прилагается!