有4个表a,b,c,d 其中a表是核心表,使用的时候 我要用a表左连接b,c两个表,然后c表左连接d表
select *
from a
left join b on a.id = b.id
left join c on c.id = a.id
c left join d on c.id = d.id
where
a.day = '20110120'
and b.day = '20110120'
and c.day = '20110120'
and d.day = '20110120'
我知道上面的语句有语法问题,希望指正,谢谢
select *
from a
left join b on a.id = b.id
left join c on c.id = a.id
c left join d on c.id = d.id
where
a.day = '20110120'
and b.day = '20110120'
and c.day = '20110120'
and d.day = '20110120'
我知道上面的语句有语法问题,希望指正,谢谢
from a
left join b on a.id = b.id
left join c on c.id = a.id
left join d on c.id = d.id ---c 去掉了
where
a.day = '20110120'
and b.day = '20110120'
and c.day = '20110120'
and d.day = '20110120'
Select *
From Ods_Hx_Ln_Mst m,
left join Ods_Hx_Ln_Reg r on m.ac_id = r.ac_id,
left join Ods_Hx_Cif_Basic_Inf c on m.cif_id = c.cif_id,
left join Ods_Hx_Ln_Parm p on m.prdt_no = p.prdt_no,
left join Ods_Hx_Ln_Auth a on m.pact_no = a.pact_no,
left join Ods_Hx_Ln_Expand e on m.ac_id = e.ac_id,
left join ods_hx_ln_mst_hst mh on m.ac_id = mh.ac_id,
inner join ods_hx_ln_pay_pln pp on mh.ac_id = pp.ac_id --老在这提示表或者视图不存在
Where m.data_date = '20110117'
and c.data_date = '20110117'
and r.data_date = '20110117'
and p.data_date = '20110117'
and a.data_date = '20110117'
and e.data_date = '20110117'
and pp.data_date = '20110117'
and mh.data_date = '20110117';
from a
left join b ...
left join c ...的时候 后面的b和c表都是以a为左连接的对象,难倒是以on的左右为标准么?
m left join ( mh inner join pp) ?如果上述理解成立,非常遗憾的告之,上述的写法,不是你要想的结果。因为,SQL 在最后解析时,是 inner join 会将 m、mh、pp 等表,一个一个关联,一直到最后一个关联。 而上面的SQL 是 inner join ,你想一下,那结果会什么。 记录应该会少了不少才是。
关于这方面,看一下执行计划就知道了,SQL是将按顺序进行关联的。至于为什么 Phoenix_99 建议 left join 的写法在同一层级,简单的验证了一下,从结果上来看,没什么影响。但总感觉有点问题,还期待高人帮解释一下吧。