求助：如何高效关联两张表获取客户起止日期对应的Status值

我有一张客户表，包含客户、起始日期、结束日期；另一张客户日期状态表，记录了客户在不同日期的状态。需要把起止日期对应的状态填充到客户表中，得到包含起始状态和结束状态的结果表。

表结构 #

客户表 #

Customers   Start Date  Enddate
A           01/01/2024  30/06/2024
B           01/01/2024  30/05/2024
C           05/05/2023  01/01/2024
D           28/02/2023  01/01/2024
E           07/06/2023  20/02/2024

客户日期状态表 #

Customers   Date        Status
A           01/01/2024  Active
B           01/01/2024  Active
C           01/01/2024  Inactive
D           01/01/2024  Inactive
E           01/01/2024  Active
A           05/05/2023  Active
B           05/05/2023  Active
C           05/05/2023  Inactive
D           05/05/2023  Inactive
E           05/05/2023  Active
A           28/02/2023  Active
B           28/02/2023  Active
C           28/02/2023  Active
D           28/02/2023  Active
E           28/02/2023  Active

预期结果 #

Customers   Start Date  End date    Status at start date    Status at end date
A           01/01/2024  30/06/2024  Active                  Active
B           01/01/2024  30/05/2024  Active                  Active
C           05/05/2023  01/01/2024  Inactive                Active
D           28/02/2023  01/01/2024  Active                  Inactive
E           07/06/2023  20/02/2024  Active                  Active

方法1：两次左连接（直观高效） #

通过两次关联客户日期状态表，分别匹配起始日期和结束日期的状态：

SELECT
    c.Customers,
    c.[Start Date],
    c.Enddate AS [End date],
    s_start.Status AS [Status at start date],
    s_end.Status AS [Status at end date]
FROM 客户表 c
LEFT JOIN 客户日期状态表 s_start
    ON c.Customers = s_start.Customers
    AND c.[Start Date] = s_start.Date
LEFT JOIN 客户日期状态表 s_end
    ON c.Customers = s_end.Customers
    AND c.Enddate = s_end.Date;

如果客户日期状态表中存在某客户对应日期无记录的情况，LEFT JOIN会保留客户表的所有行，状态字段显示NULL，若需默认值可使用COALESCE(s_start.Status, '默认状态')替换。

方法2：子查询获取状态 #

若偏好紧凑写法，可在SELECT中直接查询对应日期的状态：

SELECT
    Customers,
    [Start Date],
    Enddate AS [End date],
    (SELECT Status FROM 客户日期状态表 WHERE Customers = c.Customers AND Date = c.[Start Date]) AS [Status at start date],
    (SELECT Status FROM 客户日期状态表 WHERE Customers = c.Customers AND Date = c.Enddate) AS [Status at end date]
FROM 客户表 c;

性能优化建议 #

为提升查询效率，建议在客户日期状态表上创建复合索引：

CREATE INDEX idx_customer_date ON 客户日期状态表 (Customers, Date);

该索引能让JOIN或子查询快速定位到目标记录，避免全表扫描。

内容的提问来源于stack exchange，提问作者user29435106