Работа с функцией GROUP_CONCAT()
Всем привет!
Хочу рассказать о работу с функцией 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%.
Статья просмотренна 267907 раз, зашло посетителей 96630