Лекция 14: Извлечение данных при помощи Transact-SQL
Предложение HAVING
Предложение HAVING применяется, чтобы задать условия поиска для групп или для агрегатной функции. Предложение HAVING чаще всего используется после предложения GROUP BY в случаях, когда условие поиска должно проверяться уже после группировки результатов. Если условие поиска можно было бы проверить до группировки, то гораздо эффективней было бы поместить его в предложение WHERE, а не пользоваться предложением HAVING (за счет этого уменьшилось бы количество строк, участвующих в группировке). Если предложение GROUP BY отсутствует, то HAVING может применяться только в отношении агрегатной функции в списке выборки. В этом случае предложение HAVING действует точно так же, как предложение WHERE. Если попытаться использовать HAVING как-нибудь по-другому, то SQL Server выдаст сообщение об ошибке.
Предложение HAVING имеет такой синтаксис:
HAVING <условие_поиска>
Здесь условие_поиска имеет такой же смысл, что и условие поиска. (См. раздел "Предложение WHERE и условие поиска" выше в данной лекции). Единственным различием между предложениями HAVING и WHERE является то, что предложение HAVING может содержать агрегатную функцию в условии поиска, а предложение WHERE – нет.
Примечание. Агрегатные функции можно применять в предложениях SELECT и HAVING, но не в предложении WHERE.Ниже приведен пример запроса, использующего предложение HAVING для поиска книг, сгруппированных по типам и по издательствам, средняя цена на которые превышает 15 долларов:
SELECT type, pub_id, AVG(price) AS "Средняя цена" FROM titles GROUP BY type, pub_id HAVING AVG(price) > 15.00 GO
В набор результатов попадут 4 строки:
type pub_id Средняя цена -------------------------------------------------- psychology 0877 21.59 trad_cook 0877 15.96 business 1389 17.31 popular_comp 1389 21.48
В предложениях HAVING можно употреблять логические операции. Ниже показан несколько измененный последний пример, в нем теперь применяется логическая операция AND:
SELECT type, pub_id, AVG(price) AS "Средняя цена"
FROM titles
GROUP BY type, pub_id
HAVING AVG(price) >= 15.00 AND
AVG(price) <= 20.00
GOВ набор результатов попадут 2 строки:
type pub_id Средняя цена -------------------------------------------------- trad_cook 0877 15.96 business 1389 17.31
Такой же результат получится, если вместо AND применить предложение BETWEEN, вот так:
SELECT type, pub_id, AVG(price) AS "Средняя цена" FROM titles GROUP BY type, pub_id HAVING AVG(price) BETWEEN 15.00 AND 20.00 GO
Чтобы применять HAVING без предложения GROUP BY, нужно иметь агрегатную функцию в списке выборки и в предложении HAVING. Например, чтобы выводить сумму цен на книги типа mod_cook (современная кулинария), только в тех случаях, когда эта сумма будет превышать 20 долларов, можно применять такой запрос:
SELECT SUM(price) FROM titles WHERE type = "mod_cook" HAVING SUM(price) > 20 GO
Если в этом запросе поместить выражение SUM(price) > 20 в предложение WHERE, то SQL Server выдаст сообщение об ошибке, т.к. в предложениях WHERE агрегатные функции применять нельзя.
Примечание. Помните, что предложение HAVING можно применять, только когда вы добавляете условие поиска, проверяющее результаты группировки при помощи предложения GROUP BY или проверяющее результат агрегатной функции. В остальных случаях условия поиска следует задавать в предложениях WHERE.Предложение ORDER BY
Предложение ORDER BY применяется, чтобы задать порядок, в котором должны сортироваться строки набора результатов. Пользуясь ключевыми словами ASC и DESC, вы можете задать как возрастающий (ascending, от меньших значений к большим), так и убывающий (descending, от больших значений к меньшим) порядок сортировки. Если порядок сортировки не указать, то по умолчанию будет применяться возрастающий порядок сортировки. В предложении ORDER BY можно задать более одной колонки. Результаты будут сортироваться по первой из заданных колонок, но если в первой колонке встретятся строки с одинаковыми значениями, то они будут сортироваться в порядке возрастания значения из второй колонки, и т.д. Как вы увидите из материала данного раздела, такая сортировка особенно полезна при использовании вместе с предложением GROUP BY. Давайте сначала рассмотрим пример, в котором предложение ORDER BY работает с одной колонкой и сортирует список авторов по фамилии , в возрастающем порядке:
SELECT au_lname, au_fname FROM authors ORDER BY au_lname ASC GO
Набор результатов будет отсортирован в алфавитном порядке по фамилиям авторов. Не забудьте, что чувствительность сортировки к регистру букв, заданная вами при инсталляции SQL Server, повлияет на обработку фамилий вроде "del Castillo".
Если вы хотите отсортировать результаты для более чем одной колонки, то просто добавьте в предложение ORDER BY имена колонок, разделяя их запятыми. Ниже приведен пример запроса, выдающего идентификаторы должностей, фамилии и имена сотрудников, сортирующего вывод сначала по идентификатору должности, затем по фамилии, а затем по имени:
SELECT job_id, lname, fname FROM employee ORDER BY job_id, lname, fname GO
Набор результатов (43 строки) будет выглядеть так:
job_id lname fname
-----------------------------------
2 Cramer Philip
3 Devon Ann
4 Chang Francisco
5 Henriot Paul
5 Hernadez Carlos
5 Labrune Janine
5 Lebihan Laurence
5 Muller Rita
5 Ottlieb Sven
5 Pontes Maria
6 Ashworth Victoria
6 Karttunen Matti
6 Roel Diego
6 Roulet Annette
7 Brown Lesley
7 Ibsen Palle
7 Larsson Maria
7 Nagy Helvetius
. . .
. . .
. . .
13 Accorti Paolo
13 O’Rourke Timothy
13 Schmitt Carine
14 Afonso Pedro
14 Josephs Karin
14 Lincoln ElizabethСортировка по именам (не фамилиям) людей в этом запросе не влияет на набор результатов, потому что там нет даже двух людей с одинаковыми фамилиями и идентификаторами должности.
А теперь давайте рассмотрим предложение ORDER BY, работающее совместно с предложением GROUP BY и агрегатной функцией:
SELECT type, pub_id, AVG(price) AS "Средняя цена" FROM titles GROUP BY type, pub_id ORDER BY Средняя цена GO
Набор результатов (8 строк) будет выглядеть так:
type pub_id Средняя цена ------------------------------------------------- UNDECIDED 0877 NULL business 0736 2.99 business 1389 17.31 mod_cook 0877 11.49 popular_comp 1389 21.48 psychology 0736 11.48 psychology 0877 21.59 trad_cook 0877 15.96
Результаты отсортированы в алфавитном порядке (возрастающем) по типу книг. Также обратите внимание, что в этом запросе в предложении GROUP BY должны присутствовать и type, и pub_id, потому что они не являются частью агрегатной функции. Если вы не зададите колонку pub_id в предложении GROUP BY, то SQL Server выдаст сообщение об ошибке (рис. 14.4).
В предложении ORDER BY нельзя применять агрегатные функции и подзапросы. Однако если вы зададите алиас агрегатной функции в предложении SELECT, то его применить можно в предложении ORDER BY, вот так:
SELECT type, pub_id, AVG(price) AS "Средняя цена" FROM titles GROUP BY type, pub_id ORDER BY type GO
увеличить изображение
Рис. 14.4. Сообщение об ошибке, которое появится, если не задать pub_id в предложении GROUP BY
Набор результатов (8 строк) будет выглядеть так:
type pub_id Средняя цена ------------------------------------------------- UNDECIDED 0877 NULL business 0736 2.99 business 1389 17.31 mod_cook 0877 11.49 popular_comp 1389 21.48 psychology 0736 11.48 psychology 0877 21.59 trad_cook 0877 15.96
Теперь результаты будут отсортированы по средней цене, причем NULL рассматривается как самое маленькое из значений, поэтому оно отображается в самой верхней строке списка.
Примечание. Точные результаты применения предложения ORDER BY зависят от настроек сортировки, заданных при инсталляции SQL Server.