mysql中GROUP_CONCAT的使用方法实例分析
程序员文章站
2022-09-06 21:22:47
本文实例讲述了mysql中group_concat的使用方法。分享给大家供大家参考,具体如下:现在有三个表,结构如下:cate表:create table `cate` ( `id` int(10)...
本文实例讲述了mysql中group_concat的使用方法。分享给大家供大家参考,具体如下:
现在有三个表,结构如下:
cate表:
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表:
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表:
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。
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来实现了:
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有多个的要进行合并,如何做?
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;
结果如下: