如何在多表关联查询中获取每个item分组的最后一行数据

要实现按Item分组，取出每组中itemStoreUnitID最大的完整行数据，推荐使用窗口函数ROW_NUMBER()，这是最直接且高效的方案：

WITH ranked_items AS (
    SELECT 
        isu.*,
        su.*,
        i.*,
        p.*,
        ROW_NUMBER() OVER (PARTITION BY i.itemID ORDER BY isu.itemStoreUnitID DESC) AS rn
    FROM itemStoreUnits isu
    LEFT JOIN storeUnits su ON isu.itemStoreUnitStoreUnitID = su.storeUnitID  
    LEFT JOIN items i ON isu.itemStoreUnitItemID = i.itemID
    LEFT JOIN products p ON i.productID = p.productID
)
SELECT * 
FROM ranked_items
WHERE rn = 1;

方案说明： #

1. PARTITION BY i.itemID：按itemID分组，确保每个Item单独计算排序逻辑
2. ORDER BY isu.itemStoreUnitID DESC：在每个分组内，按itemStoreUnitID降序排列，最大的ID会排在分组内第1位
3. ROW_NUMBER()：给每个分组内的行分配序号，最大ID对应的行序号为1，最后筛选出序号为1的行即可

兼容老版本数据库的方案（不支持窗口函数时） #

如果你的数据库版本较低（比如MySQL 5.x），可以用关联子查询筛选每个Item对应的最大itemStoreUnitID，再关联获取完整数据：

SELECT isu.*, su.*, i.*, p.*
FROM itemStoreUnits isu
LEFT JOIN storeUnits su ON isu.itemStoreUnitStoreUnitID = su.storeUnitID  
LEFT JOIN items i ON isu.itemStoreUnitItemID = i.itemID
LEFT JOIN products p ON i.productID = p.productID
WHERE isu.itemStoreUnitID = (
    SELECT MAX(itemStoreUnitID)
    FROM itemStoreUnits
    WHERE itemStoreUnitItemID = i.itemID
);

为什么你之前的方案无效： #

• 直接GROUP BY的写法：在严格SQL模式下会报错，即使允许，返回的是分组内的任意行（并非最大ID对应的行），GROUP BY后的DESC仅排序分组结果，不影响分组内的行选择
• 全局MAX子查询：只筛选出了整个表中最大的itemStoreUnitID，不是每个Item分组的最大值
• 附加MAX(isu.itemStoreUnitID)列：只是在分组行后附加了该组的最大值，但该行本身并不是最大ID对应的行

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