根据条件筛选外连接中的记录

Question

我有两个简单的表如下:-

Student
---------------------------------------------
student_id    student_name    student_class
107           paul            A Level-I
108           susan           Diploma
109           jack            O Level-II 
---------------------------------------------
Student_Positions
--------------------------------------------------
position_id      student_id    position    date
1               107           1          1-1-2020
2               107           1          1-1-2021
3               109           2          1-1-2021
4               109           1          1-6-2019

我想在这些表上对每个学生的最新位置进行左外连接，如下所示:-

student_id    student_name     position       date
107              paul          1          1-1-2021
108             susan        
109              jack          2          1-1-2021

我已经多次尝试max(日期)和group by的不同位置，但徒劳无功。请帮忙正确查询

Answer 1

规范SQL解决方案使用一个窗口函数，如row_number():

select s.*, sp.position, sp.date
from students s left join
(select sp.*,
row_number() over (partition by student_id order by date desc) as seqnum
from student_positions sp
) sp
on sp.student_id = s.student_id and sp.seqnum = 1;