项目中有这样一个需求:有一张表存放了基本的人的信息,另一张家庭表,存放着这个人的家庭情况,再加上第三张表的消费记录,这三张表的部分数据需要拼接在一起,然后展示到前端,这里家庭情况,一个人是存在多条记录的,如何在使用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 wife
from 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
860
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
