Лекция 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.