通过加入相同日期或最接近的先前日期（不仅仅是精确匹配）来合并两个表

Question

我有两个表：

• CustomerID
• Lead_Date
• Lead_Source

and

• CustomerID
• Product_Interest_Date
• Product_Interest

我想两个创建一个表，其中对于每个CustomerID，每个Product_Interest都连接到Lead_Source，该Lead_Source是最近的日期（但不晚于此）。决赛桌将是：

• CustomerID
• Product_Interest_Date
• Product_Interest
• Lead_Date (the closest entry in time to Product_Interest_Date)
• Lead_Source (the Lead_Source of the closest Lead_Date)

到目前为止，我可以加入表格，并创建一个新字段来计算最接近的日期而不用重复，但是当我尝试使用Min进行分组时，我仍然会获得多个排列（每个Lead_Date到每个Product_Interest）。这是代码：

SELECT Min(Int(Abs([Test_PI]![Product_Interest_Date]-[Test_Leads]![Lead_Date])))
       AS Lead_PI_Link, 
       Test_Leads.CustomerID,
       Test_PI.Product_Interest_Date, 
       Test_PI.Product_Interest,
       Test_Leads.Lead_Date, 
       Test_Leads.Lead_Source
FROM Test_Leads INNER JOIN Test_PI ON Test_Leads.CustomerID = Test_PI.CustomerID
GROUP BY Test_Leads.CustomerID,
         Test_PI.Product_Interest_Date,
         Test_PI.Product_Interest, 
         Test_Leads.Lead_Date,
         Test_Leads.Lead_Source
HAVING (((Test_Leads.CustomerID)="C6UJ9A002Q2P"));

该CustomerID在Test_Leads中有4个条目，在Product_Interest中有4个条目。该查询的结果提供了16个结果，而不是所需的4个结果。如果日期完全匹配，我可以添加一个条件，使日期差为“0”，但是，有时这些日期会偏移1天，有时很多天。

我正在使用Access，并且希望使用“本机”解决方案，但现在可以解决任何问题！

Answer 1

Test_PI

CustomerID  Product_Interest_Date  Product_Interest
----------  ---------------------  ----------------
         1  2014-09-07             Interest1       
         1  2014-09-08             Interest2       
         1  2014-09-15             Interest3       
         1  2014-09-28             Interest4

Test_Leads

CustomerID  Lead_Date   Lead_Source
----------  ----------  -----------
         1  2014-09-07  Source1    
         1  2014-09-14  Source2    
         2  2014-09-15  Source3    
         1  2014-09-21  Source4

这里的技巧是使用不相等的联接作为子查询的一部分，以标识每个Product_Interest_Date的最新Lead_Date。查询

SELECT 
    pi.CustomerID, 
    pi.Product_Interest_Date, 
    l.Lead_Date
FROM 
    Test_PI pi 
    INNER JOIN 
    Test_Leads l
        ON pi.CustomerID = l.CustomerID 
            AND pi.Product_Interest_Date >= l.Lead_Date

returns

CustomerID  Product_Interest_Date  Lead_Date 
----------  ---------------------  ----------
         1  2014-09-07             2014-09-07
         1  2014-09-08             2014-09-07
         1  2014-09-15             2014-09-07
         1  2014-09-15             2014-09-14
         1  2014-09-28             2014-09-07
         1  2014-09-28             2014-09-14
         1  2014-09-28             2014-09-21

请注意，对于09-15如何返回两个匹配项，对于09-28如何返回三个匹配项。我们只对最新查询感兴趣，因此我们将对该查询进行一些调整

SELECT 
    pi.CustomerID, 
    pi.Product_Interest_Date, 
    Max(l.Lead_Date) AS MaxOfLead_Date
FROM 
    Test_PI pi 
    INNER JOIN 
    Test_Leads l
        ON pi.CustomerID = l.CustomerID 
            AND pi.Product_Interest_Date >= l.Lead_Date
GROUP BY 
    pi.CustomerID, 
    pi.Product_Interest_Date

which returns

CustomerID  Product_Interest_Date  MaxOfLead_Date
----------  ---------------------  --------------
         1  2014-09-07             2014-09-07    
         1  2014-09-08             2014-09-07    
         1  2014-09-15             2014-09-14    
         1  2014-09-28             2014-09-21

现在，我们可以将两个表与该查询一起加入，以将其全部组合在一起

SELECT 
    Test_PI.CustomerID,
    Test_PI.Product_Interest_Date,
    Test_PI.Product_Interest,
    Test_Leads.Lead_Date,
    Test_Leads.Lead_Source
FROM
    (
        Test_PI
        INNER JOIN
        (
            SELECT 
                pi.CustomerID, 
                pi.Product_Interest_Date, 
                Max(l.Lead_Date) AS MaxOfLead_Date
            FROM 
                Test_PI pi 
                INNER JOIN 
                Test_Leads l
                    ON pi.CustomerID = l.CustomerID 
                        AND pi.Product_Interest_Date >= l.Lead_Date
            GROUP BY 
                pi.CustomerID, 
                pi.Product_Interest_Date 
        ) latest
            ON Test_PI.CustomerID = latest.CustomerID
                AND Test_PI.Product_Interest_Date = latest.Product_Interest_Date
    )
    INNER JOIN
    Test_Leads
        ON Test_Leads.CustomerID = latest.CustomerID
            AND Test_Leads.Lead_Date = latest.MaxOfLead_Date

返回

CustomerID  Product_Interest_Date  Product_Interest  Lead_Date   Lead_Source
----------  ---------------------  ----------------  ----------  -----------
         1  2014-09-07             Interest1         2014-09-07  Source1    
         1  2014-09-08             Interest2         2014-09-07  Source1    
         1  2014-09-15             Interest3         2014-09-14  Source2    
         1  2014-09-28             Interest4         2014-09-21  Source4