Northwind数据库：如何简化每年最高销售额员工的SQL查询语句？

需求：在Northwind数据库中，筛选出每年总销售额最高的有产品销售记录的员工姓名。

你当前的SQL查询已能实现需求，但存在重复计算的问题，以下是更简洁高效的实现方式：

原查询代码 #

WITH TEMP_QUERY AS
    (
        SELECT
            DATEPART(YEAR, OrderDate) AS 'Year'
            ,FirstName + ' ' + LastName AS 'Employee'
            ,SUM(UnitPrice * Quantity) AS 'Total year sale'
        FROM Employees
        INNER JOIN Orders
            ON Employees.EmployeeID = Orders.EmployeeID
        INNER JOIN [Order Details]
            ON Orders.OrderID = [Order Details].OrderID
        GROUP BY FirstName + ' ' + LastName, DATEPART(YEAR, OrderDate)
    )

    SELECT
        DATEPART(YEAR, OrderDate) AS 'Year'
        ,FirstName + ' ' + LastName AS 'Employee'
        ,SUM(UnitPrice * Quantity) AS 'Total year sale'
    FROM Employees
    INNER JOIN Orders
        ON Employees.EmployeeID = Orders.EmployeeID
    INNER JOIN [Order Details]
        ON Orders.OrderID = [Order Details].OrderID
    GROUP BY
        FirstName + ' ' + LastName
        ,DATEPART(YEAR, OrderDate)
    HAVING SUM(UnitPrice * Quantity) IN
    (
        SELECT MAX(main.[Total year sale]) FROM TEMP_QUERY AS main 
        INNER JOIN TEMP_QUERY AS e ON e.Employee = main.Employee
        GROUP BY main.Year
    )
    ORDER BY 1;

优化方案1：CTE + RANK()窗口函数（推荐） #

利用RANK()窗口函数可以一次性完成销售额计算与年度排名，避免重复聚合查询，代码更简洁易读：

WITH EmployeeYearlySales AS (
    SELECT
        DATEPART(YEAR, o.OrderDate) AS [Year],
        e.FirstName + ' ' + e.LastName AS [Employee],
        SUM(od.UnitPrice * od.Quantity) AS [TotalYearSale],
        -- 按年份分区，按销售额降序排名，并列冠军会保留相同排名
        RANK() OVER (PARTITION BY DATEPART(YEAR, o.OrderDate) ORDER BY SUM(od.UnitPrice * od.Quantity) DESC) AS SalesRank
    FROM Employees e
    JOIN Orders o ON e.EmployeeID = o.EmployeeID
    JOIN [Order Details] od ON o.OrderID = od.OrderID
    GROUP BY e.FirstName, e.LastName, DATEPART(YEAR, o.OrderDate)
)
SELECT [Year], [Employee], [TotalYearSale]
FROM EmployeeYearlySales
WHERE SalesRank = 1
ORDER BY [Year];

说明 #

• PARTITION BY DATEPART(YEAR, o.OrderDate)：按年份分组统计排名
• RANK()：如果多名员工年度销售额并列第一，会同时返回所有并列记录；若需强制仅返回一条（比如取任意一名），可替换为ROW_NUMBER()

优化方案2：无CTE版本（子查询实现） #

如果不想使用CTE，也可以直接将聚合与排名逻辑放在子查询中：

SELECT [Year], [Employee], [TotalYearSale]
FROM (
    SELECT
        DATEPART(YEAR, o.OrderDate) AS [Year],
        e.FirstName + ' ' + e.LastName AS [Employee],
        SUM(od.UnitPrice * od.Quantity) AS [TotalYearSale],
        RANK() OVER (PARTITION BY DATEPART(YEAR, o.OrderDate) ORDER BY SUM(od.UnitPrice * od.Quantity) DESC) AS SalesRank
    FROM Employees e
    JOIN Orders o ON e.EmployeeID = o.EmployeeID
    JOIN [Order Details] od ON o.OrderID = od.OrderID
    GROUP BY e.FirstName, e.LastName, DATEPART(YEAR, o.OrderDate)
) AS EmployeeYearlySales
WHERE SalesRank = 1
ORDER BY [Year];

优化优势 #

• 仅需执行一次聚合查询，性能优于原查询的重复计算逻辑
• 逻辑清晰，代码行数更少，便于维护和理解

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