如何基于最近历史日期关联SQL表以匹配订单时会员状态

现有两张业务表：

• 表1：客户订单信息表，记录客户的订单明细与下单日期
• 表2：客户会员状态表，记录客户会员状态的变更历史与生效日期

需求是关联两表，获取每个订单下单时对应的客户会员状态，无匹配状态则返回NULL。尝试过min/max、>=、GROUP BY HAVING等方法未成功，以下是可行的SQL方案（注：日期为欧/澳格式，即日/月/年）。

表结构示例 #

客户订单信息表 #

x---------x--------x-------------x
 cust_id  |  item  |  order date |
x---------x--------x-------------x
   1      |  100   |  01/01/2020 |
   1      |  112   |  03/07/2022 |
   2      |  100   |  01/02/2020 |
   2      |  168   |  05/03/2022 |
   3      |  200   |  15/06/2021 |
----------x--------x-------------x

客户会员状态表 #

x---------x--------x-------------x
  cust_id | Status | startdate   |
x---------x--------x-------------x
    1     | silver | 01/01/2019  |
    1     | bronze | 05/12/2019  |
    1     | gold   | 05/06/2022  |
    2     | silver | 24/12/2021  |
----------x--------x-------------x

期望结果 #

x---------x--------x-------------x----------x
 cust_id  |  item  |  order date |  status  |
x---------x--------x-------------x----------x
   1      |  100   |  01/01/2020 |  bronze  |
   1      |  112   |  03/07/2022 |  gold    |
   2      |  100   |  01/02/2020 |  NULL    |
   2      |  168   |  05/03/2022 |  silver  |
   3      |  200   |  15/06/2021 |  NULL    |
----------x--------x-------------x----------x

解决方案 #

核心逻辑：对每个订单，匹配同一客户下生效日期早于等于下单日期的最新会员状态，无匹配则返回NULL。

方案1：窗口函数实现（兼容MySQL 8+、PostgreSQL、SQL Server等） #

利用ROW_NUMBER()窗口函数对符合条件的会员状态按生效日期倒序排序，取最新的一条：

SELECT 
    o.cust_id,
    o.item,
    o.`order date`,
    m.status
FROM (
    SELECT 
        o.*,
        m.status,
        ROW_NUMBER() OVER (
            PARTITION BY o.cust_id, o.`order date`, o.item 
            ORDER BY STR_TO_DATE(m.startdate, '%d/%m/%Y') DESC
        ) AS rn
    FROM `customer order information` o
    LEFT JOIN `customer membership status` m 
        ON o.cust_id = m.cust_id
        AND STR_TO_DATE(m.startdate, '%d/%m/%Y') <= STR_TO_DATE(o.`order date`, '%d/%m/%Y')
) t
WHERE rn = 1;

方案2：子查询关联（兼容旧版本数据库） #

通过子查询找到每个订单对应的最新有效会员状态生效日期，再关联获取状态：

SELECT 
    o.cust_id,
    o.item,
    o.`order date`,
    m.status
FROM `customer order information` o
LEFT JOIN `customer membership status` m 
    ON o.cust_id = m.cust_id
    AND STR_TO_DATE(m.startdate, '%d/%m/%Y') = (
        SELECT MAX(STR_TO_DATE(m2.startdate, '%d/%m/%Y'))
        FROM `customer membership status` m2
        WHERE m2.cust_id = o.cust_id
        AND STR_TO_DATE(m2.startdate, '%d/%m/%Y') <= STR_TO_DATE(o.`order date`, '%d/%m/%Y')
    );

注意事项 #

• 日期转换函数需根据数据库调整：
  • SQL Server：替换STR_TO_DATE(xxx, '%d/%m/%Y')为CONVERT(DATE, xxx, 103)
  • PostgreSQL：替换为TO_DATE(xxx, 'DD/MM/YYYY')
• 表名含空格时，需用反引号（MySQL）或方括号（SQL Server）包裹。

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