MySQL:不合并或合并两个不同的表

I need to merge two tables:

我需要合并两个表:

• Both have a primary key-column date, but with different values (different time intervals).
• 两者都有主键-列日期，但值不同(时间间隔不同)。
• Both have different (unknown) columns: I don't know the names of the columns (same column-name may occur in both tables), I don't know how many columns, but all of the same type.
• 它们都有不同的(未知的)列:我不知道列的名称(两个表中可能都有相同的列名称)，我不知道有多少列，但都是相同的类型。

An example:

一个例子:

table1
date       | colA | colB | colC
2011-02-02 | 1.09 | 1.03 | 1.04

table2
date       | col1 | col2 | col3 | col4
2011-02-03 | 1.03 | 1.02 | 1.07 | 1.03

the result of the query should look like this:

查询的结果应该如下所示:

tableResult
date       | colA | colB | colC | col1 | col2 | col3 | col4
2011-02-02 | 1.09 | 1.03 | 1.04 | null | null | null | null
2011-02-03 | null | null | null | 1.03 | 1.02 | 1.07 | 1.03

This will not work:

这将不工作:

• INNER JOIN because it will only return the intersection between table1 and table2,
• 内连接，因为它只返回表1和表2之间的交点，
• OUTER JOIN returns intersection + values only from left table (or right table if right join is used)
• 外部联接只从左表返回交集+值(如果使用正确的连接，则返回右表)
• UNION because the count of columns may differ.
• 因为列数可能不同。

Any Ideas?

什么好主意吗?

Christoph

Christoph

2 个解决方案

#1

You can create a temp table with the union of just the date column, and then use the temp table to left outer join with the other 2.

您可以创建一个临时表，其中只包含日期列的联合，然后使用临时表将外部连接与其他2连接。

Example:

例子:

DROP TABLE temptbl IF EXISTS;
CREATE TEMPORARY TABLE temptbl (myDate DATETIME PRIMARY KEY)
    AS (SELECT MyDate FROM table1)
    UNION (SELECT MyDate FROM table2)
    ORDER BY MyDate;
SELECT * FROM temptbl
    LEFT OUTER JOIN table1 USING (MyDate)
    LEFT OUTER JOIN table2 USING (MyDate);

#2

select coalesce(t2.data,'')+coalesce(t1.data,'') as data,
    t2.col1, t2.col2, t2.col3 ,t2.col4 ,t1.cola ,t1.colb, t1.colc
from table2 as t2
full outer join 
table1 t1
on t2.data = 2011-02-03
or t1.data = 2011-02-02