如何按非恒定值Group By？获取物品最新价值的SQL查询优化

需求是获取所有物品的最新价值（$），关联三张表：

• Table_1：通过ITEM_ID获取ITEM_Name
• Table_2：记录物品价格随时间的变化
• Table_3：存储物品附加信息（含ITEM_Owner）

预期结果为每个物品对应**最新End_Date的Item_Value($)**及ITEM_Owner，但原查询因Group By子句逻辑错误返回多余记录——把非恒定值Item_Value($)和聚合函数MAX(MMM.End_Date)放入Group By，导致分组混乱，无法正确筛选最新记录。

原查询错误分析 #

原查询代码：

select M.ITEM_Name,MAX(MMM.End_Date), MMM.Item_Value($),MM.ITEM_Owner
FROM Table_3 MM
    INNER JOIN Table_1 M ON M.ITEM_ID = MM.ITEM_ID
    INNER JOIN Table_2 MMM ON MMM.ITEM_ID = MM.ITEM_ID
GROUP BY M.ITEM_Name,MAX(MMM.End_Date), MMM.Item_Value($),MM.ITEM_Owner;

错误点：

• Group By子句中不能包含聚合函数（如MAX(MMM.End_Date)），这不符合SQL分组逻辑
• 将MMM.Item_Value($)加入Group By后，每个不同的价值都会生成独立分组，导致同一物品的多条价格记录被保留，无法只获取最新日期的那条

优化方案一：使用窗口函数（推荐） #

通过ROW_NUMBER()窗口函数为每个物品的价格记录按日期倒序编号，仅保留编号为1的最新记录：

SELECT 
    M.ITEM_Name,
    latest_records.End_Date,
    latest_records.Item_Value($),
    MM.ITEM_Owner
FROM Table_3 MM
INNER JOIN Table_1 M ON M.ITEM_ID = MM.ITEM_ID
INNER JOIN (
    SELECT 
        ITEM_ID,
        End_Date,
        Item_Value($),
        -- 按物品ID分组，按End_Date倒序排列，最新记录编号为1
        ROW_NUMBER() OVER (PARTITION BY ITEM_ID ORDER BY End_Date DESC) AS rn
    FROM Table_2
) AS latest_records ON latest_records.ITEM_ID = MM.ITEM_ID
WHERE latest_records.rn = 1; -- 筛选最新记录

该方案逻辑清晰，无需多次分组，适合大多数支持窗口函数的数据库（如MySQL 8+、PostgreSQL、SQL Server等）。

优化方案二：先获取最新日期再关联 #

先通过子查询得到每个物品的最新End_Date，再关联Table_2筛选对应记录：

SELECT 
    M.ITEM_Name,
    MMM.End_Date,
    MMM.Item_Value($),
    MM.ITEM_Owner
FROM Table_3 MM
INNER JOIN Table_1 M ON M.ITEM_ID = MM.ITEM_ID
INNER JOIN Table_2 MMM ON MMM.ITEM_ID = MM.ITEM_ID
INNER JOIN (
    SELECT ITEM_ID, MAX(End_Date) AS latest_end_date
    FROM Table_2
    GROUP BY ITEM_ID
) AS latest_dates ON latest_dates.ITEM_ID = MMM.ITEM_ID 
    AND latest_dates.latest_end_date = MMM.End_Date;

该方案兼容性更好，适合不支持窗口函数的旧版本数据库。

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