Mysql

FULL OUTER JOIN 使用 MySQL,MySQL 不支持超過 2 個表的解決方案是什麼?

  • May 11, 2021
  select * from

(select col1,col2,col3 from tableA 
where col1 like 'somthing1'
and col2 like 'somthing2' ) as t1

left join

(select col2,col2, col3 from tableB
where col2 like 'somthing1'
and col2 like 'somthing2') as t2

on t1.col3= t2.col3

left join

(select col2,col2, col3 from tableC
where col2 like 'somthing1'
and col2 like 'somthing2') as t3

on t2.col3= t3.col3

left join

(select col2,col2, col3 from tableC
where col2 like 'somthing1'
and col2 like 'somthing2') as t4

on t3.col3= t4.col3

UNION

(select col1,col2,col3 from tableA 
where col1 like 'somthing1'
and col2 like 'somthing2' ) as t1

right join

(select col2,col2, col3 from tableB
where col2 like 'somthing1'
and col2 like 'somthing2') as t2

on t1.col3= t2.col3

right join

(select col2,col2, col3 from tableC
where col2 like 'somthing1'
and col2 like 'somthing2') as t3

on t2.col3= t3.col3

right join

(select col2,col2, col3 from tableC
where col2 like 'somthing1'
and col2 like 'somthing2') as t4

on t3.col3= t4.col3

在此處輸入圖像描述

  1. 建構一個VIEW模擬 aFULL OUTER JOINA的a B。(使用一些連結給出的模板。)
  2. 建構一個VIEW模擬FULL OUTER JOIN那個VIEW和的一個C。(使用一些連結給出的模板。)
  3. 等等。

最終,您將擁有FULL OUTER JOIN所有表中的一個,而不會因為所需的連接數量而復雜化。

我知道我來得太晚了,但是……如果這是我,鑑於一切似乎都加入了 col3,我會做類似的事情:

select 
t1.*, ​t2.*, t3.*, t4.*
from
(
select col3 from t1
union select col3 from t2
union select col3 from t3
union select col3 from t4
) allcol3
left join t1 on t1.col3 = allcol3.col3
left join t2 on t2.col3 = allcol3.col3
left join t3 on t3.col3 = allcol3.col3
left join t4 on t4.col3 = allcol3.col3

引用自:https://dba.stackexchange.com/questions/184494