如何通过SQL根据订单时间匹配合同对应的产品价格？

假设你的两张表字段如下（若实际字段不同，替换成对应名称即可）：

• contract表：contract_id（合同ID）、product_id（产品ID）、order_time（下单时间）
• price表：product_id（产品ID）、from_time（价格生效起始时间）、end_time（价格生效结束时间）、price（产品价格）

核心逻辑是通过产品ID关联，同时让合同的下单时间落在价格表的生效时间区间内，以此匹配对应价格。

基础查询SQL #

SELECT 
    c.contract_id,
    c.product_id,
    c.order_time,
    p.price
FROM 
    contract c
JOIN 
    price p ON c.product_id = p.product_id
WHERE 
    c.order_time >= p.from_time 
    AND c.order_time <= p.end_time;

注意事项 #

• 如果你的时间区间是左闭右开（比如end_time是区间的截止日但不包含当天），需要把条件改成c.order_time < p.end_time，具体根据业务规则调整。
• 若存在同一产品同一时间点有多个重叠价格区间的情况，需要额外添加筛选条件（比如取最新创建的价格、优先级最高的价格），示例：

SELECT 
    c.contract_id,
    c.product_id,
    c.order_time,
    p.price
FROM 
    contract c
JOIN 
    price p ON c.product_id = p.product_id
WHERE 
    c.order_time >= p.from_time 
    AND c.order_time <= p.end_time
QUALIFY 
    ROW_NUMBER() OVER (PARTITION BY c.contract_id ORDER BY p.update_time DESC) = 1;

（QUALIFY是BigQuery、Snowflake等数据库支持的语法；如果是MySQL等不支持的数据库，可以用子查询或关联查询实现类似逻辑）

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