SQL HAVING - получение данных с интересующими значениями
Компьютеры
и программирование

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

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

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

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

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

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

CategoryPartUnitsMoney
ТранспортАвтомашины11017600
НедвижимостьКвартиры8918690
НедвижимостьДачи5711970
ТранспортМотоциклы13120960
СтройматериалыДоски687140
ЭлектротехникаТелевизоры1278255
ЭлектротехникаХолодильники1378905
СтройматериалыРегипс11211760
ДосугКниги966240
НедвижимостьДома479870
ДосугМузыка1177605
ДосугИгры412665

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

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

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

CategoryMoney
Недвижимость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

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

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

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

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

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

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

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

Оператор 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

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

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