如何基于AdventureWorks数据库查询各客户Top3 TotalDue销售额？

要获取每个客户的Top3最高TotalDue金额，核心是利用SQL窗口函数按客户分组并对销售额排序，再筛选排名前3的记录。以下是两种适配不同业务场景的实现方案：

方案1：严格取Top3（并列金额仅保留一条） #

使用ROW_NUMBER()函数，每个客户内按TotalDue降序生成唯一排名，确保每个客户最多返回3条记录：

WITH SalesRanked AS (
    SELECT 
        productsubcategory.ProductCategoryID, 
        product.ProductID,
        salesorderdetail.SalesOrderID,
        SalesOrderHeader.CustomerID,
        SalesOrderHeader.TotalDue,
        -- 按客户分组，销售额降序生成唯一排名
        ROW_NUMBER() OVER (PARTITION BY SalesOrderHeader.CustomerID ORDER BY SalesOrderHeader.TotalDue DESC) AS SalesRank
    FROM product
    INNER JOIN productsubcategory ON product.ProductSubcategoryID = productsubcategory.ProductSubcategoryID
    INNER JOIN salesorderdetail ON salesorderdetail.ProductID = product.ProductID
    INNER JOIN SalesOrderHeader ON SalesOrderHeader.SalesOrderID = salesorderdetail.SalesOrderID
)
SELECT 
    ProductCategoryID,
    ProductID,
    SalesOrderID,
    CustomerID,
    TotalDue
FROM SalesRanked
WHERE SalesRank <= 3;

方案2：保留并列的Top3记录 #

如果需要保留客户名下金额相同的并列记录（比如某客户有3条以上相同最高销售额，全部保留），使用RANK()函数，它会给相同金额分配相同排名，后续排名自动跳过：

WITH SalesRanked AS (
    SELECT 
        productsubcategory.ProductCategoryID, 
        product.ProductID,
        salesorderdetail.SalesOrderID,
        SalesOrderHeader.CustomerID,
        SalesOrderHeader.TotalDue,
        -- 按客户分组，销售额降序生成排名，并列值共享同一名次
        RANK() OVER (PARTITION BY SalesOrderHeader.CustomerID ORDER BY SalesOrderHeader.TotalDue DESC) AS SalesRank
    FROM product
    INNER JOIN productsubcategory ON product.ProductSubcategoryID = productsubcategory.ProductSubcategoryID
    INNER JOIN salesorderdetail ON salesorderdetail.ProductID = product.ProductID
    INNER JOIN SalesOrderHeader ON SalesOrderHeader.SalesOrderID = salesorderdetail.SalesOrderID
)
SELECT 
    ProductCategoryID,
    ProductID,
    SalesOrderID,
    CustomerID,
    TotalDue
FROM SalesRanked
WHERE SalesRank <= 3;

关键逻辑说明 #

• PARTITION BY CustomerID：指定按客户ID分组，每个客户单独计算销售额排名
• ORDER BY TotalDue DESC：在每个客户组内，按销售额从高到低排序生成排名

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