Oracle PL/SQL •MySQL •SQL Server. Sql group by having описание
SQL HAVING - получение данных с интересующими значениями
SELECT ИМЕНА_СТОЛБЦОВ FROM ИМЯ_ТАБЛИЦЫ GROUP BY ИМЯ_СТОЛБЦА HAVING АГРЕГАТНАЯ_ФУНКЦИЯ(ИМЯ СТОЛБЦА) ОПЕРАТОР_СРАВНЕНИЯ ЗАДАННОЕ ЧИСЛО
Пример 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
Результатом выполнения запроса будет следующая таблица:
| 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)Результатом будет следующая таблица:
| 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). Квантор 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, в которых сравнение проводится с заданным числом, как в первом параграфе. Но усложним задачу. На практике часто число строк в результате запроса определяется внешним ключом, ссылающимся на другую таблицу..
Пример 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
Поделиться с друзьями
Другие темы в блоке "Реляционные базы данных"
function-x.ru
HAVING оператор MySQL | Oracle PL/SQL •MySQL •SQL Server
В этом учебном пособии вы узнаете, как использовать MySQL оператор HAVING с синтаксисом и примерами.
Описание
MySQL оператор HAVING используется в сочетании с оператором GROUP BY, чтобы ограничить группы возвращаемых строк только тех, чье условие TRUE.
Синтаксис
Синтаксис оператора HAVING в MySQL:
SELECT expression1, expression2, … expression_n,aggregate_function (expression)FROM tables[WHERE conditions]GROUP BY expression1, expression2, … expression_nHAVING condition;
Параметры или аргументы
aggregate_function — функция, такая как функции SUM, COUNT, MIN, MAX или AVG.expression1, expression2, … expression_n — выражения, которые не заключены в агрегированную функцию и должны быть включены в предложение GROUP BY.WHERE conditions — необязательный. Это условия для выбора записей.HAVING condition — Это дополнительное условие применяется только к агрегированным результатам для ограничения групп возвращаемых строк. В результирующий набор будут включены только те группы, состояние которых соответствует TRUE.
Пример использования функции SUM
Рассмотрим пример MySQL оператора HAVING, в котором используется функция SUM.
Вы также можете использовать функцию SUM, чтобы вернуть имя product и общее количество (для этого product). MySQL оператор HAVING будет фильтровать результаты так, чтобы возвращались только product с общим количеством больше 10.
SELECT product, SUM(quantity) AS "Total quantity" FROM order_details GROUP BY product HAVING SUM(quantity) > 10;
SELECT product, SUM(quantity) AS "Total quantity" FROM order_details GROUP BY product HAVING SUM(quantity) > 10; |
Пример использования функции COUNT
Рассмотрим, как мы можем использовать оператор HAVING с функцией COUNT в MySQL.
Вы можете использовать функцию COUNT, чтобы вернуть имя product и количество заказов (для этого product), которые находятся в категории ‘produce’. MySQL оператор HAVING будет фильтровать результаты так, чтобы возвращались только product с более чем 20 заказами.
SELECT product, COUNT(*) AS "Number of orders" FROM order_details WHERE category = 'produce' GROUP BY product HAVING COUNT(*) > 20;
SELECT product, COUNT(*) AS "Number of orders" FROM order_details WHERE category = 'produce' GROUP BY product HAVING COUNT(*) > 20; |
Пример использования функции MIN
Рассмотрим, как мы можем использовать оператор HAVING с функцией MIN в MySQL.
Вы также можете использовать функцию MIN, чтобы вернуть имя каждого department и минимальную зарплату в department. MySQL оператор HAVING будет возвращать только те department, где минимальная зарплата составляет менее 50000 биткоинов :).
SELECT department, MIN(salary) AS "Lowest salary" FROM employees GROUP BY department HAVING MIN(salary) < 50000;
SELECT department, MIN(salary) AS "Lowest salary" FROM employees GROUP BY department HAVING MIN(salary) < 50000; |
Пример использования функции MAX
Наконец, давайте посмотрим, как мы можем использовать оператор HAVING с функцией MAX в MySQL.Например, вы также можете использовать функцию MAX для возврата имени каждого department и максимальной заработной платы в department. Предложение MySQL HAVING будет возвращать только те department, чья максимальная заработная плата превышает 25000 биткоинов :).
SELECT department, MAX(salary) AS "Highest salary" FROM employees GROUP BY department HAVING MAX(salary) > 25000;
SELECT department, MAX(salary) AS "Highest salary" FROM employees GROUP BY department HAVING MAX(salary) > 25000; |
oracleplsql.ru
HAVING в SQL
Вы здесь: Главная - MySQL - SQL - HAVING в SQL
В предыдущей статье мы разобрали GROUP BY в SQL. Там я написал, что данная конструкция позволяет выбирать отдельные группы и для каждой группы вычислять функции, указанные после SELECT. А HAVING позволяет, согласно результату выполнения функций, отфильтровать лишние строки из групп. Разберём это подробнее.
Вспомним нашу предыдущую задачу, где мы вычисляли среднюю цену на молоко для конкретной сети супермаркетов. Давайте с Вами не просто посмотрим среднюю цену, но и при этом выведем только те сети супермаркетов, где средняя цена ниже 38.
Для этой фильтрации по результатам выполнения агрегатной функции используется в SQL команда HAVING:
SELECT `shop_id`, AVG(`price`) FROM `table` GROUP BY `shop_id` HAVING AVG(`price`) < 38В результате, вместо 4-х строк у нас будет лишь 3:
| shop_id | AVG(`price`) |
| 1 | 37.5 |
| 2 | 36.0 |
| 3 | 37.0 |
Если конструкции GROUP BY не будет, то HAVING будет применяться не к конкретной группе, а ко всей выборке. Это означает, что если условие HAVING будет выполняться, то оно никакого влияния не окажет. А если выполняться не будет, то не будет ни одной результирующей строки.
Используется HAVING в SQL не так часто, как остальные команды, но знать и уметь его использовать крайне желательно.
- Создано 11.09.2013 13:39:50
- Михаил Русаков
Копирование материалов разрешается только с указанием автора (Михаил Русаков) и индексируемой прямой ссылкой на сайт (http://myrusakov.ru)!
Добавляйтесь ко мне в друзья ВКонтакте: http://vk.com/myrusakov.Если Вы хотите дать оценку мне и моей работе, то напишите её в моей группе: http://vk.com/rusakovmy.
Если Вы не хотите пропустить новые материалы на сайте,то Вы можете подписаться на обновления: Подписаться на обновления
Если у Вас остались какие-либо вопросы, либо у Вас есть желание высказаться по поводу этой статьи, то Вы можете оставить свой комментарий внизу страницы.
Порекомендуйте эту статью друзьям:
Если Вам понравился сайт, то разместите ссылку на него (у себя на сайте, на форуме, в контакте):
-
Кнопка:
<a href="https://myrusakov.ru" target="_blank"><img src="https://myrusakov.ru//images/button.gif" alt="Как создать свой сайт" /></a>
Она выглядит вот так:
-
Текстовая ссылка:<a href="https://myrusakov.ru" target="_blank">Как создать свой сайт</a>
Она выглядит вот так: Как создать свой сайт
- BB-код ссылки для форумов (например, можете поставить её в подписи): [URL="https://myrusakov.ru"]Как создать свой сайт[/URL]
myrusakov.ru
