求助：如何基于生效日期计算总成本？SQL查询修正指导

表结构与数据 #

TableB（别名B） #

结构：(Date, Store, Product, Qty)
数据：

('4/1/20', '1', 'A', '5'),
('4/9/20', '2', 'A', '5'),
('4/1/20', '2', 'A', '4'),
('4/1/20', '3', 'B', '3'),
('4/2/20', '3', 'B', '1'),
('4/2/20', '5', 'C', '2')

TableD（别名D） #

结构：(Product, Effective, Cost)
数据：

('A', '4/1/20', '100'),
('A', '4/8/20', '120'),
('B', '4/1/20', '100'),
('B', '4/8/20', '110'),
('C', '4/1/20', '100')

需求说明 #

计算SUM(B.Qty * D.Cost) AS TotalCost，要求每个TableB的记录匹配对应产品下生效日期（Effective）不晚于记录日期（Date）的最新成本。例如：

• Store 1的Product A在'4/1/20'时，总成本为5*100=500
• Store 2的Product A在'4/9/20'时，总成本为5*120=600

原SQL问题分析 #

你提供的SQL存在两个核心问题：

1. 仅通过Product关联两个表，导致每条TableB记录会匹配所有同产品的TableD记录（包括生效日期晚于记录日期的）
2. 分组逻辑包含D.Effective，会将同一TableB记录拆分为多行，最终SUM结果不符合需求

修正后的SQL方案 #

方案1：使用关联子查询（兼容多数数据库） #

SELECT
    B.Date,
    B.Store,
    B.Product,
    SUM(B.Qty * (
        -- 子查询获取对应产品下，生效日期<=当前记录日期的最新成本
        SELECT D.Cost
        FROM TableD AS D
        WHERE D.Product = B.Product
          AND D.Effective <= B.Date
        ORDER BY D.Effective DESC
        LIMIT 1
    )) AS TotalCost
FROM TableB AS B
GROUP BY
    B.Date,
    B.Store,
    B.Product
ORDER BY B.Date;

方案2：使用窗口函数（支持窗口函数的数据库如MySQL 8+, PostgreSQL等） #

WITH RankedCosts AS (
    SELECT
        Product,
        Effective,
        Cost,
        -- 按产品分组，给生效日期倒序排名，最新生效的成本排第1
        ROW_NUMBER() OVER (PARTITION BY Product ORDER BY Effective DESC) AS rn
    FROM TableD
)
SELECT
    B.Date,
    B.Store,
    B.Product,
    SUM(B.Qty * RC.Cost) AS TotalCost
FROM TableB AS B
INNER JOIN RankedCosts AS RC
    ON B.Product = RC.Product
    AND RC.Effective <= B.Date
WHERE RC.rn = 1
GROUP BY
    B.Date,
    B.Store,
    B.Product
ORDER BY B.Date;

执行结果说明 #

上述SQL会输出符合需求的分组总成本：

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