日期:2014-05-17 浏览次数:20793 次
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 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
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
------解决方案--------------------
楼主实际上你已经自己把答案写出来了,只是差了一个知识点,就是在sql中,是可以把一个select结果集当做一个视图来使用的,所以你只要把那个视图的select语句直接放到你的sql中使用就可以了,不需要去创建这个视图。具体估法楼上几位已经给出答案了。