MySQL 多行数据合并 GROUP_CONCAT

曾经终败给现在 2023-10-09 12:51 31阅读 0赞

表结构及数据

DROP TABLE IF EXISTS \`course\`;  
CREATE TABLE \`course\` (  
\`name\` varchar(255) CHARACTER SET utf8 COLLATE utf8\_general\_ci DEFAULT NULL,  
\`course\_name\` varchar(255) CHARACTER SET utf8 COLLATE utf8\_general\_ci DEFAULT NULL  
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8\_general\_ci ROW\_FORMAT = Dynamic;

\-- ----------------------------  
\-- Records of course  
\-- ----------------------------  
INSERT INTO \`course\` VALUES ('李明', '阅读赏析');  
INSERT INTO \`course\` VALUES ('李明', '摄影');  
INSERT INTO \`course\` VALUES ('李明', '太极剑');  
INSERT INTO \`course\` VALUES ('司马光', '马克思主义基本原理');  
INSERT INTO \`course\` VALUES ('司马光', '摄影');  
INSERT INTO \`course\` VALUES ('叶问', '阅读赏析');  
INSERT INTO \`course\` VALUES ('叶问', '法语');

SET FOREIGN\_KEY\_CHECKS = 1;

选修课程表

select t.name as '姓名',t.course_name as '课程' from course t  ;

![990755-20190805115728854-358202820.png][]

需求： 将每个人的课程合并为一行数据

select t.name as '姓名',GROUP_CONCAT(t.course_name )  as '课程' from course t  group by t.name ;

![990755-20190805115825325-2134809233.png][]

修改分隔符及排序

select t.name as '姓名',GROUP_CONCAT(t.course_name  order by t.course_name SEPARATOR '#')  as '课程' from course t  group by t.name

![990755-20190805115911535-1650376479.png][]

注意：使用 GROUP\_CONCAT（）函数必须对源数据进行分组，否则所有数据会被合并成一行

转载于:https://www.cnblogs.com/luxd/p/11302255.html

[990755-20190805115728854-358202820.png]: https://img2018.cnblogs.com/blog/990755/201908/990755-20190805115728854-358202820.png
[990755-20190805115825325-2134809233.png]: https://img2018.cnblogs.com/blog/990755/201908/990755-20190805115825325-2134809233.png
[990755-20190805115911535-1650376479.png]: https://img2018.cnblogs.com/blog/990755/201908/990755-20190805115911535-1650376479.png