SQL查询实现按公司分组获取每组总销量最高的第一条数据

实现方案 #

方案1：支持窗口函数的数据库（MySQL 8.0+、PostgreSQL、SQL Server等，推荐） #

核心思路是先预计算每个产品的总销量，再用窗口函数给同公司的产品按销量倒序排名，过滤出排名第一的记录即可，性能远高于子查询方案。

WITH product_total AS (
    -- 预计算全量产品的总销量，避免逐行重复计算
    SELECT productID, SUM(quantity) AS total
    FROM orderdetails
    GROUP BY productID
),
company_product_rank AS (
    SELECT 
        DISTINCT p.productname, 
        c.companyname,
        od.productID,
        pt.total,
        -- 按公司分组，组内按总销量倒序排序，销量最高的行排名为1
        ROW_NUMBER() OVER (PARTITION BY c.companyname ORDER BY pt.total DESC) AS rn
    FROM customers c 
    INNER JOIN orders o ON c.customerid = o.customerid 
    INNER JOIN orderdetails od ON o.orderid = od.orderid 
    INNER JOIN products p ON od.productid = p.productid
    INNER JOIN product_total pt ON p.productid = pt.productid
)
-- 过滤仅保留每个公司销量最高的记录
SELECT productname, companyname, productID, total
FROM company_product_rank
WHERE rn = 1
ORDER BY companyname;

注意：如果同一家公司有多个产品总销量并列最高，需要全部返回的话，把ROW_NUMBER()替换为RANK()即可。

方案2：不支持窗口函数的旧版本数据库（如MySQL 5.7及以下） #

用子查询先算出每个公司的最高销量，再关联匹配对应记录：

SELECT 
    DISTINCT p.productname, 
    c.companyname,
    od.productID,
    (SELECT SUM(quantity) FROM orderdetails WHERE productID = p.ProductID) AS total
FROM customers c 
INNER JOIN orders o ON c.customerid = o.customerid 
INNER JOIN orderdetails od ON o.orderid = od.orderid 
INNER JOIN products p ON od.productid = p.productid
WHERE (
    c.companyname, 
    (SELECT SUM(quantity) FROM orderdetails WHERE productID = p.ProductID)
) IN (
    SELECT 
        c_inner.companyname,
        MAX((SELECT SUM(quantity) FROM orderdetails WHERE productID = p_inner.ProductID)) AS max_total
    FROM customers c_inner 
    INNER JOIN orders o_inner ON c_inner.customerid = o_inner.customerid 
    INNER JOIN orderdetails od_inner ON o_inner.orderid = od_inner.orderid 
    INNER JOIN products p_inner ON od_inner.productid = p_inner.productid
    GROUP BY c_inner.companyname
)
ORDER BY c.companyname;

注意：该方案存在重复计算逻辑，数据量较大时性能较差，优先推荐方案1。

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