I have 2 tables:
我有2张桌子:
-
table1
id|val1|val2 1 | 100|200 2 | 200|400 -
table2
id|val1|val2 1 | 100|250 3 | 500|400
table1 id | val1 | val2 1 | 100 | 200 2 | 200 | 400
table2 id | val1 | val2 1 | 100 | 250 3 | 500 | 400
table3 should be like,
table3应该是,
id|val1|val2
1 | 100|250
2 | 200|400
3 | 500|400
These 2 tables have some common elements.
这两个表有一些共同的元素。
I want to write a query which results in following condition:
我想写一个导致以下条件的查询:
Table3 = Table2 + ( Table1- Table2)
表3 =表2 +(表1-表2)
The common elements should be deleted from Table1 and the remaining data should be taken.
应从表1中删除公共元素,并应采用其余数据。
1 个解决方案
#1
0
Use the following:
使用以下内容:
select ifnull(id_2, id_1) id,
ifnull(val1_2, val1_1) val1,
ifnull(val2_2, val2_1) val2
from (
select t1.id id_1, t1.val1 val1_1, t1.val2 val2_1,
t2.id id_2, t2.val1 val1_2, t2.val2 val2_2
from table1 t1 left join table2 t2 on t1.id = t2.id
union
select * from table1 t1 right join table2 t2 on t1.id = t2.id
) x
#1
0
Use the following:
使用以下内容:
select ifnull(id_2, id_1) id,
ifnull(val1_2, val1_1) val1,
ifnull(val2_2, val2_1) val2
from (
select t1.id id_1, t1.val1 val1_1, t1.val2 val2_1,
t2.id id_2, t2.val1 val1_2, t2.val2 val2_2
from table1 t1 left join table2 t2 on t1.id = t2.id
union
select * from table1 t1 right join table2 t2 on t1.id = t2.id
) x