mysql 将竖列的表格数据拼接成横向的数据,查询结果

            项目中有这样一个需求:有一张表存放了基本的人的信息,另一张家庭表,存放着这个人的家庭情况,再加上第三张表的消费记录,这三张表的部分数据需要拼接在一起,然后展示到前端,这里家庭情况,一个人是存在多条记录的,如何在使用left join的时候,不会产生多条重复数据呢,其实是可以将查询的结果当成列来显示,如下sql语句:

         

select infor.name,rv.visitTime,(select family.name from retire_infor_family as family where family.type = 1 and family.retire_infor_id = infor.retire_infor_id  ) as father,(select family.name from retire_infor_family as family where family.type = 2 and family.retire_infor_id = infor.retire_infor_id  ) as monther,(select family.name from retire_infor_family as family where family.type = 3 and family.retire_infor_id = infor.retire_infor_id  ) as wifefrom retire_infor as infor LEFT JOIN (SELECT * from retire_infor_visit  where type=2) as rv ON infor.retire_infor_id = rv.retire_infor_id