oracle 关联 A表与B表关联，A->B 一对多

u010242159

于 2013-04-16 11:24:43 发布

阅读量2.7k

点赞数

CC 4.0 BY-SA版权

本文链接：https://blog.youkuaiyun.com/u010242159/article/details/8807910

本文探讨了两种不同的SQL查询方法，一种是使用LEFT JOIN连接两张表来获取客户信息及电话号码，另一种则是通过子查询并限制返回第一条电话记录的方法。对比这两种查询方式的结果差异，帮助读者理解如何更高效地进行数据检索。

select rownum,t.cr_customer_number,t.cr_name,t.cr_idnum,(select f.tl_tel_num from crm_tel f where t.id=f.customerid and rownum=1),t.cr_gather_date from crm_customer t
order by t.cr_customer_number desc

select count((select f.tl_tel_num from crm_tel f where t.id=f.customerid and rownum=1)) as tel
from crm_customer t order by rownum desc

查询两条数据相同选一条用 rownum

一下两条数据查询结果不同：

select t.cr_customer_number,t.cr_name,t.cr_idnum,f.tl_tel_num,t.cr_gather_date from crm_customer t
left join crm_tel f on t.id=f.customerid
order by t.cr_customer_number desc