实现将多条数据合并为一条数据,在mysql中可以通过 GROUP_CONCAT 函数实现解决方案:用
GROUP_CONCAT
CREATE TABLE `edu_test` (
`test_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '本表的主键',
`courseware_ids` varchar(255) DEFAULT NULL COMMENT '课件ids',
`courseware_types` varchar(255) DEFAULT NULL COMMENT '课件类型',
PRIMARY KEY (`test_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='测试主表';
CREATE TABLE `edu_test_son` (
`test_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '本表的主键',
`courseware_id` bigint(20) DEFAULT NULL COMMENT '课件id',
`courseware_name` varchar(255) DEFAULT NULL COMMENT '课件name',
`courseware_type` bigint(20) DEFAULT NULL COMMENT '课件类型',
PRIMARY KEY (`test_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='测试子表';
insert into edu_test (courseware_ids,courseware_types)
values (1,"1,2"),(2,"2,3,5"),(3,"3,4,5"),(4,"4,6"),(5,"5,8"),(6,"6"),(7,"7"),(8,"8");
insert into edu_test_son (courseware_id,courseware_name,courseware_type)
values (1,"课件1",1),(1,"课件2",2),(3,"课件3",3),(5,"课件4",4),(5,"课件5",5),(5,"课件6",6),(7,"课件7",7),(8,"课件8",8);
select * from edu_test;
select * from edu_test_son;
-- 主表单个字段单个id,对应子表多个id
SELECT
a.*,(
SELECT
GROUP_CONCAT( CONCAT_WS( ',', b.courseware_name ) SEPARATOR ',' ) AS courseware_name
FROM
edu_test_son b
WHERE
b.courseware_id IN ( a.courseware_ids )) AS coursewareName
FROM
edu_test a;
多字段如何处理
GROUP_CONCAT(CONCAT_WS(',',type,typename) SEPARATOR ',')
IN在子查询中不起作用group_concat,解决方法见 ↓文章来源:https://www.toymoban.com/news/detail-653435.html
IN在子查询中不起作用group_concat_行者彡的博客-CSDN博客文章来源地址https://www.toymoban.com/news/detail-653435.html
到了这里,关于mysql将多条数据合并成一条的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!