mysql中GROUP_CONCAT的使用方法实例分析

吾爱主题阅读：178 2024-04-05 16:20:25 评论：0

本文实例讲述了mysql中group_concat的使用方法。分享给大家供大家参考，具体如下：

现在有三个表，结构如下：

cate表：

1 2 3 4 5 create table `cate` ( `id` int (10) unsigned not null auto_increment comment 'id' , ` name ` char (20) default '' comment '分类名' , primary key (`id`) ) engine=innodb auto_increment=5 default charset=utf8 comment= '文章分类表' ;

article表：

1 2 3 4 5 6 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 '分类id' , primary key (`id`) ) engine=innodb auto_increment=5 default charset=utf8 comment= '文章表' ;

article_extend表：

1 2 3 4 5 6 create table `article_extend` ( `id` int (10) unsigned not null auto_increment, `article_id` int (10) unsigned default '0' comment '文章id' , ` name ` varchar (255) default '' comment '音频，图片之类' , primary key (`id`) ) engine=innodb auto_increment=4 default charset=utf8 comment= '附件表' ;

三张表数据如下：

cate表:

article表:

article_extend表:

问题来了，现在通过表连接查询，查询文章id为1的文章数据，并显示文章标题，文章分类，文章name。

1 2 3 4 5 6 7 8 9 10 11 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;

结果如下，出现了两条数据：

现在只想要一条结果，aname字段进行合并，如何做？

只有通过group_concat来实现了：

1 2 3 4 5 6 7 8 9 10 11 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;

结果如下：

那么，现在我们不想通过文章id一条一条的查，我们要取全部，但如果文章name有多个的要进行合并，如何做？

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 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;