(My)SQL与三个表完全连接 [英] (My)SQL full join with three tables
本文介绍了(My)SQL与三个表完全连接的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有树表
ID A
-----------
1 10
ID B
-----------
1 20
2 30
ID C
-----------
2 40
3 50
有人可以告诉我如何进行这样的视图或查询打印吗?
Can anybody please tell how to make a view or query prints like this?
ID A B C R (A + B - C)
-----------------------------------
1 10 20 0 30
2 0 30 40 -10
3 0 0 50 -50
谢谢.
推荐答案
据我所知,MySql中没有完整的外部联接.因此,要执行所需的操作,您应该在派生表中获得不同的ID,并保留原始表的左联接:
As far as I know there is no full outer join in MySql. So, to do what you require you should get distinct IDs in derived table and left join original tables:
select ids.id,
ifnull(table1.A, 0) A,
ifnull(table2.B, 0) B,
ifnull(table3.C, 0) C,
ifnull(table1.A, 0) + ifnull(table2.B, 0) - ifnull(table3.C, 0) R
from
(
select id
from table1
union
select id
from table2
union
select id
from table3
) ids
left join table1
on ids.id = table1.id
left join table2
on ids.id = table2.id
left join table3
on ids.id = table3.id
这篇关于(My)SQL与三个表完全连接的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文