Mysql
FULL OUTER JOIN 使用 MySQL,MySQL 不支持超過 2 個表的解決方案是什麼?
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
- 建構一個
VIEW
模擬 aFULL OUTER JOIN
和A
的aB
。(使用一些連結給出的模板。)- 建構一個
VIEW
模擬FULL OUTER JOIN
那個VIEW
和的一個C
。(使用一些連結給出的模板。)- 等等。
最終,您將擁有
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