基于SQL Server的Northwind数据库查询员工每月最后下单日及当日订单

解决方案 #

你可以通过以下几种方式，基于你已有的查询逻辑关联原订单表，或者直接用窗口函数筛选目标订单：

方法1：CTE+关联查询

先计算每个员工每月的最后下单日期，再关联原订单表获取当日所有订单：

WITH EmployeeMonthlyLastOrder AS (
    SELECT 
        EmployeeID,
        YEAR(OrderDate) AS OrderYear,
        MONTH(OrderDate) AS OrderMonth,
        MAX(CONVERT(date, OrderDate)) AS LastOrderDay
    FROM northwind.dbo.Orders
    GROUP BY EmployeeID, YEAR(OrderDate), MONTH(OrderDate)
)
SELECT o.*
FROM northwind.dbo.Orders o
JOIN EmployeeMonthlyLastOrder emlo 
    ON o.EmployeeID = emlo.EmployeeID
    AND YEAR(o.OrderDate) = emlo.OrderYear
    AND MONTH(o.OrderDate) = emlo.OrderMonth
    AND CONVERT(date, o.OrderDate) = emlo.LastOrderDay
ORDER BY o.EmployeeID, o.OrderDate;

方法2：窗口函数直接筛选

用窗口函数给每条订单标记出所属员工当月的最后下单日，再筛选匹配的记录：

SELECT *
FROM (
    SELECT 
        *,
        MAX(CONVERT(date, OrderDate)) OVER (PARTITION BY EmployeeID, YEAR(OrderDate), MONTH(OrderDate)) AS LastOrderDay
    FROM northwind.dbo.Orders
) AS OrderWithLastDay
WHERE CONVERT(date, OrderDate) = LastOrderDay
ORDER BY EmployeeID, OrderDate;

方法3：子查询关联

对每条订单，通过子查询找到同员工同月份的最后下单日，再筛选匹配的订单：

SELECT o.*
FROM northwind.dbo.Orders o
WHERE CONVERT(date, o.OrderDate) = (
    SELECT MAX(CONVERT(date, OrderDate))
    FROM northwind.dbo.Orders
    WHERE EmployeeID = o.EmployeeID
      AND YEAR(OrderDate) = YEAR(o.OrderDate)
      AND MONTH(OrderDate) = MONTH(o.OrderDate)
)
ORDER BY o.EmployeeID, o.OrderDate;

这三种方法都能实现需求：窗口函数写法最简洁，CTE方式可读性更强，子查询适合简单场景，你可以根据实际情况选择。

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