基于AdventureWorks 2019的发票最贵商品报表开发请求

针对AdventureWorks 2019示例数据库的需求，我们可以通过窗口函数筛选出每个订单（发票）中单价最高的商品，关联商品表获取名称后输出指定字段。

核心SQL查询 #

SELECT 
    sod.SalesOrderID AS [Invoice number],
    p.Name AS [product title],
    sod.LineTotal AS [product amount]
FROM (
    SELECT 
        SalesOrderID,
        ProductID,
        LineTotal,
        UnitPrice,
        -- 按订单分组，单价降序排序，标记每组内的最高单价商品
        ROW_NUMBER() OVER (PARTITION BY SalesOrderID ORDER BY UnitPrice DESC) AS rn
    FROM Sales.SalesOrderDetail
) sod
JOIN Production.Product p 
    ON sod.ProductID = p.ProductID
WHERE sod.rn = 1;

说明 #

• 子查询中使用ROW_NUMBER()窗口函数，以SalesOrderID（订单/发票号）为分组依据，每组内按UnitPrice（单价）降序排列，序号rn=1的记录即为该订单中单价最高的商品。
• 关联Production.Product表获取商品的名称（product title）。
• LineTotal字段对应商品的总金额（单价×数量），符合需求中的product amount。

扩展：保留同订单内多个最高单价商品 #

如果订单中存在多个商品单价相同且均为最高值，想要保留所有这类记录，可将ROW_NUMBER()替换为RANK()：

SELECT 
    sod.SalesOrderID AS [Invoice number],
    p.Name AS [product title],
    sod.LineTotal AS [product amount]
FROM (
    SELECT 
        SalesOrderID,
        ProductID,
        LineTotal,
        UnitPrice,
        RANK() OVER (PARTITION BY SalesOrderID ORDER BY UnitPrice DESC) AS rn
    FROM Sales.SalesOrderDetail
) sod
JOIN Production.Product p 
    ON sod.ProductID = p.ProductID
WHERE sod.rn = 1;

内容的提问来源于stack exchange，提问作者فاطیما شاه حسینی