17,377
社区成员
发帖
与我相关
我的任务
分享
create table sc(
student_id char(10),
course_id char(5),
sc_time datetime,
score decimal(3)
create view last_sc
as
select student_id, course_id, max(sc_time)
from sc
group by student_id, course_id;
create view last_sc_score
as
select student_id, course_id, grade
from sc left join last_sc
on sc.student_id = last_sc.student_id and sc.course_id=last_sc.student_id
select t.* from sc t where sc_time = (select max(sc_time) from sc where student_id = t.student_id) order by t.student_id
select t.* from sc t where not exists (select 1 from sc where student_id = t.student_id and sc_time > t.sc_time) order by t.student_id
select student_id ,course_id ,sc_time ,score from(
select student_id ,course_id ,sc_time ,score ,
row_number() over(partition by student_id ,course_id order by sc_time desc) rn
) where rn=1