sql - Access/SQL：如何在 Access 查询中获取下一个最接近的日期？

Question

我有两个表，我想加入并将 table 中的日期与 tableDATE_GAPS中最接近的下一个日期匹配MISSING_DATES。表格如下：

表MISSING_DATES：

UPC  | Date
-----+----------
1111 | 8/18/2018
1111 | 8/19/2018
1111 | 8/25/2018
1222 | 8/18/2018
1222 | 9/1/2018

表DATE_GAPS：

UPC  | Date
-----+----------
1111 | 8/16/2018
1111 | 8/17/2018
1111 | 8/20/2018
1111 | 8/27/2018
1222 | 8/20/2018
1222 | 9/4/2018

新表：

UPC  | Date      | Closest Next Date
-----+-----------+------------------
1111 | 8/18/2018 | 8/20/2018
1111 | 8/19/2018 | 8/20/2018
1111 | 8/25/2018 | 8/27/2018
1222 | 8/18/2018 | 8/20/2018
1222 | 9/1/2018  | 9/4/2018

这是我目前拥有的脚本，但加载时间太长：

SELECT MISSING_DATES.UPC
  , MISSING_DATES.DATE
  , (
      SELECT MIN(DATE_GAPS.DATE)
      FROM DATE_GAPS
      INNER JOIN MISSING_DATES
        ON DATE_GAPS.UPC = MISSING_DATES.UPC
      WHERE DATE_GAPS.DATE > MISSING_DATES.DATE
    ) AS [Closest Next Date]
FROM MISSING_DATES
  , DATE_GAPS
ORDER BY MISSING_DATES.DATE
  , MISSING_DATES.UPC;

我不知道如何优化当前脚本或重写以获得我想要输出的内容。

我也尝试使用以下有效的代码，但它给了我最接近的日期（之前或之后）：

SELECT TOP 1 MISSING_DATES.UPC, MISSING_DATES.DATE, 
DATE_GAPS.DATE, DATE_GAPS.UPC 
FROM MISSING_DATES INNER JOIN DATE_GAPS ON MISSING_DATES.UPC=DATE_GAPS.UPC 
WHERE (((DATE_GAPS.UPC)=[MISSING_DATES].[UPC])) ORDER BY
Abs(DateDiff("d",MISSING_DATES.DATE,DATE_GAPS.DATE));

提前致谢！

标签： sqlms-access

Answer 1

您可以使用相关子查询：

select md.*,
       (select top 1 dg.date
        from date_gaps as dg
        where dg.upc = md.upc and dg.date >= md.date
        order by dg.date
       ) as [Closest Next Date]
from missing_dates as md;

对于性能，索引data_gaps(upc, date)会有所帮助。