如何在按empname分组后仅显示最高的TotalSales值？

你的现有SQL已经能按员工姓名分组计算总销售额并降序排序，但如果只想保留总销售额最高的那一条（或多条并列最高）记录，可以用以下几种方法：

方法1：使用LIMIT（适用于MySQL、PostgreSQL等） #

直接在现有SQL末尾加上LIMIT 1，只取排序后的第一条数据：

select e.empname as "EmployeeName", sum(p.price) as "TotalSales" 
from employees e
join orders o on e.employee_id = o.empid
join products p on o.orderID = p.orderID
group by e.empname
order by TotalSales desc
LIMIT 1;

方法2：使用窗口函数（适用于SQL Server、MySQL 8.0+、PostgreSQL等） #

如果存在多个员工总销售额并列最高的情况，LIMIT 1只会取其中一个，用窗口函数可以灵活保留所有并列最高的记录：

WITH EmployeeSales AS (
    select e.empname as "EmployeeName", sum(p.price) as "TotalSales",
           -- 若要保留所有并列第一，把ROW_NUMBER()换成RANK()即可
           ROW_NUMBER() OVER(ORDER BY sum(p.price) DESC) as rn
    from employees e
    join orders o on e.employee_id = o.empid
    join products p on o.orderID = p.orderID
    group by e.empname
)
SELECT EmployeeName, TotalSales
FROM EmployeeSales
WHERE rn = 1;

方法3：使用子查询匹配最高销售额 #

先算出所有员工中的最高总销售额，再筛选出等于该值的记录：

select e.empname as "EmployeeName", sum(p.price) as "TotalSales" 
from employees e
join orders o on e.employee_id = o.empid
join products p on o.orderID = p.orderID
group by e.empname
HAVING sum(p.price) = (
    SELECT MAX(TotalSales)
    FROM (
        select sum(p.price) as TotalSales
        from employees e
        join orders o on e.employee_id = o.empid
        join products p on o.orderID = p.orderID
        group by e.empname
    ) as SalesSummary
);

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