mysql 拼接字段+根据特定值返回特定内容

1、这里，首选实现根据特定值返回特定内容，使用 case
 

case  
    when r.Name='demand' then '采购商'
    when r.Name='supplier' then '供应商'
    else '未知'
end as roleName

2、然后使用Group_Concat函数

group_concat(
    (case  
		when r.Name='demand' then '采购商'
		when r.Name='supplier' then '供应商'
	end) separator ',') as  RoleNames

3、完整实例

select bc.Id,bc.CompanyName,bc.ContactInfo ,bc.ContactPerson,bc.CreationTime companyCreationTime,bc.EnterpriseType,
	bc.IndustryGroup ,bc.IndustrySubGroup,bc.IndustrySpecificName,bc.Email,bc.Logo,bc.Introduction,
	a.UserName,a.PhoneNumber,a.CreationTime,
	group_concat((case  
		when r.Name='demand' then '采购商'
		when r.Name='supplier' then '供应商'
	end) separator ',') as  RoleNames
	from base_companyinfo bc 
inner join abpusers a on bc.UserId =a.Id 
inner join abpuserroles ar on a.Id =ar.UserId 
inner join abproles r on ar.RoleId =r.Id
where bc.Id ='1'