mysql、serverSql将多条数据合并成一条

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

 

SQL server把多条记录查找结果合并成一条记录 

CONVERT(nvarchar(10),'需要合并的字段'+',')

for xml Path('')

SELECT  CONVERT(nvarchar(10),'需要合并的字段'+',')
 from  test_info where  id  
in (select enterpriseId from info where  license ='湘A88888' and color= '蓝色')
for xml Path('')

IN在子查询中不起作用group_concat，解决方法见 ↓

IN在子查询中不起作用group_concat_行者彡的博客-优快云博客