MySQL利用GROUP_CONCAT将一对多关系把子表某一字段拼成一行返回主表中

主表master

  @ApiModelProperty("主表主键id")
  private String id;

  @ApiModelProperty("姓名")
  private String name;

子表slave

  @ApiModelProperty("子表主键id")
  private String id;

  @ApiModelProperty("主表主键id")
  private String masterId;

  @ApiModelProperty("行为")
  private String active;

第一步将子表数据归为一行

SELECT masterId, GROUP_CONCAT(active) 
FROM `slave`
GROUP BY masterId

 

第二步联合主表 

SELECT
	m.*,
	s.actives 
FROM
	`master` m
LEFT JOIN ( SELECT masterId, GROUP_CONCAT( active ) actives 
							FROM `slave` 
							GROUP BY masterId ) s 
ON m.id = s.masterId