51Testing软件测试论坛

 找回密码
 (注-册)加入51Testing

QQ登录

只需一步,快速开始

微信登录,快人一步

查看: 1947|回复: 0

[转贴] Sql三张表的连接查询

[复制链接]

该用户从未签到

发表于 2018-10-15 20:33:22 | 显示全部楼层 |阅读模式
//这是三张表的左连接查询;
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!=''





回复

使用道具 举报

本版积分规则

关闭

站长推荐上一条 /1 下一条

小黑屋|手机版|Archiver|51Testing软件测试网 ( 沪ICP备05003035号 关于我们

GMT+8, 2024-3-29 03:45 , Processed in 0.063273 second(s), 24 queries .

Powered by Discuz! X3.2

© 2001-2024 Comsenz Inc.

快速回复 返回顶部 返回列表