MySQL-使用联接获取两个表上的两组数据之间的差异？

问题描述：

假设我有来自多个人的多组报告。如何确定这些数据之间的变化并决定将哪些数据合并到特定数据库。

Assuming that I have multiple sets of reports from multiple individuals. How do I identify the changes between these datas and decide which to merge to a specific database.

适用情况下的情况

当数据存在于数据1中但不存在数据2

当数据存在于数据2中但不存在数据1

当两个表中的数据都不匹配时（无论是销售/收入），并且假设有更多列而不是可能具有不同值的收入

数据1

Date        Sales   Revenue
2016-01-01  27      30
2016-01-03  12      10
2016-01-04  48      50

数据2

Date        Sales   Revenue
2016-01-01  27      10
2016-01-02  31      40
2016-01-04  48      50

期望的结果

Date        Sales   T1 Revenue  T2 Revenue
2016-01-01  27      30          10
2016-01-02  31      NULL        40
2016-01-03  12      10          NULL
2016-01-04  48      50          50

我尝试了多种方法，包括UNION和JOIN的组合，现在似乎对我没有任何作用。

I have tried various method including a combination of UNION and JOIN, nothing seem to work for me right now.

我现在得到的最接近的是以下内容。

The closest I got right now is the following.

SELECT d1.date,
       d1.sales,
       d1.revenue AS T1,
       d2.revenue AS T2

FROM dataset1 d1

RIGHT JOIN dataset2 d2 ON d1.date = d2.date

WHERE d1.revenue <> d2.revenue
OR (d1.revenue IS NOT NULL AND d2.revenue IS NULL)
OR (d1.revenue IS NULL AND d2.revenue IS NOT NULL)

左联接/右联接之间的跳转仅取决于哪一侧缺少数据。

The jump between left join / right join will only work depending on which side has the missing data.

搜索了站点，但没有找到适合我的解决方案= /

Searched the site but haven't found a solution working for me =/

答

您应该使用完全加入。

SELECT coalesce(d1.date,d2.date) dt,
       coalesce(d1.sales,d2.sales) sales,
       d1.revenue AS T1Revenue,
       d2.revenue AS T2Revenue
FROM dataset1 d1
FULL JOIN dataset2 d2 ON d1.date = d2.date

使用 coalesce 来获取给定表中都不存在的列的非空值。

Use coalesce to get the non-null value for a column when it is not present in either of the given tables.

由于MySQL不支持完全加入，可以使用左和右的组合来完成加入 u nion 组合结果。

As MySQL doesn't support full join, this can be done with a combination of left and right joins with a union combining the results.

SELECT d1.date dt,
       d1.sales sales,
       d1.revenue AS T1Revenue,
       d2.revenue AS T2Revenue
FROM dataset1 d1
LEFT JOIN dataset2 d2 ON d1.date = d2.date
UNION
SELECT d2.date dt,
       d2.sales sales,
       d1.revenue AS T1Revenue,
       d2.revenue AS T2Revenue
FROM dataset1 d1
RIGHT JOIN dataset2 d2 ON d1.date = d2.date
ORDER BY 1

MySQL-使用联接获取两个表上的两组数据之间的差异？

相关推荐