OK, so I have two tables (views actually) that both have the same structure. Each has a column with a date and a column with a numerical value.
好的,所以我有两个表(实际上是视图),它们都具有相同的结构。每个都有一个带有日期的列和一个带有数值的列。
The dates in both tables will be almost continuous and cover the same period. The data will consequently be largely the same, but a date in one table may not appear in the other.
两个表格中的日期几乎是连续的,涵盖同一时期。因此,数据将基本相同,但一个表中的日期可能不会出现在另一个表中。
I want to have one table, with one date column and two numerical columns. Where there is no date in one table, the corresponding numerical field should be null.
我想要一个表,一个日期列和两个数字列。如果一个表中没有日期,则相应的数字字段应为null。
Any approaches out there?
有什么方法吗?
PS This is not the same question: How can I merge two MySQL tables?
PS这不是同一个问题:如何合并两个MySQL表?
2 个解决方案
#1
VladiatOr's solution can be shortened by having the first part select records present in both and only in the first table and then adding those only present in the second table.
VladiatOr的解决方案可以通过让第一部分选择记录同时存在于第一个表中并且仅存储在第一个表中然后添加仅存在于第二个表中的那些来缩短。
SELECT t1.td, t1.val as val1, t2.val as val2
FROM table1 as t1
LEFT JOIN table2 as t2
ON t1.td = t2.td
UNION
SELECT t2.td, t1.val as val1, t2.val as val2
FROM table2 as t2
LEFT JOIN table1 as t1
ON t2.td = t1.td
WHERE t1.td IS NULL
See also the comment from Tobias Riemenschneider in the MySQL Reference on JOIN Syntax.
另请参阅关于JOIN语法的MySQL参考中Tobias Riemenschneider的评论。
#2
This query is a union of 3 queries: The first joins the records that are common in both tables. The second adds the ones that only exist in table1. The third adds the records that are only in table2.
此查询是3个查询的并集:第一个查询连接两个表中常见的记录。第二个添加仅存在于table1中的那些。第三个添加仅在table2中的记录。
SELECT t1.td, t1.val as val1, t2.val as val2
FROM table1 as t1, table2 as t2
WHERE t1.dt = t2.dt
UNION
SELECT t1.td, t1.val as val1, null as val2
FROM table1 as t1
LEFT JOIN table2 as t2
ON t1.td = t2.td
WHERE t2.td IS NULL
UNION
SELECT t2.td, null as val1, t2.val as val2
FROM table2 as t2
LEFT JOIN table1 as t1
ON t2.td = t1.td
WHERE t1.td IS NULL
#1
VladiatOr's solution can be shortened by having the first part select records present in both and only in the first table and then adding those only present in the second table.
VladiatOr的解决方案可以通过让第一部分选择记录同时存在于第一个表中并且仅存储在第一个表中然后添加仅存在于第二个表中的那些来缩短。
SELECT t1.td, t1.val as val1, t2.val as val2
FROM table1 as t1
LEFT JOIN table2 as t2
ON t1.td = t2.td
UNION
SELECT t2.td, t1.val as val1, t2.val as val2
FROM table2 as t2
LEFT JOIN table1 as t1
ON t2.td = t1.td
WHERE t1.td IS NULL
See also the comment from Tobias Riemenschneider in the MySQL Reference on JOIN Syntax.
另请参阅关于JOIN语法的MySQL参考中Tobias Riemenschneider的评论。
#2
This query is a union of 3 queries: The first joins the records that are common in both tables. The second adds the ones that only exist in table1. The third adds the records that are only in table2.
此查询是3个查询的并集:第一个查询连接两个表中常见的记录。第二个添加仅存在于table1中的那些。第三个添加仅在table2中的记录。
SELECT t1.td, t1.val as val1, t2.val as val2
FROM table1 as t1, table2 as t2
WHERE t1.dt = t2.dt
UNION
SELECT t1.td, t1.val as val1, null as val2
FROM table1 as t1
LEFT JOIN table2 as t2
ON t1.td = t2.td
WHERE t2.td IS NULL
UNION
SELECT t2.td, null as val1, t2.val as val2
FROM table2 as t2
LEFT JOIN table1 as t1
ON t2.td = t1.td
WHERE t1.td IS NULL