Example analysis of the use of GROUP_CONCAT in MySQL

This article uses an example to describe how to use GROUP_CONCAT in MySQL. Share with you for your reference, the details are as follows:

Now there are three tables with the following structure:

cate table:

CREATE TABLE `cate` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
 `name` char(20) DEFAULT '' COMMENT 'Category name',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COMMENT='Article Category Table';

Article table:

CREATE TABLE `article` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
 `title` varchar(50) DEFAULT '',
 `cate_id` int(11) NOT NULL DEFAULT '0' COMMENT 'Category id',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COMMENT='Article table';

article_extend table:

CREATE TABLE `article_extend` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `article_id` int(10) unsigned DEFAULT '0' COMMENT 'Article id',
 `name` varchar(255) DEFAULT '' COMMENT 'Audio, pictures, etc.',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='Attachment table';

The data in the three tables are as follows:

cate table:

Article table:

article_extend table:

Here comes the problem. Now query through table connection to query the article data with article id 1, and display the article title, article category, and article name.

SELECT
	a.id AS aid,
	a.title AS atitle,
	c. NAME AS cname,
	ae.NAME AS aname
FROM
	article AS a
LEFT JOIN cate AS c ON a.cate_id = c.id
LEFT JOIN article_extend AS ae ON a.id = ae.article_id
WHERE
	a.id = 1;

The results are as follows, two pieces of data appear:

Now I only want one result, aname field to be merged, how to do it?

This is only possible with GROUP_CONCAT:

SELECT
	a.id AS aid,
	a.title AS atitle,
	c. NAME AS cname,
	GROUP_CONCAT(ae.NAME SEPARATOR '-') AS aname
FROM
	article AS a
LEFT JOIN cate AS c ON a.cate_id = c.id
LEFT JOIN article_extend AS ae ON a.id = ae.article_id
WHERE
	a.id = 1;

The results are as follows:

Well, now we don't want to search one by one by article ID, we want to get all, but if there are multiple article names that need to be merged, how to do it?

SELECT
	a.id AS aid,
	a.title AS atitle,
	c. NAME AS cname,
	ae.allname
FROM
	article AS a
LEFT JOIN (
	SELECT
		ae.article_id,
		GROUP_CONCAT(ae.NAME) AS allname
	FROM
		article_extend AS ae
	GROUP BY
		ae.article_id
) AS ae ON a.id = ae.article_id
LEFT JOIN cate AS c ON a.cate_id = c.id;

The results are as follows:

Readers who are interested in more MySQL-related content can check out the following topics on this site: "MySQL query skills", "MySQL common functions summary", "MySQL log operation skills", "MySQL transaction operation skills summary", "MySQL stored procedure skills" and "MySQL database lock related skills summary"

I hope this article will be helpful to everyone's MySQL database design.

You may also be interested in:

Strings in Python 3 (single quotes, double quotes, triple quotes) and operations between strings and numbers
How to modify the length limit of group_concat in Mysql
Problems with using multiple single quotes and triple quotes in MySQL concat

<<: Linux MySQL root password forgotten solution

>>: VMware15 installation of Deepin detailed tutorial (picture and text)