I have a table 'new_foobar' that is empty. it has columns 'fbc_primary_key', 'fbc_two', fbc_three, and fbc_four.
我有一个表'new_foobar'是空的。它有列'fbc_primary_key'，'fbc_two'，fbc_three和fbc_four。
fbc_three defaults to 'apple'
fbc_three默认为'apple'
then I have a table 'foo', with a columns 'fc_pimary_key', 'fc_two'. And a table 'bar' with columns 'bc_primary_key', 'bc_two', 'bc_three'.
然后我有一个表'foo'，列'fc_pimary_key'，'fc_two'。还有一个表'bar'，列'bc_primary_key'，'bc_two'，'bc_three'。
fbc_primary_key, bc_primary_key, and fc_primary_key, are all primary keys of their respective tables.
fbc_primary_key，bc_primary_key和fc_primary_key都是各自表的主键。
how does one insert fc_two, into fbc_two, and bc_three into fbc_four.
如何将fc_two插入fbc_two，将bc_three插入fbc_four。
TABLE foo fc_primary_key | fc_two | ------------------------- 1 | hello | 2 | goodbye| TABLE bar bc_primary_key | bc_two | bc_three | ------------------------------------ 1 | abc | 123 | 2 | def | 456 | *NOTE bar and foo have a 1-to-1 relationship. TABLE foobar fbc_primary_key | fbc_two | fbc_three | fbc_four | --------------------------------------- /* empty */ ...AFTER SOME SQL MAGIC.... TABLE foobar fbc_primary_key | fbc_two | fbc_three | fbc_four | --------------------------------------------------- 1 | hello | apple | 123 | 2 | goodbye | apple | 456 |
The problem I am having with is, how to get the columns of data to fall under the foobar column.
我遇到的问题是，如何让数据列落在foobar列下。
2 个解决方案
#1
2
insert into foobar
select
ifnull(f.fbc_primary_key, b.fbc_primary_key),
fbc_two,
fbc.three
from
foo f
full outer join bar b on b.fbc_primary_key = f.fbc_primary_key
If you only want records that exist in both tables, you can use inner join. You can skip the ifnull too, because both f and b will have an id.
如果只需要两个表中都存在的记录，则可以使用内部联接。你也可以跳过ifnull，因为f和b都有一个id。
#2
1
If the relationship between foo and bar is one-to-one and required on both sides:
如果foo和bar之间的关系是一对一的，并且双方都需要：
INSERT INTO foobar (
SELECT f.fc_primary_key, f.fc_two, b.bc_three
FROM foo f INNER JOIN bar b ON f.fc_primary_key = b.bc_primary_key);
#1
2
insert into foobar
select
ifnull(f.fbc_primary_key, b.fbc_primary_key),
fbc_two,
fbc.three
from
foo f
full outer join bar b on b.fbc_primary_key = f.fbc_primary_key
If you only want records that exist in both tables, you can use inner join. You can skip the ifnull too, because both f and b will have an id.
如果只需要两个表中都存在的记录，则可以使用内部联接。你也可以跳过ifnull，因为f和b都有一个id。
#2
1
If the relationship between foo and bar is one-to-one and required on both sides:
如果foo和bar之间的关系是一对一的，并且双方都需要：
INSERT INTO foobar (
SELECT f.fc_primary_key, f.fc_two, b.bc_three
FROM foo f INNER JOIN bar b ON f.fc_primary_key = b.bc_primary_key);