合并两个数据集按照ID和日期。

huangapple go评论118阅读模式
英文:

combine two datasets by ID and Date

问题

我想要合并两个看起来像这样的数据集:

ID 日期 X
1234 01.01.2022 15
1235 02.01.2022 244
ID 日期 Y
1234 01.01.2022 7
1235 04.01.2022 18

合并后的数据集应该如下所示:

ID 日期 X Y
数字 dd.MM.YYYY 数字 数字
1234 01.01.2022 15 7
1235 02.01.2022 244 0
1235 04.01.2022 0 18

有时候,对于特定的日期,可能没有X和Y的数值。

最终,我需要一个表格/图表,允许我筛选一个ID,并根据它们的日期(月份)提供所有的X和Y数据。

我对Access不太熟悉,正在通过实践学习。在网上搜索时找不到解决方法,所以任何帮助都将不胜感激。

英文:

i want to combine two datasets that look like this:

ID Date X
1234 01.01.2022 15
1235 02.01.2022 244
ID Date Y
1234 01.01.2022 7
1235 04.01.2022 18

into one that looks like this

ID Date X Y
Number dd.MM.YYYY Number Number
1234 01.01.2022 15 7
1235 02.01.2022 244 0
1235 04.01.2022 0 18

Sometimes for a certain date, there might not be an X and a Y value

in the end, I need a table/chart that allows me to filter for one ID and gives me all the X and Y data for said ID depending on their dates (months).

I am really new to the whole Access thing and am learning by doing. Couldn't find a way to do this by Googling, so any help is appreciated.

答案1

得分: 0

首先,使用联合查询将多个查询组合成一个结果

Select ID, [Date], X As X1, 0 As Y2 From Dataset1
Union All 
Select ID, [Date], 0 As X1, Y As Y2 From Dataset2

将其保存为 DatasetU

接下来,在一个聚合查询中使用它:

Select 
    DatasetU.ID, 
    DatasetU.[Date], 
    Sum(DatasetU.X1) AS X, 
    Sum(DatasetU.Y2) AS Y
From 
    DatasetU
Group By 
    DatasetU.ID, 
    DatasetU.[Date]

输出:

合并两个数据集按照ID和日期。

英文:

First, Use a union query to combine multiple queries into a single result:

Select ID, [Date], X As X1, 0 As Y2 From Dataset1
Union All 
Select ID, [Date], 0 As X1, Y As Y2 From Dataset2

Save it as DatasetU.

Next, use this in an aggregating query:

Select 
    DatasetU.ID, 
    DatasetU.[Date], 
    Sum(DatasetU.X1) AS X, 
    Sum(DatasetU.Y2) AS Y
From 
    DatasetU
Group By 
    DatasetU.ID, 
    DatasetU.[Date]

Output:

合并两个数据集按照ID和日期。

答案2

得分: 0

这是一个选项。

SELECT Table1.ID, Table1.Date, X, Y FROM Table2 
    RIGHT JOIN Table1 ON (Table2.Date = Table1.Date) AND (Table2.ID = Table1.ID)
UNION SELECT Table2.ID, Table2.Date, X, Y FROM Table1 
    RIGHT JOIN Table2 ON (Table1.Date = Table2.Date) AND (Table1.ID = Table2.ID);
英文:

This is an option.

SELECT Table1.ID, Table1.Date, X, Y FROM Table2 
    RIGHT JOIN Table1 ON (Table2.Date = Table1.Date) AND (Table2.ID = Table1.ID)
UNION SELECT Table2.ID, Table2.Date, X, Y FROM Table1 
    RIGHT JOIN Table2 ON (Table1.Date = Table2.Date) AND (Table1.ID = Table2.ID);

huangapple
  • 本文由 发表于 2023年3月8日 19:05:32
  • 转载请务必保留本文链接:https://go.coder-hub.com/75672198.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定