SQL MAX聚合函数无法返回教师最新采购日期及关联数据的问题求助

你遇到的核心问题是：MAX() OVER (PARTITION by t.ID)只是为每一行附加了对应教师的最新采购日期，但并没有过滤掉那些非最新日期的行。要只保留每位教师最新采购的那条记录，这里提供两种可靠的实现方案：

方案1：子查询获取教师最新采购日期后关联原表 #

这种方法先通过子查询计算出每个教师符合筛选条件的最大采购日期，再将结果与原表关联，筛选出匹配的记录：

SELECT 
    o.Ordertype,
    o.Status,
    t.Teachername,
    o.PurchaseDate AS last_purchase,
    s.schoolname
FROM Orders o
INNER JOIN Teachers t ON t.TeacherID = o.TeacherID
INNER JOIN Schools s ON s.ID = o.SchoolID
INNER JOIN (
    -- 子查询：获取每个教师符合条件的最新采购日期
    SELECT 
        TeacherID,
        MAX(PurchaseDate) AS LatestPurchaseDate
    FROM Orders
    WHERE Status IN ('Completed','In-progress') 
      AND Ordertype NOT LIKE 'Notebooks'
    GROUP BY TeacherID
) latest ON o.TeacherID = latest.TeacherID 
        AND o.PurchaseDate = latest.LatestPurchaseDate
WHERE o.Status IN ('Completed','In-progress') 
  AND o.Ordertype NOT LIKE 'Notebooks';

方案2：使用CTE和ROW_NUMBER()窗口函数排名 #

通过窗口函数给每个教师的订单按采购日期倒序排名，然后只保留排名为1的记录（即最新的那条）：

WITH RankedOrders AS (
    SELECT 
        o.Ordertype,
        o.Status,
        t.Teachername,
        o.PurchaseDate,
        s.schoolname,
        -- 按教师分区，采购日期倒序排名，最新的记录排名为1
        ROW_NUMBER() OVER (PARTITION BY o.TeacherID ORDER BY o.PurchaseDate DESC) AS OrderRank
    FROM Orders o
    INNER JOIN Teachers t ON t.TeacherID = o.TeacherID
    INNER JOIN Schools s ON s.ID = o.SchoolID
    WHERE o.Status IN ('Completed','In-progress') 
      AND o.Ordertype NOT LIKE 'Notebooks'
)
SELECT 
    Ordertype,
    Status,
    Teachername,
    PurchaseDate AS last_purchase,
    schoolname
FROM RankedOrders
WHERE OrderRank = 1;

说明 #

两种方案都能得到你期望的结果：

• 对于Mary Smith（TeacherID=1），排除了Notebooks类型后，最新的采购是Paper（3/5/2021）
• 对于Jason Crane（TeacherID=2），唯一符合条件的采购是Erasers（2/1/2021）

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