mysql - 合并两个没有关系的表的列

Question

我需要合并两个表的不同行的列。它应该将 NULL 值放入行数较少的表中。

示例:

假设这两个简单的表:

table_one

+----+-----------+---------------------+
| id | title     | date                |
+----+-----------+---------------------+
| 10 | Good      | 2014-10-08 05:13:00 |
| 11 | NotBad    | 2014-10-24 00:00:00 |
| 12 | Excellent | 2014-10-26 14:00:00 |
| 13 | Bad       | 2014-10-11 19:31:23 |
+----+-----------+---------------------+

table_two

+----+------+
| id | name |
+----+------+
|  1 | Sara |
|  2 | Alex |
+----+------+

我需要什么输出:

+----+-----------+---------------------+------+------+
| id | title     | date                |  id  | name |
+----+-----------+---------------------+------+------+
| 10 | Good      | 2014-10-08 05:13:00 |   1  | Sara |
| 11 | NotBad    | 2014-10-24 00:00:00 |   2  | Alex |
| 12 | Excellent | 2014-10-26 14:00:00 | NULL | NULL |
| 13 | Bad       | 2014-10-11 19:31:23 | NULL | NULL |
+----+-----------+---------------------+----+------+

到目前为止我尝试过的:

SELECT table_one.*, table_two.* FROM table_one, table_two

但这不是我的愿望!它将返回笛卡尔积

附:

这两个表之间没有任何关系。

Answer 1

如果您假设第一个表有更多行，则可以通过使用变量生成键来完成此操作:

select t1.*, t2.*
from (select t1i.*, (@rn1 := @rn1 + 1) as rn
      from table_one t1i cross join (select @rn1 := 0) vars
     ) t1 left join
     (select t2i.*, (@rn2 := @rn2 + 1) as rn
      from table_two t2i cross join (select @rn2 := 0) vars
     ) t2
     on t1.rn = t2.rn;

您将有两个名为 rn 的剩余列，如果您不需要，请列出您的列而不是 select t1.*, t2.* .