查询各商品库存最高的单条记录（含表结构及现有SQL）

嗨，我来帮你搞定这个查询需求！你现在的SQL只是把三个表关联起来，返回了所有商品的库存记录，但我们需要的是每个商品库存最高的单条记录，这里有两种靠谱的解决方案：

这个方法适合支持窗口函数的现代数据库（比如MySQL 8.0+、PostgreSQL、SQL Server等），逻辑清晰且效率不错：

WITH RankedStock AS (
    SELECT 
        I.ItemName,
        L.LocationName,
        S.stock,
        -- 按商品分组，每组内按库存降序编号，最高库存的记录编号为1
        ROW_NUMBER() OVER (PARTITION BY I.ItemId ORDER BY S.stock DESC) AS rn
    FROM Items I
    INNER JOIN Stock S ON I.ItemId = S.ItemId
    INNER JOIN Locations L ON S.LocationId = L.LocationId
)
-- 筛选出每组编号为1的记录，就是每个商品库存最高的那条
SELECT ItemName, LocationName, stock
FROM RankedStock
WHERE rn = 1;

如果你的数据库不支持窗口函数（比如MySQL 5.x），可以用子查询先拿到每个商品的最大库存值，再关联回原表匹配对应的记录：

SELECT 
    I.ItemName,
    L.LocationName,
    S.stock
FROM Items I
INNER JOIN Stock S ON I.ItemId = S.ItemId
INNER JOIN Locations L ON S.LocationId = L.LocationId
-- 子查询获取每个商品的最大库存
INNER JOIN (
    SELECT ItemId, MAX(stock) AS max_stock
    FROM Stock
    GROUP BY ItemId
) AS MaxStock ON S.ItemId = MaxStock.ItemId AND S.stock = MaxStock.max_stock;

小提示： #

如果某个商品有多个仓库的库存相同且都是最大值，第二种方案会返回多条记录。如果需要强制只返回一条，可以在子查询或者外层加额外的排序条件（比如按LocationId排序），或者结合LIMIT（不同数据库语法可能有差异）。

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