mysql将多条数据合并成一条

我会带着你远行 2024-03-25 23:24 91阅读 0赞

> 实现将多条数据合并为一条数据，在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;

![fa7f12d0dd1441efb9f6562c9a07e288.png][]

> 多字段如何处理

GROUP\_CONCAT(CONCAT\_WS(',',type,typename) SEPARATOR ',')

**IN在子查询中不起作用group\_concat，解决方法见 ↓**

[IN在子查询中不起作用group\_concat\_行者彡的博客-CSDN博客][IN_group_concat_-CSDN]

[fa7f12d0dd1441efb9f6562c9a07e288.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/c63d83a57a634d048b48643d56eec9e8.png
[IN_group_concat_-CSDN]: https://blog.csdn.net/sinat_21843047/article/details/129712971?spm=1001.2014.3001.5502