Таблица PEOPLE

Хотя такой принцип обработки значений null определен в стандарте SQL, он реализован не во всех диалектах SQL. Прежде чем рассчитывать на определенные результаты, следует протестировать свою СУБД.

Условия отбора групп (предложение HAVING)

Точно так же, как предложение where используется для отбора отдельных строк, участвующих в запросе, предложение having можно применить для отбора групп строк. Его формат соответствует формату предложения where. Предложение having состоит из ключевого слова having, за которым следует условие отбора. Таким образом, данное предложение определяет условие отбора для групп строк.

Следующий пример прекрасно иллюстрирует роль предложения having:

Какова средняя стоимость заказа для каждого служащего из числа тех, у которых общая стоимость заказов превышает $30000?

SELECT REP, AVG(AMOUNT) FROM ORDERS GROUP BY REP HAVING SUM(AMOUNT) > 30000.00

AVG(AMOUNT)

105 106 108

$8,895.50 $16,479.00 $8,577.83

На рис. 8.5 изображена схема выполнения запроса. Вначале предложение group by разделяет заказы на группы по служащим. После этого предложение having исключает все группы, в которых общая стоимость заказа не превышает $30000., И наконец, предложение select вычисляет среднюю стоимость заказа для каждой из оставшихся групп и генерирует таблицу результатов запроса.

Таблица OROERS

Таблица с группировкой

OROER NUH

13042

112961 112989

113051 113045 113013 113007 112992 113049

AMOUNT

22.500.00

$31,500.00 $1.458.00

$1.420.00 $45,000.00 $652.00

$2,925.00 $760.00 $710.00

. ЛзиИСАМОиНТ) -*\> $30,000?

Результаты запроса

ЧзиисАИоинтД > $30, ООО? J-

TRUE

В предложении having указываются точно такие же условия отбора, что и в предложении where, как описано в главах 6 и 9. Ниже приведен еще один пример использования условия для отбора групп:

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

SELECT CITY, SUM(QDOTA), SUM(SALESREPS.SALES) FROM OFFICES, SALESREPS

WHERE OFFICE = REP OFFICE

GROUP BY CITY HAVING COUNT(*) >= 2

CITY

SUM(QUOTA)

Chicago $775,000.00

Los Angeles $700,000.00 New York $575,000.00

SUM(SALESREPS.SALES)

$735,042.00 $835,915.00 $692,637.00

В табл. 8.3 приведены правила выполнения SQL-запроса на выборку, расширенные с учетом условий отбора групп. В соответствии с этими правилами СУБД выполняет приведенный выше запрос таким образом:

1. Объединяет таблицы offices и salesreps, чтобы определить город, в котором работает служащий.

2. Группирует строки объединенной таблицы по офисам.

3. Исключает группы, содержащие две или менее строки, - это те группы, которые не удовлетворяют критерию предложения having.

4. Вычисляет общие плановые и фактические объемы продаж для каждой группы.

8.3: Пращнпайыпа1рЪМ101-з11а Шз-шфрку (счетом предложения HAVING)

Таблица результатов запроса на выборку генерируется следующим образом;

1. Если запрос представляет собой запрос на объединение (union) инструкций select, для каждой из этих инструкций выполнить действия 2-7 и получить отдельную таблицу результатов.

2. Сформировать произведение таблиц, перечисленных в предложении from. Если в предложении from указана только одна таблица, то произведением будет она сама.

3. Если имеется предложение where, применить заданное в нем условие отбора к каждой строке таблицы произведения и оставить в ней только те строки, для которых это условие выполняется, т.е. имеет значение true; строки, для которых условие отбора имеет значение false или null, - отбросить.

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

5. Если имеется предложение having, применить заданное в нем условие отбора к каждой группе строк и оставить в таблице произведения только те группы, для которых это условие выполняется, т.е. имеет значение true; группы, для которых условие отбора имеет значение false или null, - отбросить.

6. Для каждой из оставшихся строк (или для каждой группы строк) вычислить значение каждого элемента в списке возвращаемых столбцов и создать одну строку в таблице результатов запроса. При любой ссылке на столбец берется значение столбца для текущей строки (или группы строк). В качестве аргумента статистической функции используются значения столбца из всех строк, входящих в группу, если указано предложение group by; в противном случае используются значения столбца из всех строк таблицы результатов.

7. Если указан предикат distinct, удалить из таблицы результатов запроса все повторяющиеся строки.

8. Если запрос является запросом на объединение (union) инструкций select, объединить результаты выполнения отдельных инструкций в одну таблицу результатов запроса. Удалить из нее повторяющиеся строки, если не указан предикат all.

9. Если имеется предложение order by, отсортировать результаты запроса.

Вот еще один пример, в котором используются все предложения инструкции

select;

Показать цену, количество на складе и общее количество заказанных единиц д.пя каждого наименования товара, если для него общее количество заказанных единиц превышает 75 процентов от количества товара на складе.

SELECT DESCRIPTION, PRICE, QTY ON HAND, SOM(QTY) FROM PRODUCTS, ORDERS WHFRE MFR = MFR ID