合并Sales与Price_History表生成含Price_at_Date的Revenue视图SQL实现

通用版本（支持窗口函数的主流数据库：MySQL8.0+/PostgreSQL/SQL Server/Oracle） #

CREATE VIEW Revenue AS
SELECT
    s.Product_Name,
    s.Qty AS Sale_Qty,
    s.Sales_Date,
    ph.Price AS Price_at_Date
FROM (
    SELECT
        s.Product_Name,
        s.Qty,
        s.Sales_Date,
        ph.Price,
        -- 同笔销售记录的所有匹配价格按变更日期倒序排名
        ROW_NUMBER() OVER (
            PARTITION BY s.Product_Name, s.Sales_Date, s.Qty
            ORDER BY ph.Price_Change_Date DESC
        ) AS rank_num
    FROM Sales s
    -- 匹配同产品、销售日期前的所有价格记录
    LEFT JOIN Price_History ph
        ON s.Product_Name = ph.Product_Name
        AND ph.Price_Change_Date <= s.Sales_Date
) AS temp
-- 取最近一次的价格记录
WHERE temp.rank_num = 1;

低版本MySQL兼容版（无窗口函数支持场景） #

CREATE VIEW Revenue AS
SELECT
    s.Product_Name,
    s.Qty AS Sale_Qty,
    s.Sales_Date,
    (
        SELECT ph.Price
        FROM Price_History ph
        WHERE ph.Product_Name = s.Product_Name
          AND ph.Price_Change_Date <= s.Sales_Date
        ORDER BY ph.Price_Change_Date DESC
        LIMIT 1
    ) AS Price_at_Date
FROM Sales s;

注意：若某笔销售的日期早于对应产品的所有价格变更记录，Price_at_Date会返回NULL，可根据业务需要添加COALESCE函数设置默认兜底价格，或添加过滤条件剔除这类无效数据。

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