Sql三张表的连接查询

15257137811

//这是三张表的左连接查询；
select DISTINCT  t1.* FROM crm_wy_fee_record t1 LEFT JOIN crm_wy_fee_record_detail t2 on t1.order_no = t2.order_no
LEFT JOIN crm_owner_residence t3 on t2.residence_code = t3.residence_code WHERE t3.owner_id =#{owner_id}

//三张表的左连接和全连接混合；
SELECT DISTINCT t2.residence_code as room_code,t1.* FROM crm_wy_fee_record_detail t2 INNER JOIN crm_wy_fee_record t1 on t1.order_no = t2.order_no
LEFT JOIN crm_owner_residence t3 on t2.residence_code = t3.residence_code WHERE t3.owner_id = #{owner_id}


示例：
tb_heart_rate as h里有这三个字段：imei、heart_rate、time
tb_watch_match as w里有这三个字段：watch_imei、id_number
tb_namelist as n里有这三个字段：sex、age、id_number
要求：查询出以上所有字段，并且h.imei=w.watch_imei   w.id_number=n.id_number  w.match_id=78   w.id_number!=''

Select h.imei,h.heart_rate,FROM_UNIXTIME(h.time,'%Y-%m-%d %H:%i:%s'),n.real_name,n.sex,n.age from tb_heart_rate as h
Inner Join tb_watch_match as w On h.imei=w.watch_imei
Inner Join tb_namelist as n on n.id_number=w.id_number
where w.match_id=78 and w.id_number!=''