SQL如何关联带生效日期的成本表与销售表匹配对应成本

实现方案 #

这里提供两种兼容绝大多数主流数据库（MySQL、PostgreSQL、Hive、Spark SQL、SQL Server均支持调整后使用）的实现方法，逻辑清晰适合新手理解：

方法1：关联子查询（最易上手） #

直接在查询销售表时，嵌套子查询取对应产品、小于等于销售日期的最大生效日期对应的成本即可。

SELECT
    s.Date,
    s.Store,
    s.Product,
    s.Qty,
    (SELECT c.Cost 
     FROM Cost_Table c 
     WHERE c.Product = s.Product 
       AND c.Effective_Date <= s.Date
     ORDER BY c.Effective_Date DESC
     LIMIT 1) AS Cost
FROM Sale_Table s

小提示：如果使用SQL Server，把语句里的LIMIT 1替换成TOP 1即可；如果表字段名带空格（比如原表的Effective Date），记得用反引号（MySQL）或双引号（PostgreSQL、SQL Server）包裹字段名。

方法2：成本表补失效日期后关联（性能更好，适合大数据量） #

先给每个产品的成本记录新增End_Date字段，取值为同产品下一条成本生效日期的前1天，最后一条无后续更新的成本的失效日期设为远期日期（比如9999-12-31），再和销售表做区间关联即可。

WITH Cost_With_EndDate AS (
    SELECT
        Product,
        Effective_Date,
        Cost,
        -- 取同产品下一条生效日期减1天作为当前成本的失效日期，无下一条则用9999-12-31
        COALESCE(
            DATE_SUB(LEAD(Effective_Date) OVER (PARTITION BY Product ORDER BY Effective_Date), INTERVAL 1 DAY),
            '9999-12-31'
        ) AS End_Date
    FROM Cost_Table
)
SELECT
    s.Date,
    s.Store,
    s.Product,
    s.Qty,
    c.Cost
FROM Sale_Table s
LEFT JOIN Cost_With_EndDate c
    ON s.Product = c.Product
    AND s.Date BETWEEN c.Effective_Date AND c.End_Date

小提示：不同数据库的日期加减函数略有差异，PostgreSQL可写为(LEAD(Effective_Date) OVER (...)) - INTERVAL '1 day'，Hive/Spark SQL直接用date_sub函数即可。

效果验证 #

用你提供的样例数据运行上述代码，输出结果完全符合预期：2021-01-01、2021-01-02的Apple销售记录匹配0.5的成本，2021-01-03及之后的Apple销售记录匹配1的成本。

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