Работа с функцией GROUP_CONCAT()

Июнь15

Всем привет!

Хочу рассказать о работу с функцией GROUP_CONCAT.

Помнится меня на собеседовании в одну компанию спросили, как получить данные при группировке в строку…. ответа я не знал, конечно в тот же вечер я все узнал и понял как это легко))

Недавно мне надо было написать запрос как раз с использованием этой функции, и решил написать об этом статью.

И так, представим две таблицы, author(авторы) и books(книги авторов).

Заполним данные, ниже представлены две эти таблицы:

Таблица авторов:

CREATE TABLE `author` (
	`id` INT(10) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(200) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
	`fam` VARCHAR(200) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
	`birthday` DATE NULL DEFAULT NULL,
	PRIMARY KEY (`id`)
)
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB
ROW_FORMAT=DEFAULT
AUTO_INCREMENT=3;

Заполняем таблицу:

INSERT INTO `author` (`id`, `name`, `fam`, `birthday`) VALUES (1, 'Николай Николаевич', 'Носов', '2008-11-23');
INSERT INTO `author` (`id`, `name`, `fam`, `birthday`) VALUES (2, 'Артур Конан', 'Дойль', '1859-05-22');

Таблица книг авторов:

CREATE TABLE `books` (
	`id` INT(10) NOT NULL AUTO_INCREMENT,
	`id_author` INT(10) NULL DEFAULT NULL,
	`book` VARCHAR(500) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
	PRIMARY KEY (`id`),
	INDEX `id_author` (`id_author`)
)
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB
ROW_FORMAT=DEFAULT
AUTO_INCREMENT=8;

Заполняем таблицу:

INSERT INTO `books` (`id`, `id_author`, `book`) VALUES (1, 1, 'Незнайка учится');
INSERT INTO `books` (`id`, `id_author`, `book`) VALUES (2, 1, 'Незнайка-путешественник');
INSERT INTO `books` (`id`, `id_author`, `book`) VALUES (3, 1, 'Винтик, Шпунтик и пылесос');
INSERT INTO `books` (`id`, `id_author`, `book`) VALUES (4, 2, 'Затерянный мир');
INSERT INTO `books` (`id`, `id_author`, `book`) VALUES (5, 2, 'Шерлок Холмс');
INSERT INTO `books` (`id`, `id_author`, `book`) VALUES (6, 2, 'Шерлок Холмс');
INSERT INTO `books` (`id`, `id_author`, `book`) VALUES (7, 2, NULL);

Сделаем выборки :

mysql> select * from author;
+----+-------------------------------------+------------+------------+
| id | name                                | fam        | birthday   |
+----+-------------------------------------+------------+------------+
|  1 | Николай Николаевич                  | Носов      | 2008-11-23 |
|  2 | Артур Конан                         | Дойль      | 1859-05-22 |
+----+-------------------------------------+------------+------------+
2 rows in set (0.00 sec)

mysql> select * from books;
+----+-----------+------------------------------------------------+
| id | id_author | book                                           |
+----+-----------+------------------------------------------------+
|  1 |         1 | Незнайка учится                                |
|  2 |         1 | Незнайка-путешественник                        |
|  3 |         1 | Винтик, Шпунтик и пылесос                      |
|  4 |         2 | Затерянный мир                                 |
|  5 |         2 | Шерлок Холмс                                   |
|  6 |         2 | Шерлок Холмс                                   |
|  7 |         2 | NULL                                           |
+----+-----------+------------------------------------------------+
7 rows in set (0.00 sec)

Обратите внимание, во второй выборке я специально добавил дублирующееся поле (Шерлок Холмс) и также поле с NULL записью, для чего это я сделал объясню позже.

Теперь нам надо выбрать всех авторов и их книги, если я напишу запрос

SELECT a.id, a.name, a.fam,  b.book  as books
FROM `author` a
LEFT JOIN `books` b ON a.id=b.id_author;

В итоге мы получил все книги по всем авторам:

