如何基于ReportDraft表创建含畅销品、总销量及顶级购买客户的统计视图？

当然可以！要生成你需要的包含最畅销客户信息的统计表，我们需要在原有的分组求和基础上，额外找出每个商品对应的购买量最高的客户及其购买数。这里提供两种常见的实现思路，适配不同的SQL方言（比如SQL Server、MySQL等）：

方法1：使用窗口函数（推荐，适用于支持窗口函数的数据库） #

窗口函数可以轻松帮我们在分组内排序，找出每个商品的Top1客户：

WITH ItemSales AS (
    -- 先统计每个商品每个客户的购买量
    SELECT 
        [Purchased Item] AS ItemName,
        [Customer Name] AS CustomerName, -- 替换为你表中实际的客户名字段
        SUM([Overall Quantity purchased]) AS CustomerQty
    FROM ReportDraft
    GROUP BY [Purchased Item], [Customer Name]
),
RankedSales AS (
    -- 给每个商品的客户购买量排名
    SELECT 
        ItemName,
        CustomerName,
        CustomerQty,
        RANK() OVER (PARTITION BY ItemName ORDER BY CustomerQty DESC) AS SalesRank
    FROM ItemSales
)
-- 最后聚合总销量+取每个商品的Top1客户
SELECT 
    rs.ItemName,
    SUM(is2.CustomerQty) AS [Total quantity purchased],
    rs.CustomerName AS [Customer who purchased most],
    rs.CustomerQty AS [Customer quantity bought]
FROM RankedSales rs
JOIN ItemSales is2 ON rs.ItemName = is2.ItemName
WHERE rs.SalesRank = 1
GROUP BY rs.ItemName, rs.CustomerName, rs.CustomerQty
ORDER BY [Total quantity purchased] DESC;

说明： #

• 第一个CTE ItemSales 先按商品和客户分组，算出每个客户买该商品的总量；
• 第二个CTE RankedSales 用RANK()窗口函数给每个商品下的客户按购买量降序排名；
• 最后关联总销量数据，筛选排名第一的客户，同时聚合出商品的总购买量。

方法2：子查询方式（适用于不支持窗口函数的旧版数据库） #

如果你的数据库不支持窗口函数，可以用子查询来定位每个商品的最大客户购买量：

SELECT 
    rd.[Purchased Item] AS ItemName,
    SUM(rd.[Overall Quantity purchased]) AS [Total quantity purchased],
    (
        SELECT TOP 1 rd2.[Customer Name]
        FROM ReportDraft rd2
        WHERE rd2.[Purchased Item] = rd.[Purchased Item]
        GROUP BY rd2.[Customer Name]
        ORDER BY SUM(rd2.[Overall Quantity purchased]) DESC
    ) AS [Customer who purchased most],
    (
        SELECT TOP 1 SUM(rd2.[Overall Quantity purchased])
        FROM ReportDraft rd2
        WHERE rd2.[Purchased Item] = rd.[Purchased Item]
        GROUP BY rd2.[Customer Name]
        ORDER BY SUM(rd2.[Overall Quantity purchased]) DESC
    ) AS [Customer quantity bought]
FROM ReportDraft rd
GROUP BY rd.[Purchased Item]
ORDER BY [Total quantity purchased] DESC;

说明： #

• 主查询负责计算每个商品的总购买量；
• 两个子查询分别找出对应商品购买量最高的客户名字和对应的购买数；
• 注意：如果有多个客户购买量并列最高，TOP 1只会返回其中一个，若要返回所有并列客户，需要调整逻辑（比如用WHERE子句匹配最大购买量）。

额外提示： #

• 请把代码中的[Customer Name]替换成你表中实际存储客户名称的字段名；
• 如果存在同一商品多个客户购买量相同且都是最高的情况，方法1用RANK()会返回所有并列的行，方法2默认只返回一个，可根据需求调整。

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