SQL Server：获取每位客户的最高采购金额及对应产品

我编写了如下SQL查询语句：

select C.Persona, C.Producto, Sum(C.Cantidad * P.Precio) [Total_Purchase] 
from Compras C 
join Precios P on C.Producto = P.Item 
group by C.Producto, C.Persona

该语句可返回每位客户（Persona）对应各产品的总采购金额。请问如何调整该语句，以获取每位客户的最高采购金额及对应的产品信息？

针对你的需求，这里有两种实用的解决方案，优先推荐窗口函数法，因为它代码更简洁、执行效率也更高：

方法一：使用窗口函数 RANK()（保留并列最高记录） #

窗口函数可以轻松给每个客户的产品采购额单独排序，然后筛选出排名第一的记录。如果某客户有多个产品的总采购金额并列最高，RANK()会把这些记录都保留下来，这很符合实际业务场景。

WITH CustomerProductTotals AS (
    -- 先复用你原来的查询，算出每个客户每个产品的总采购额
    SELECT 
        C.Persona, 
        C.Producto, 
        SUM(C.Cantidad * P.Precio) AS Total_Purchase
    FROM Compras C 
    JOIN Precios P ON C.Producto = P.Item 
    GROUP BY C.Producto, C.Persona
),
RankedTotals AS (
    -- 给每个客户的采购记录按总金额降序排名
    SELECT 
        Persona,
        Producto,
        Total_Purchase,
        RANK() OVER (PARTITION BY Persona ORDER BY Total_Purchase DESC) AS PurchaseRank
    FROM CustomerProductTotals
)
-- 筛选出每个客户排名第一的记录
SELECT Persona, Producto, Total_Purchase
FROM RankedTotals
WHERE PurchaseRank = 1;

如果你的需求是只保留任意一个最高金额的产品（即使有并列），可以把RANK()换成ROW_NUMBER()，它会给每条记录分配唯一的排名。

方法二：子查询关联法（兼容旧版本数据库） #

如果你的数据库不支持窗口函数（比如一些老版本的MySQL、SQL Server），可以用子查询先找出每个客户的最高总金额，再关联回原统计结果，匹配出对应的产品信息：

WITH CustomerMaxTotal AS (
    -- 第一步：算出每个客户的最高采购金额
    SELECT 
        Persona,
        MAX(Total_Purchase) AS Max_Total
    FROM (
        SELECT 
            C.Persona, 
            SUM(C.Cantidad * P.Precio) AS Total_Purchase
        FROM Compras C 
        JOIN Precios P ON C.Producto = P.Item 
        GROUP BY C.Producto, C.Persona
    ) AS CustomerTotals
    GROUP BY Persona
)
-- 第二步：关联回去找到对应产品和金额
SELECT 
    CT.Persona,
    CT.Producto,
    CT.Total_Purchase
FROM (
    SELECT 
        C.Persona, 
        C.Producto, 
        SUM(C.Cantidad * P.Precio) AS Total_Purchase
    FROM Compras C 
    JOIN Precios P ON C.Producto = P.Item 
    GROUP BY C.Producto, C.Persona
) AS CT
JOIN CustomerMaxTotal CM ON CT.Persona = CM.Persona AND CT.Total_Purchase = CM.Max_Total;

这个方法同样会保留并列最高的记录，逻辑和窗口函数一致，只是写法更繁琐一些。

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