SQL分组+自连接+视图：如何生成含商品总销量及最高购买客户报表

没问题，我来帮你一步步实现这个需求，结合分组、自连接和视图技术生成你要的报表格式：

1. 先创建基础统计视图 #

我们用两个视图来封装基础的统计逻辑，让后续查询更清晰：

视图1：统计每个商品下各客户的购买量 #

CREATE VIEW ItemCustomerPurchases AS
SELECT 
    [Purchased Item] AS ItemName,
    [Customer Name] AS CustomerName, -- 这里假设你的表有客户名字段，替换成实际列名即可
    SUM([Overall Quantity purchased]) AS CustomerPurchaseQty
FROM ReportDraft
GROUP BY [Purchased Item], [Customer Name];

视图2：统计每个商品的总购买量 #

CREATE VIEW ItemTotalPurchases AS
SELECT 
    [Purchased Item] AS ItemName,
    SUM([Overall Quantity purchased]) AS TotalPurchaseQty
FROM ReportDraft
GROUP BY [Purchased Item];

2. 编写最终查询（结合自连接） #

通过自连接找到每个商品购买量最大的客户，再关联总购买量视图，就能得到你要的完整报表：

SELECT 
    itp.ItemName,
    itp.TotalPurchaseQty AS 总购买数量,
    icp.CustomerName AS 购买最多的客户,
    icp.CustomerPurchaseQty AS 客户购买数量
FROM ItemTotalPurchases itp
-- 关联商品-客户购买量视图
JOIN ItemCustomerPurchases icp ON itp.ItemName = icp.ItemName
-- 自连接子查询找到每个商品的最大客户购买量
JOIN (
    SELECT 
        ItemName,
        MAX(CustomerPurchaseQty) AS MaxCustomerQty
    FROM ItemCustomerPurchases
    GROUP BY ItemName
) max_qty 
    ON icp.ItemName = max_qty.ItemName 
    AND icp.CustomerPurchaseQty = max_qty.MaxCustomerQty
-- 按总购买量降序排序，和你原语句逻辑一致
ORDER BY itp.TotalPurchaseQty DESC;

3. 处理并列最多客户的特殊情况 #

如果存在多个客户对同一个商品的购买量并列第一，上面的SQL会返回多行。你可以根据需求调整：

• 只显示一个客户：把自连接子查询换成窗口函数ROW_NUMBER()，取排名第一的客户
• 显示所有并列客户：用RANK()替代ROW_NUMBER()，保留所有并列项

只显示单个客户的优化写法 #

WITH RankedCustomerPurchases AS (
    SELECT 
        ItemName,
        CustomerName,
        CustomerPurchaseQty,
        ROW_NUMBER() OVER (PARTITION BY ItemName ORDER BY CustomerPurchaseQty DESC) AS PurchaseRank
    FROM ItemCustomerPurchases
)
SELECT 
    itp.ItemName,
    itp.TotalPurchaseQty AS 总购买数量,
    rcp.CustomerName AS 购买最多的客户,
    rcp.CustomerPurchaseQty AS 客户购买数量
FROM ItemTotalPurchases itp
JOIN RankedCustomerPurchases rcp 
    ON itp.ItemName = rcp.ItemName 
    AND rcp.PurchaseRank = 1
ORDER BY itp.TotalPurchaseQty DESC;

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