Базы данных
и язык SQL
Форум
Тесты онлайн

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

Навигация по уроку Связанные темы
Назад<<<СодержаниеВперёд>>>

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

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

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

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

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

Если вы хотите выполнить запросы к базе данных из этого урока на MS SQL Server, но эта СУБД не установлена на вашем компьютере, то ее можно установить, пользуясь инструкцией по этой ссылке.

В первых примерах работаем с базой данных "Портал объявлений - 1". Скрипт для создания этой базы данных, её таблицы и заполения таблицы данных - в файле по этой ссылке.

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

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

Пример 1. Используя операторы 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
ДосугКниги
ДосугМузыка
ДосугИгры
НедвижимостьКвартиры
НедвижимостьДачи
НедвижимостьДома

Примеры запросов к базе данных "Портал объявлений-1" есть также в уроках об операторах INSERT, UPDATE, DELETE и UNION.

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

Самостоятельно поработайте с таблицей Staff (штатные сотрудники) базы данных фирмы. В ней есть столбцы Name (фамилия), Dept (номер отдела), Years (длительность трудового стажа) и Salary (размер заработной платы). Примеры для самостоятельного решения со ссылками для проверки решения - после таблицы.

NameDeptYearsSalary
Sanders20718357.5
Junkers15616232.8
Moonlight15821500.6
Pernal20818171.2
Aisen15719540.7
McGregor15715790.8
Marenghi38517506.8
Doctor20512322.4
Factor38816228.7

Пример 4. Определить номера отделов, в которых средний размер заработной платы больше 18000.

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

Пример 5. Определить номера отделов, в которых средний трудовой стаж сотрудников больше 6.5 лет.

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

Пример 6. Определить номера отделов, в которых число сотрудников меньше 3.

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

Оператор 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 ИМЯ_СТОЛБЦА)

Пример 7. Есть база данных "Театр". В ней есть таблица 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, в которых сравнение проводится с заданным числом, как в первом параграфе. Но усложним задачу. На практике часто число строк в результате запроса определяется внешним ключом, ссылающимся на другую таблицу..

Пример 8. Продолжаем работать с базой данных "Театр". Нам понадобятся таблицы 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 самостоятельно, а затем посмотреть решения

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

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

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

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

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

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

Назад<<<СодержаниеВперёд>>>