(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