大家好,现在有两个表,主表Mstb_Order_Header和右表mstb_order_header_extend_atl,两个表用order_number关联起来,但是右表的order_number可能对应多条记录,这多条记录的carrier_type值其实是同一个,只要取其中一条的carrier_type值就好了。下面是SQL
第一条SQL
select oh.*,hea.carrier_type from Mstb_Order_Header oh
left join (select order_number,max(carrier_type) as carrier_type from mstb_order_header_extend_atl group by order_number) hea
on oh.order_number = hea.order_number;
第二条SQL
select oh.*,hea.carrier_type from Mstb_Order_Header oh
left join (select order_number,carrier_type from mstb_order_header_extend_atl where rownum = 1) hea
on oh.order_number = hea.order_number;
现在发现两条SQL都能满足业务需求,我不明白的是第二条SQL,我理解的第二条SQL,右表应该是只要一条记录和主表匹配,就是应该先执行了select order_number,carrier_type from mstb_order_header_extend_atl where rownum = 1 在和主表关联。
两条SQL的执行计划如下:
第一条
第二条
我想问一下具体执行的逻辑如何理解?