英文:
MS Access Join 2 tables with exact match and also range match
问题
我想要在MS Access中连接两个表格，一个是精确值，一个是值范围。我不知道如何继续操作。
这是我的结果表格
我想要与限制表格连接
这里的Info和Entity在两个表格中匹配，LIMIT应该是RefDate在From_Date和To_Date范围内的地方。
所以输出应该如下所示
限制表格
| Info | Entity | From_Date | To_Date | Limit |
|---|---|---|---|---|
| M1 | PARENT | 31/03/2023 | 01/01/2050 | 384% |
| M1 | PARENT | 28/02/2022 | 31/03/2023 | 350% |
| M1 | PARENT | 28/02/2021 | 27/02/2022 | 275% |
| M1 | PARENT | 01/01/2020 | 04/03/2021 | 235% |
| M1 | PARENT | 01/12/2019 | 31/12/2019 | 209% |
| M2 | CHILD | 21/10/2020 | 01/01/2050 | 115% |
| M2 | CHILD | 01/12/2019 | 20/10/2020 | 140% |
| M2 | PARENT | 21/10/2022 | 01/01/2050 | 135% |
| M2 | PARENT | 01/12/2019 | 21/10/2022 | 140% |
| M2 | PARENT | 05/02/2019 | 30/11/2019 | 138% |
结果表格
| Info | Entity | RefDate | Value |
|---|---|---|---|
| M2 | PARENT | 31/07/2023 | 168.9% |
| M2 | CHILD | 31/07/2023 | 482.01% |
| M1 | PARENT | 31/07/2023 | 278.53% |
| M1 | CHILD | 31/07/2023 | 482.01% |
| M2 | PARENT | 10/06/2023 | 164.35% |
| M2 | CHILD | 10/06/2023 | 150.17% |
| M1 | PARENT | 10/06/2023 | 344.79% |
| M1 | CHILD | 10/06/2023 | 213.94% |
查询输出
| Info | Entity | RefDate | Value | Limit |
|---|---|---|---|---|
| M2 | PARENT | 31/07/2023 | 168.9% | 135% |
| M2 | CHILD | 31/07/2023 | 482.01% | 115% |
| M1 | PARENT | 31/07/2023 | 278.53% | 384% |
| M1 | CHILD | 31/07/2023 | 482.01% | |
| M2 | PARENT | 10/06/2023 | 164.35% | 140% |
| M2 | CHILD | 10/06/2023 | 150.17% | 115% |
| M1 | PARENT | 10/06/2023 | 344.79% | 350% |
| M1 | CHILD | 10/06/2023 | 213.94% |
英文:
I want to join to tables in MS access with both exact values and range of values. I am at a loss on how to proceed
This is my results table
I want to join with limits table
here Info and Entity match in both the tables and LIMIT should be where RefDate is within range From_Date and To_Date
so the output would look like
Limits_Table
| Info | Entity | From_Date | To_Date | Limit |
|---|---|---|---|---|
| M1 | PARENT | 31/03/2023 | 01/01/2050 | 384% |
| M1 | PARENT | 28/02/2022 | 31/03/2023 | 350% |
| M1 | PARENT | 28/02/2021 | 27/02/2022 | 275% |
| M1 | PARENT | 01/01/2020 | 04/03/2021 | 235% |
| M1 | PARENT | 01/12/2019 | 31/12/2019 | 209% |
| M2 | CHILD | 21/10/2020 | 01/01/2050 | 115% |
| M2 | CHILD | 01/12/2019 | 20/10/2020 | 140% |
| M2 | PARENT | 21/10/2022 | 01/01/2050 | 135% |
| M2 | PARENT | 01/12/2019 | 21/10/2022 | 140% |
| M2 | PARENT | 05/02/2019 | 30/11/2019 | 138% |
Results_Table
| Info | Entity | RefDate | Value |
|---|---|---|---|
| M2 | PARENT | 31/07/2023 | 168.9% |
| M2 | CHILD | 31/07/2023 | 482.01% |
| M1 | PARENT | 31/07/2023 | 278.53% |
| M1 | CHILD | 31/07/2023 | 482.01% |
| M2 | PARENT | 10/06/2023 | 164.35% |
| M2 | CHILD | 10/06/2023 | 150.17% |
| M1 | PARENT | 10/06/2023 | 344.79% |
| M1 | CHILD | 10/06/2023 | 213.94% |
Query_Output
| Info | Entity | RefDate | Value | Limit |
|---|---|---|---|---|
| M2 | PARENT | 31/07/2023 | 168.9% | 135% |
| M2 | CHILD | 31/07/2023 | 482.01% | 115% |
| M1 | PARENT | 31/07/2023 | 278.53% | 384% |
| M1 | CHILD | 31/07/2023 | 482.01% | |
| M2 | PARENT | 10/06/2023 | 164.35% | 140% |
| M2 | CHILD | 10/06/2023 | 150.17% | 115% |
| M1 | PARENT | 10/06/2023 | 344.79% | 350% |
| M1 | CHILD | 10/06/2023 | 213.94% |
答案1
得分: 1
这个SQL语句应该可以工作，但如果结果与限制重叠的日期在同一天，它会排除重复的值。
SELECT RT.Info,
RT.Entity,
RefDate,
ActualValue,
Limit
FROM Results_Table RT LEFT JOIN Limits_Table LT ON RT.Info = LT.Info AND
RT.Entity = LT.Entity AND
RT.RefDate >= LT.From_Date AND
RT.RefDate <= LT.To_Date
英文:
This SQL should work, but will throw out duplicate values if a Result is on the same day that the Limits overlap.
SELECT RT.Info,
RT.Entity,
RefDate,
ActualValue,
Limit
FROM Results_Table RT LEFT JOIN Limits_Table LT ON RT.Info = LT.Info AND
RT.Entity = LT.Entity AND
RT.RefDate >= LT.From_Date AND
RT.RefDate <= LT.To_Date
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论