聚合数据查询异常：如何获取各书店库存最高书籍的对应标题？

我们有一个包含3家书店的数据库，每家书店对应库存表，书籍库存数量随机。需求是查询展示：每家书店（共3行）、该书店库存最高的书籍数量（通过MAX(INV.UnitsInStock)计算）以及对应书籍的标题。

之前尝试的问题 #

第一次编写的SQL：

SELECT BS.Name, B.Title, MAX(UnitsInStock) AS 'Quantity'
FROM Inventories AS INV
JOIN BookShops AS BS ON BS.Id = INV.ShopId
JOIN Books AS B ON B.Id = INV.BookId
GROUP BY BS.Name

运行后报错：

Column 'Books.Title' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

第二次SQL能得到书店名和最高库存，但缺少书籍标题：

SELECT BS.Name, MAX(UnitsInStock) AS 'Quantity'
FROM Inventories AS INV
JOIN BookShops AS BS ON BS.Id = INV.ShopId
JOIN Books AS B ON B.Id = INV.BookId
GROUP BY BS.Name

可行解决方案 #

方案1：窗口函数（推荐，灵活处理并列情况） #

利用窗口函数给每家书店的库存记录排序，筛选出排名第一的条目。根据是否需要保留并列最高的书籍，选择ROW_NUMBER()或RANK()：

仅返回单本（随机选一本，若有并列）

如果书店存在多本库存并列最高的书籍，这个方法会随机选取其中一本返回：

WITH RankedInventory AS (
    SELECT 
        BS.Name AS ShopName,
        B.Title,
        INV.UnitsInStock AS Quantity,
        -- 按书店分组，库存降序排序，给每条记录编号
        ROW_NUMBER() OVER (PARTITION BY BS.Id ORDER BY INV.UnitsInStock DESC) AS RowNum
    FROM Inventories AS INV
    JOIN BookShops AS BS ON BS.Id = INV.ShopId
    JOIN Books AS B ON B.Id = INV.BookId
)
SELECT ShopName, Title, Quantity
FROM RankedInventory
WHERE RowNum = 1;

返回所有并列最高的书籍

如果需要把书店里所有库存达到最高值的书籍都列出来，改用RANK()：

WITH RankedInventory AS (
    SELECT 
        BS.Name AS ShopName,
        B.Title,
        INV.UnitsInStock AS Quantity,
        -- 并列最高的记录会获得相同排名
        RANK() OVER (PARTITION BY BS.Id ORDER BY INV.UnitsInStock DESC) AS RankNum
    FROM Inventories AS INV
    JOIN BookShops AS BS ON BS.Id = INV.ShopId
    JOIN Books AS B ON B.Id = INV.BookId
)
SELECT ShopName, Title, Quantity
FROM RankedInventory
WHERE RankNum = 1;

方案2：子查询关联匹配最高库存 #

先计算每家书店的最高库存值，再关联回原表找到对应的书籍信息：

SELECT 
    BS.Name AS ShopName,
    B.Title,
    INV.UnitsInStock AS Quantity
FROM Inventories AS INV
JOIN BookShops AS BS ON BS.Id = INV.ShopId
JOIN Books AS B ON B.Id = INV.BookId
-- 子查询算出每家书店的最高库存
JOIN (
    SELECT ShopId, MAX(UnitsInStock) AS MaxStock
    FROM Inventories
    GROUP BY ShopId
) AS MaxInv 
    ON INV.ShopId = MaxInv.ShopId 
    AND INV.UnitsInStock = MaxInv.MaxStock;

这个方法同样会返回所有库存并列最高的书籍，结果和用RANK()的窗口函数一致。

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