MySQL-使用联接获取两个表上的两组数据之间的差异? [英] MySQL - Getting differences between two sets of data on two table using join?
问题描述
假设我有来自多个人的多组报告。如何确定这些数据之间的变化并决定将哪些数据合并到特定数据库。
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-使用联接获取两个表上的两组数据之间的差异?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!