How to use GROUP_CONCAT method in mysql
This article will explain in detail how to use the GROUP_CONCAT method in mysql. The content of the article is of high quality, so the editor shares it for you as a reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.
The structure is as follows:
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 classification 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_ extension 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' DEFAULT''COMMENT' audio, pictures', PRIMARY KEY (`id`) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT=' attachment table'
The data of the three tables are as follows:
Cate table:
Article table:
Article_ extension table:
The problem is, now through the table join query, query the article id 1 article data, and display the article title, article classification, article name.
SELECT a.id AS aid, a.title AS atitle, c. NAME AS cname, ae. NAME AS anameFROM article AS aLEFT JOIN cate AS c ON a.cate_id = c.idLEFT JOIN article_extend AS ae ON a.id = ae.article_idWHERE a.id = 1
The results are as follows, and two pieces of data appear:
Now all you want is a result, and the aname field is merged. What do you do?
This can only be achieved through GROUP_CONCAT:
SELECT a.id AS aid, a.title AS atitle, c. NAME AS cname, GROUP_CONCAT (ae. NAME SEPARATOR'-') AS anameFROM article AS aLEFT JOIN cate AS c ON a.cate_id = c.idLEFT JOIN article_extend AS ae ON a.id = ae.article_idWHERE a.id = 1
The results are as follows:
So, now we don't want to check the article id one by one, we want to take all of them, but if there are more than one article name to merge, what to do?
SELECT a.id AS aid, a.title AS atitle, c. NAME AS cname, ae.allnameFROM article AS aLEFT 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_idLEFT JOIN cate AS c ON a.cate_id = c.id
The results are as follows:
On how to use the GROUP_CONCAT method in mysql to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.
