left join on 里用逗号分隔表名

今天看别人代码看到一段看不懂的东西,

    left join 之后的 on 子句 , 右面紧接了 逗号 + 表名

看不懂什么意思,网上找不到说明

经过研究发现语法如下:

上面SQL改成下面正常写法的SQL具体改写的步骤是:

step1. 把 on 子句逗号后面的表 拉到下面做 join (注意不是left join)

step2. 把上面SQL的 where 条件拉到 join on子句位置做链接条件

step3. 删除多余的逗号 , 改写完毕!

具体列子如下:

1

47
select *
48
  from TABLE_A a
49
  left join TABLE_B b
50
    on a.col1 = b.col1, TABLE_C c, TABLE_D d
51
 where c.col2 = a.col1
52
   and d.col1 = b.col1;
53
​
54
等价于
55
​
56
select *
57
  from TABLE_A a
58
  left join TABLE_B b
59
    on a.col1 = b.col1
60
  join TABLE_C c,
61
    on c.col2 = a.col1
62
  join TABLE_D d
63
    on 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';