SQL HAVING - получение данных с интересующими значениями агрегатных функций

Оператор SQL HAVING и сравнение с заданным числом
Оператор SQL HAVING и сравнение со значением, возвращаемым квантором ALL или ANY (SOME)
Оператор SQL HAVING в соединениях таблиц
Написать запросы с оператором SQL HAVING самостоятельно, а затем посмотреть решения

Оператор SQL HAVING наиболее часто применяется вместе с оператором GROUP BY для получения данных из таблицы базы, соответствующих определённым значениям результатов, возвращаемых агрегатными функциями. В случае такого применения HAVING результатом будут строки, соответствующие всем группам, в которых выполняется условие сравнения со значением агрегатной функции.

Оператор SQL HAVING и сравнение с заданным числом

Синтаксис запроса с оператором SQL HAVING, определяющий сравнение с некоторым значением, в случае сравнения с явно заданным числом выглядит следующим образом:.

SELECT ИМЕНА_СТОЛБЦОВ FROM ИМЯ_ТАБЛИЦЫ GROUP BY ИМЯ_СТОЛБЦА HAVING АГРЕГАТНАЯ_ФУНКЦИЯ(ИМЯ СТОЛБЦА) ОПЕРАТОР_СРАВНЕНИЯ ЗАДАННОЕ ЧИСЛО

Следует особо заметить, что оператор HAVING всегда находится в запросе после оператора GROUP BY.

Пример 1. Есть база данных портала объявлений. В ней есть таблица Ads, содержащая данные об объявлениях, поданных за неделю. Столбец Category содержит данные о больших категориях объявлений (например, Недвижимость), а столбец Parts - о более мелких частях, входящих в категории (например, части Квартиры и Дачи являются частями категории Недвижимость). Столбец Units содержит данные о количестве поданных объявлений, а столбец Money - о денежных суммах, вырученных за подачу объявлений.

Category	Part	Units	Money
Транспорт	Автомашины	110	17600
Недвижимость	Квартиры	89	18690
Недвижимость	Дачи	57	11970
Транспорт	Мотоциклы	131	20960
Стройматериалы	Доски	68	7140
Электротехника	Телевизоры	127	8255
Электротехника	Холодильники	137	8905
Стройматериалы	Регипс	112	11760
Досуг	Книги	96	6240
Недвижимость	Дома	47	9870
Досуг	Музыка	117	7605
Досуг	Игры	41	2665

Используя операторы SQL HAVING и GROUP BY, определить категории, в которых за подачу объявлений выручено более 20000. Пишем следующий запрос:

SELECT Category, SUM(Money) AS Money FROM Ads GROUP BY Category HAVING SUM(Money)>20000

Результатом выполнения запроса будет следующая таблица:

Category	Money
Недвижимость	40530
Транспорт	38560

Пример 2. База данных и таблица - те же, что и в примере 1.

Используя операторы SQL HAVING и GROUP BY, требуется вывести категории, в какой-либо части которых минимальное количество поданных объявлений не превышает 100. Для этого пишем следующий запрос:

SELECT Category, Units, MIN(Units) AS Minimum FROM Ads GROUP BY Category HAVING MIN(Units)<100

Результатом будет следующая таблица:

Category	Part	Minimum
Досуг	Игры	41
Недвижимость	Дома	47
Стройматериалы	Доски	68

Пример 3. База данных и таблица - те же, что и в предыдущих примерах.

Используя операторы SQL HAVING и GROUP BY, требуется вывести категории объявлений, в которых более двух частей. Пишем следующий запрос:

SELECT Category, Part FROM Ads GROUP BY Category HAVING COUNT(*)>2

Результатом будет следующая таблица:

Category	Part
Досуг	Книги
Досуг	Музыка
Досуг	Игры
Недвижимость	Квартиры
Недвижимость	Дачи
Недвижимость	Дома

Оператор SQL HAVING и сравнение со значением, возвращаемым квантором ALL или ANY (SOME)

Оператор SQL HAVING можно использовать для выборки данных, соответствующим результатам сравнения не только с заданным числом, но и со значением, возвращаемым квантором ALL или ANY (SOME). Квантор ALL возвращает из запроса, к которому он применяется, максимальное значение и тогда при помощи оператора HAVING происходит сравнение с максимальным значением. Например, ALL(10, 15, 20) вернёт 20. Квантор ANY (и его аналог SOME) возвращает минимальное значение и тогда при помощи оператора HAVING происходит сравнение с минимальным значением. Синтаксис запроса с оператором SQL HAVING, определяющий сравнение со значением, возвращаемым квантором ALL или ANY (SOME) выглядит следующим образом:.

