sql - 加入 Access 2016 中具有最大值的行

Question

尝试在 Access 2016 中选择所有学生，并为每个学生加入一个计划。它工作正常，但这不是选择的最新计划......如果我仅针对特定的 Student_id 运行子查询，则它正在运行并且会选择最新的计划。

SELECT dbo_Student.*, a.*
FROM dbo_Student LEFT JOIN (SELECT TOP 1 * FROM dbo_Plan ORDER BY Year 
DESC, Version DESC)  AS a ON dbo_Student.ID = a.student_id
WHERE Archived = FALSE
ORDER BY Name;

编辑
这是迄今为止我得到的最接近的结果，感谢 C perkins:

SELECT s.*, p.*
FROM dbo_Student s LEFT JOIN
(SELECT p1.* 
 FROM dbo_Plan p1 INNER JOIN 
      (SELECT p2.student_id, MAX(p2.ID) As MaxID
       FROM dbo_Plan p2
       GROUP BY p2.student_id
      ) pAgg
      ON p1.student_id = pAgg.student_id 
         AND p1.ID = pAgg.MaxID
) p
ON s.id = p.student_id
WHERE Archived = FALSE
ORDER BY Name;

这假定最大 ID 是最新的计划(年份 DESC、版本 DESC)。它几乎总是有效，但它并不安全。

Answer 1

鉴于您的原始查询意味着单个Year可能有多个Versions(ORDER BY Year DESC, Version DESC) ;我建议如下:

select dbo_student.*, b.*
from
    dbo_student left join
    (
        select a.* from dbo_plan a inner join
        (
            select q.student_id, q.year, max(q.version) as v
            from dbo_plan q inner join
            (
                select p.student_id, max(p.year) as y
                from dbo_plan p
                group by p.student_id
            ) s on q.student_id = s.student_id and q.year = s.y
            group by q.student_id, q.year
        ) t on
        a.student_id = t.student_id and 
        a.year = t.year and
        a.version = t.v
    ) b on dbo_student.id = b.student_id
where 
    archived = false
order by 
    name

最里面的查询获取每个Student_ID的最大Year，然后父查询获取该Year的最大Version code> 每个 Student_ID，将原始表连接到该表，以便可以输出所有字段。