Оператор SQL GROUP BY для группировки в запросах
Назад<<< | Содержание | Вперёд>>> |
Оператор SQL GROUP BY служит для распределения строк - результата запроса - по группам, в которых значения некоторого столбца, по которому происходит группировка, являются одинаковыми. Группировку можно производить как по одному столбцу, так и по нескольким.
Часто оператор SQL GROUP BY применяется вместе с агрегатными функциями (COUNT, SUM, AVG, MAX, MIN). В этих случаях агрегатные функции служат для вычисления соответствующего агрегатного значения ко всему набору строк, для которых некоторый столбец - общий.
Оператор GROUP BY имеет следующий синтаксис:
Группировка по одному столбцу без агрегатных функций
Если в результате запроса требуется вывести один столбец и по этому же столбцу производится группировка, то оператор GROUP BY просто выбирает уникальные значения и убирает дубликаты, то есть выполняет те же задачи, что и ключевое слово DISTINCT.
Если вы хотите выполнить запросы к базе данных из этого урока на MS SQL Server, но эта СУБД не установлена на вашем компьютере, то ее можно установить, пользуясь инструкцией по этой ссылке.
Скрипт для создания базы данных библиотеки, её таблиц и заполения таблиц данными - в файле по этой ссылке.
В примерах работаем с базой данных библиотеки и ее таблицей "Книга в пользовании" (Bookinuse). Отметим, что оператор GROUP BY ведёт себя несколько по-разному в MySQL и в MS SQL Server. Эти различия будут показаны на примерах.
Author | Title | Pubyear | Inv_No | Customer_ID |
Толстой | Война и мир | 2005 | 28 | 65 |
Чехов | Вишневый сад | 2000 | 17 | 31 |
Чехов | Избранные рассказы | 2011 | 19 | 120 |
Чехов | Вишневый сад | 1991 | 5 | 65 |
Ильф и Петров | Двенадцать стульев | 1985 | 3 | 31 |
Маяковский | Поэмы | 1983 | 2 | 120 |
Пастернак | Доктор Живаго | 2006 | 69 | 120 |
Толстой | Воскресенье | 2006 | 77 | 47 |
Толстой | Анна Каренина | 1989 | 7 | 205 |
Пушкин | Капитанская дочка | 2004 | 25 | 47 |
Гоголь | Пьесы | 2007 | 81 | 47 |
Чехов | Избранные рассказы | 1987 | 4 | 205 |
Пушкин | Сочинения, т.1 | 1984 | 6 | 47 |
Пастернак | Избранное | 2000 | 137 | 18 |
Пушкин | Сочинения, т.2 | 1984 | 8 | 205 |
NULL | Наука и жизнь 9 2018 | 2019 | 127 | 18 |
Чехов | Ранние рассказы | 2001 | 171 | 31 |
Пример 1. Вывести авторов выданных книг, сгруппировав их. Пишем следующий запрос:
Этот запрос вернёт следующий результат:
Author |
NULL |
Гоголь |
Ильф и Петров |
Маяковский |
Пастернак |
Пушкин |
Толстой |
Чехов |
Как видим, в таблице стало меньше строк, так как фамилии авторов остались каждая по одной.
В следующем примере увидим, что оператор GROUP BY не следует путать с оператором ORDER BY и поймём, чем эти операторы отличаются друг от друга.
Пример 2. Вывести авторов и названия выданных книг, сгруппировав по авторам. Пишем следующий запрос, который допустим в MySQL:
Этот запрос вернёт следующий результат:
Author | Title |
NULL | Наука и жизнь 9 2018 |
Гоголь | Пьесы |
Ильф и Петров | Двенадцать стульев |
Маяковский | Поэмы |
Пастернак | Доктор Живаго |
Пушкин | Капитанская дочка |
Толстой | Война и мир |
Чехов | Вишнёвый сад |
Как видим, в таблице каждому автору соответствует лишь одна книга, причём та, которая в таблице BOOKINUSE является первой по порядку записей.
Если бы нам требовалось вывести все книги, причём авторы должны были бы следовать не "вразброс", а по порядку: сначала Гоголь и все его книги, затем другие авторы и все их книги, то мы применили бы не оператор GROUP BY, а оператор ORDER BY.
По-другому ведёт себя оператор GROUP BY в MS SQL Server.
Группировка по нескольким столбцам без агрегатных функций
И всё же вывести все записи, соответствующие значению столбца, по которому происходит группировка, можно. Но в этом случае в результирующей таблице должен появиться ещё один столбец. Такой случай проиллюстирован в следующем примере.
Пример 3. Вывести авторов, названия выданных книг, ID пользователя и инвентарный номер выданной книги. Сгруппировать по авторам, ID пользователя и инвентарному номеру. На MySQL запрос будет следующим:
Этот запрос вернёт следующий результат:
Author | Title | Customer_ID | Inv_no |
Гоголь | Пьесы | 47 | 81 |
Ильф и Петров | Двенадцать стульев | 31 | 3 |
Маяковский | Поэмы | 120 | 2 |
Пастернак | Избранное | 18 | 137 |
Пастернак | Доктор Живаго | 120 | 69 |
Пушкин | Капитанская дочка | 47 | 25 |
Пушкин | Сочинения, т.1 | 47 | 6 |
Пушкин | Сочинения, т.2 | 205 | 8 |
Толстой | Воскресенье | 47 | 77 |
Толстой | Война и мир | 65 | 28 |
Толстой | Анна Каренина | 205 | 7 |
Чехов | Вишневый сад | 31 | 19 |
Чехов | Ранние рассказы | 31 | 171 |
Чехов | Вишневый сад | 65 | 5 |
Чехов | Избранные рассказы | 120 | 19 |
Чехов | Избранные рассказы | 205 | 4 |
Как видим, в результирующей таблице присутствуют все книги всех авторов, причём авторы следуют по порядку, как если бы мы применили оператор ORDER BY. Кроме того, видно, что записи сгруппированы и по второму указанному столбцу - Customer_ID. Так, у автора Пушкина сначала перечисляются книги, выданные пользователю с Customer_ID 47, а затем - 205. У автора Чехова сначала перечисляются книги, выданные пользователю с Customer_ID 31, а затем - с другими номерами. Третий столбец, по которому происходит группировка - Inv_no - добавлен только для того, чтобы в результирующей таблице выводились все строки, соответствующие значениям ранее перечисленных столбцов для группировки, а не только уникальные.
По-другому ведёт себя оператор GROUP BY в MS SQL Server и в случае этого запроса.
Поделиться с друзьями
Назад<<< | Содержание | Вперёд>>> |