SELECT ИМЕНА_СТОЛБЦОВ FROM ИМЯ_ТАБЛИЦЫ GROUP BY ИМЯ_СТОЛБЦА HAVING АГРЕГАТНАЯ_ФУНКЦИЯ(ИМЯ СТОЛБЦА) ОПЕРАТОР_СРАВНЕНИЯ КВАНТОР (SELECT АГРЕГАТНАЯ_ФУНКЦИЯ(ИМЯ СТОЛБЦА) FROM ИМЯ_ТАБЛИЦЫ GROUP BY ИМЯ_СТОЛБЦА)

Пример 4. Есть база данных "Театр". В ней есть таблица Play, содержащая данные о постановках в театре. В этой таблице есть поля PlayID (идентификатор), Name (название), Genre (жанр), Author (автор), Dir_ID (внешний ключ - идентификатор режиссёра), PremiereDate (дата премьеры), LastDate (дата окончания). Требуется определить самый популярный жанр театра, то есть жанр, в котором поставлено наибольшее количество постановок.

Используя операторы SQL HAVING и GROUP BY, пишем первую часть запроса к таблице Play, которая задаёт сравнение числа строк, сгруппированных по жанру:

SELECT Genre FROM Play GROUP BY Genre HAVING COUNT(*) >=

Теперь нужно определить, с чем сравнивать. Это максимальное число записей в той же таблице, сгруппированных по жанру. Поэтом нам понадобиться квантор ALL. Пишем вторую часть запроса:

ALL(SELECT COUNT(*) FROM PLAY GROUP BY Genre)

Весь запрос для определения самого популярного жанра в театре будет следующим:

SELECT Genre FROM Play GROUP BY Genre HAVING COUNT(*) >= ALL(SELECT COUNT(*) FROM PLAY GROUP BY Genre)

Оператор SQL HAVING в соединениях таблиц

Вернёмся к запросам с оператором SQL HAVING, в которых сравнение проводится с заданным числом, как в первом параграфе. Но усложним задачу. На практике часто число строк в результате запроса определяется внешним ключом, ссылающимся на другую таблицу..

Пример 5. Продолжаем работать с базой данных "Театр". Нам понадобятся таблицы Play, содержащая данные о постановках в театре и Team, содержащая данные о ролях актёров. Требуется вывести список моноспектаклей (спектаклей с одним актёром). Ниже приведена схема базы данных "Театр" (для увеличения рисунка нажать на него левой кнопкой мыши).

Если ещё точнее, нам нужно выбрать спектакли, в которых лишь одна роль. Среди полей в таблице Team есть PlayID - внешний ключ, ссылающийся на таблицу Play. В каждой записи таблицы Team этот внешний ключ определяет постановку, в которой исполняется данная роль. Если мы соединим таблицы Play и Team по ключу PlayID, то мы сможем определить количество ролей в постановках. Так как мы соединяем две таблицы, а не больше, то для простоты можем использовать соединение без оператора JOIN, перечисляя таблицы через запятую, а для обозначения условия соединения используя слово WHERE.

C оператором HAVING используем агрегатную функцию COUNT - для подсчёта числа ролей в каждой постановке. Весь запрос для определения спектаклей с одной ролью, а следовательно, с одним актёром будет следующим:

SELECT Play.Name AS pname FROM Play, Team WHERE Team.PLAY_ID = Play.Play_ID GROUP BY Play.Name HAVING COUNT(Team.PLAY_ID) = 1 ORDER BY Play.Name

Написать запросы с оператором SQL HAVING самостоятельно, а затем посмотреть решения

Пример 6. Продолжаем работать с базой данных "Театр". Вывести список актеров, которые в одном спектакле играют более одной роли, и количество их ролей.

Использовать оператор JOIN. Естественно, использовать HAVING, GROUP BY.

Правильное решение и ответ.

Пример 7. Вывести спектакли, в которых средний возраст актеров от 20 до 30 (использовать BETWEEN, Group by, Having, AVG, перекрестное соединение таблиц (CROSS JOIN), удобнее без слова JOIN, а с перечислением таблиц через запятую).

Правильное решение и ответ.

Поделиться с друзьями

Другие темы в блоке "Реляционные базы данных"

Реляционная модель данных

Основы проектирования базы данных - пример

Реляционная алгебра

Создание базы данных SQL

SQL ALTER TABLE - изменение таблицы базы данных

SQL INSERT, UPDATE, DELETE - вставка, изменение и удаление данных

SQL SELECT - запрос на выборку данных

SQL JOIN - соединение таблиц базы данных

Агрегатные функции SQL - SUM, MIN, MAX, AVG, COUNT

SQL UNION - объединение результатов запросов