求学生单科流水表中单科最近/最新的考试成绩表的新增可行性方案 使用Oracle提供的分析函数rank
在 https://www.cnblogs.com/xiandedanteng/p/12327809.html 一文中,提到了三种求学生单科最新成绩的SQL可行性方案,这里还有另一种实现,那就是利用分析函数rank。
以下SQL语句就能让记录以学生id,科目id分组,然后按照创建时间逆序排名:
select tb_scoreflow.*,rank() over(partition by stuid,sbjid order by cdate desc) as seq from tb_scoreflow
seq字段就存储了排名,我们只需找出seq为1的记录就行了。
select * from (select tb_scoreflow.*,rank() over(partition by stuid,sbjid order by cdate desc) as seq from tb_scoreflow) a where a.seq=1
和左连,分组,反联三种方案取得的结果集一样,都是38条。
然后和分组方案比较一下结果集,发现是一样的。
实验到此结束,求学生单科最新成绩的可行性方案又增加一种。
实验用到的全部sql如下:
create table tb_scoreflow( id number(6,0) primary key, stuid number(6,0) not null, sbjid number(6,0) not null, score number(3,0) not null, cdate date ) insert into tb_scoreflow select rownum, dbms_random.value(0,15), dbms_random.value(0,5), dbms_random.value(0,100), to_date('2020-01-01','yyyy-MM-dd')+dbms_random.value(0,100) from dual connect by level<=50 order by dbms_random.random commit; select tb_scoreflow.*,rank() over(partition by stuid,sbjid order by cdate desc) as seq from tb_scoreflow select * from (select tb_scoreflow.*,rank() over(partition by stuid,sbjid order by cdate desc) as seq from tb_scoreflow) a where a.seq=1 select t1.*,t2.* from (select * from (select tb_scoreflow.*,rank() over(partition by stuid,sbjid order by cdate desc) as seq from tb_scoreflow) a where a.seq=1) t1 full join ( select a.* from tb_scoreflow a , (select stuid,sbjid,max(cdate) as cdate from tb_scoreflow group by stuid,sbjid) b where a.stuid=b.stuid and a.sbjid=b.sbjid and a.cdate=b.cdate order by a.stuid,a.sbjid) t2 on (t1.id=t2.id) where (t1.id IS NULL or t2.id IS NULL)
相关文章:
求学生单科流水表中单科最近/最新的考试成绩表的三种方案(可行性篇)
求学生单科流水表中单科最近/最新的考试成绩表的三种方案(结果集鉴别篇)
--2020-03-12--