+----+-----------------------+----------+----------------------------+
| id | name                  | fam      | books                      |
+----+-----------------------+----------+----------------------------+
|  1 | Николай Николаевич    | Носов    | Незнайка учится            |
|  1 | Николай Николаевич    | Носов    | Незнайка-путешественник    |
|  1 | Николай Николаевич    | Носов    | Винтик, Шпунтик и пылесос  |
|  2 | Артур Конан           | Дойль    | Затерянный мир             |
|  2 | Артур Конан           | Дойль    | Шерлок Холмс               |
|  2 | Артур Конан           | Дойль    | Шерлок Холмс               |
|  2 | Артур Конан           | Дойль    | NULL                       |
+----+-----------------------+----------+----------------------------+
7 rows in set (0.00 sec)

А мне бы получить записи авторов, т.е. две записи, в нашем случае, и чтобы в поле books были все книги автора…

Это можно сделать применив как раз ф-цию GROUP_CONCAT().
Схема ф-ции:

GROUP_CONCAT([DISTINCT] expr [,expr ...]
             [ORDER BY {unsigned_integer | col_name | expr}
                 [ASC | DESC] [,col_name ...]]
             [SEPARATOR str_val])

Для того, чтобы расписать каждую из команд, я напишу пример:

mysql> SELECT a.id, a.name, a.fam, GROUP_CONCAT(DISTINCT b.book ORDER BY b.book ASC SEPARATOR ', ') AS books
    -> FROM `author` a
    -> LEFT JOIN `books` b ON a.id=b.id_author
    -> GROUP BY a.id;
+----+--------------------+-------+---------------------------------------------------------------------+
| id | name               | fam   | books                                                               |
+----+--------------------+-------+---------------------------------------------------------------------+
|  1 | Николай Николаевич | Носов | Винтик, Шпунтик и пылесос, Незнайка учится, Незнайка-путешественник |
|  2 | Артур Конан        | Дойль | Затерянный мир, Шерлок Холмс                                        |
+----+--------------------+-------+---------------------------------------------------------------------+
2 rows in set (0.05 sec)

Как видно после использование этой ф-ции я получил то, что хотел — дву строчки и в поле books все книги автора.

Теперь распишем каждую команду.
Начну с того, что группировке поддается только поле отличные от NULL, т.е. записи с содержимыми типа NULL не будет в поле books.

DISTINCT — позволяет выбрать не повторяющиеся значения, т.е. книга с дублирующим названием будет откинута, для этого я ввел дважды название книги Шерлок Холмс;
ORDER BY col_name ASC | DESC — позволяет сортировать данные;
SEPARATOR — позволяет разделить данные через нужный вам делитель, по-умолчанию это запятая «,» (без кавычек)

Также эта функция относится к групповым функциям, поэтому использование её без оператора GROUP BY не желательно, иначе в нашем примере получим одну строку с первым автором в таблице и всеми книгами которые есть в таблице книг.

Есть еще одно ограничение. Кол-во символов которые будут отображены в сгруппированном поле по-умолчанию 1024, если надо больше, то использую переменную group_concat_max_len устанавливаем своё значение:

синатксис:
SET [GLOBAL | SESSION] group_concat_max_len = val;

пример:
SET group_concat_max_len = 2048;

Всем спасибо, в пользу тебе %username%.

Статья просмотренна 268408 раз, зашло посетителей 96853

рубрика: MySQL

Комментарии (51)

Выполняем авторизацию...

Панель управления | Изменить профиль | Выйти

Вошли как

+11 Хороший комментарий Плохой комментарий

rame0 · 772 недель назад

Спасибо, познавательно!

Пн	Вт	Ср	Чт	Пт	Сб	Вс
« Май				Июл »
	1	2	3	4	5	6
7	8	9	10	11	12	13
14	15	16	17	18	19	20
21	22	23	24	25	26	27
28	29	30

За чистый и ясный код!

Работа с функцией GROUP_CONCAT()

Забыли пароль?

Комментарии (51)

Опубликовать новый комментарий

(51)

Облако тегов

Я на твиттере!

Календарь

Архивы блога

Сейчас на сайте