今天看别人代码看到一段看不懂的东西,
left join 之后的 on 子句 , 右面紧接了 逗号 + 表名
看不懂什么意思,网上找不到说明
经过研究发现语法如下:
上面SQL改成下面正常写法的SQL具体改写的步骤是:
step1. 把 on 子句逗号后面的表 拉到下面做 join (注意不是left join)
step2. 把上面SQL的 where 条件拉到 join on子句位置做链接条件
step3. 删除多余的逗号 , 改写完毕!
具体列子如下:
1
47select *
48from TABLE_A a
49left join TABLE_B b
50on a.col1 = b.col1, TABLE_C c, TABLE_D d
51where c.col2 = a.col1
52and d.col1 = b.col1;
53
54等价于
55
56select *
57from TABLE_A a
58left join TABLE_B b
59on a.col1 = b.col1
60join TABLE_C c,
61on c.col2 = a.col1
62join TABLE_D d
63on d.col1 = b.col1;
64
join特殊例子
1
select *
2
from ysxy y --运输协议
3
left join (select ysxy_id, sum(je) paid
4
from ysxy_fkd
5
where zzjg_id = '985e42f63b4c48d0b6e7dc19eea7a436'
6
and qy_id = '985e42f63b4c48d0b6e7dc19eea7a436'
7
group by ysxy_id) t
8
on y.ysxy_id = t.ysxy_id, gxht g, hdxy h
9
left join hdgs d --货代公司
10
on h.hdgs_id = d.hdgs_id, xmht x
11
where y.gxht_id = g.gxht_id
12
and y.hdxy_id = h.hdxy_id
13
and g.xmht_id = x.xmht_id
14
and y.zzjg_id = '985e42f63b4c48d0b6e7dc19eea7a436'
15
and y.qy_id = '985e42f63b4c48d0b6e7dc19eea7a436'
16
and y.sp_flag = 'Y';
17
18
select *
19
from ysxy y --运输协议
20
left join (select ysxy_id, sum(je) paid
21
from ysxy_fkd
22
where zzjg_id = '985e42f63b4c48d0b6e7dc19eea7a436'
23
and qy_id = '985e42f63b4c48d0b6e7dc19eea7a436'
24
group by ysxy_id) t
25
on y.ysxy_id = t.ysxy_id
26
join gxht g
27
on g.gxht_id = y.gxht_id
28
join hdxy h
29
on h.hdxy_id = y.hdxy_id
30
left join hdgs d
31
on h.hdgs_id = d.hdgs_id
32
join xmht x
33
on x.xmht_id = g.xmht_id
34
where 1 = 1
35
and y.zzjg_id = '985e42f63b4c48d0b6e7dc19eea7a436'
36
and y.qy_id = '985e42f63b4c48d0b6e7dc19eea7a436'
37
and y.sp_flag = 'Y';