mysql同一个表中两条数据进行合并

Question

mysql同一个表中两条数据进行合并

<!--获取公司和主营产品信息-->
  <select id="selectUserExt" resultMap="getUserExt">
    select u.user_id,u.user_name,u.company_name,u.are_name,u.address,u.web,u.tel,u.fax,u.u_describe,
        GROUP_CONCAT(m.mainproduct_name) AS mainproduct_name
    FROM `user` u
    INNER JOIN mainproduct m
    on user_id=m.company_id
    AND user_id=#{userId,jdbcType=INTEGER}
  </select>

  <!--获取联系人信息-->
  <select id="selectPeopleExt" resultMap="getUserExt">
    select CONCAT(c.principal_name,' ', b.business_department_name,' ',c.mphone) as principal_name
    FROM contactinfo c,business_department b
    WHERE c.business_department_id=b.business_department_id
    AND company_id=#{userId,jdbcType=INTEGER}
    UNION
    select CONCAT(c.principal_name, ' ',b.business_department_name,' ',c.mphone) as principal_name
    FROM contactinfo c,business_department b
    WHERE c.business_department_id=b.business_department_id
    AND company_id=#{userId,jdbcType=INTEGER}
</select>

Answer 1

使用GROUP BY分组后再用GROUP_CONCAT连接起来