编写SQL查询获取各产品最新价格及生效日期，解决现有查询结果错误问题

你的现有查询逻辑错误出在GROUP BY的分组规则上：你同时按照prodID和price分组，这会把同一个产品下所有价格不同的记录拆分为多个独立分组，每个分组只会返回该价格对应的最大生效日期，根本不会全局匹配该产品最新生效日期对应的价格，所以结果不符合预期。

方案1：窗口函数法（推荐，性能高写法简洁） #

支持SQL Server、MySQL 8.0+、PostgreSQL、Oracle等主流数据库：

SELECT prodID, price, [date]
FROM (
    SELECT 
        p.prodID, 
        pr.price, 
        pr.[from] AS [date],
        -- 按产品分组，生效日期倒序排序，排名第一的就是最新记录
        ROW_NUMBER() OVER (PARTITION BY p.prodID ORDER BY pr.[from] DESC) AS rn
    FROM Price pr
    INNER JOIN Product p ON p.prodId = pr.prodId
) t
WHERE rn = 1;

如果同一个产品同一天有多条价格记录需要全部返回，把ROW_NUMBER()替换为RANK()即可。

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

先查询每个产品的最大生效日期，再关联回价格表取对应价格：

SELECT p.prodID, pr.price, pr.[from] AS [date]
FROM Product p
INNER JOIN Price pr ON p.prodId = pr.prodId
INNER JOIN (
    -- 先取每个产品的最新生效日期
    SELECT prodId, MAX([from]) AS max_from
    FROM Price
    GROUP BY prodId
) t ON pr.prodId = t.prodId AND pr.[from] = t.max_from;

以上两种方案针对你给出的测试数据，都会返回prodID=36、price=12.81、date=2019-12-30的结果，符合预期。

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