mysql - 如何从多个表中选择创建表？

Question

如何从具有可变列的多个表创建一个表(不能使用联合)？

mysql> select * from a;
+------+------+
| id   | name |
+------+------+
| 1    | Ruby |
+------+------+

mysql> select * from b;
+------+------+
| pid  | name |
+------+------+
| 1    | Java |
| 1    | C++  |
+------+------+

我想要的是...

+------+------+------+------+
| id   | name | pid  | bname|
+------+------+------+------+
| 1    | Ruby |      |      |
|      |      | 1    | C++  |
|      |      | 1    | Java |
+------+------+------+------+

我尝试过的。

mysql> create table t as select * from a; # worked fine
mysql> alter table t as select pid, name as bname from b; #didn't work

Answer 1

你可以用这种相当神秘的方式做到这一点:

select a.*, b.*
from a left join
     b
     on 1 = 0
union all
select a.*, b.*
from b left join
     a
     on 1 = 0;

这是在 MySQL 中实现的一种方式:

select a.*, b.*
from a full outer join
     b
     on 1 = 0;

但是，它需要一个 union .如果你想避免 union，你可以分两步完成。 :

create table ab as
    select a.*, b.*
    from a left join
         b
         on 1 = 0;

insert into ab
    select a.*, b.*
    from b left join
         a
         on 1 = 0;