现在有这么一个学生单科流水表如下：
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 )
说明一下：stuid是学生学号，sbjid是科目编号，cdate是考试日期时间，score是分数。
可以这样给它充值：
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
在我这边充值后的数据如下：
SQL> select * from tb_scoreflow order by stuid,sbjid,cdate; ID STUID SBJID SCORE CDATE ---------- ---------- ---------- ---------- -------------- 48 0 0 69 23-1月 -20 1 0 3 27 16-2月 -20 39 0 3 26 14-3月 -20 2 0 4 26 11-2月 -20 15 1 0 14 06-3月 -20 3 1 1 43 17-2月 -20 26 1 2 71 23-2月 -20 12 1 3 65 02-3月 -20 32 1 5 15 11-3月 -20 22 2 2 32 02-1月 -20 36 2 2 57 06-1月 -20 ID STUID SBJID SCORE CDATE ---------- ---------- ---------- ---------- -------------- 28 2 4 91 28-2月 -20 16 3 2 95 26-3月 -20 7 3 4 15 07-1月 -20 13 4 0 5 26-2月 -20 41 4 2 73 12-3月 -20 49 4 2 14 05-4月 -20 47 4 3 87 18-3月 -20 19 4 5 30 06-1月 -20 11 5 1 95 07-1月 -20 37 5 1 15 14-3月 -20 5 5 2 13 21-1月 -20 ID STUID SBJID SCORE CDATE ---------- ---------- ---------- ---------- -------------- 44 6 4 2 09-3月 -20 45 7 2 83 21-1月 -20 20 7 3 88 23-3月 -20 21 7 5 68 09-4月 -20 24 8 4 91 31-1月 -20 38 8 4 82 09-3月 -20 43 9 2 28 26-3月 -20 14 10 0 14 14-3月 -20 50 10 0 15 16-3月 -20 4 10 0 24 20-3月 -20 31 10 2 19 01-4月 -20 ID STUID SBJID SCORE CDATE ---------- ---------- ---------- ---------- -------------- 18 10 4 75 12-3月 -20 17 11 3 42 27-1月 -20 33 12 2 15 27-3月 -20 35 12 4 74 06-2月 -20 25 13 2 9 04-1月 -20 34 13 2 19 04-4月 -20 40 13 3 48 26-1月 -20 8 13 4 25 01-1月 -20 27 13 4 85 23-2月 -20 42 14 3 50 18-2月 -20 9 14 4 86 09-3月 -20 ID STUID SBJID SCORE CDATE ---------- ---------- ---------- ---------- -------------- 10 14 4 65 25-3月 -20 23 15 2 35 04-1月 -20 6 15 2 15 01-2月 -20 30 15 2 2 06-4月 -20 46 15 3 44 26-3月 -20 29 15 4 6 07-1月 -20 已选择50行。
要得到单个学生(stuid指定)某一科(sbjid指定)的最新成绩，有下面三种SQL语句可以做到：
左连接方案,此方案将自身左连过来，利用b表中找不到大于a表条件确定学号和科目id相同时时间最近的记录，思路较奇特，不容易在第一时间想到：
SELECT a.* from tb_scoreflow a left JOIN tb_scoreflow b on a.stuid = b.stuid and a.sbjid = b.sbjid and b.cdate > a.cdate where b.cdate IS NULL order by a.stuid,a.sbjid
SQL> SELECT 2 a.* 3 from 4 tb_scoreflow a 5 left JOIN tb_scoreflow b on 6 a.stuid = b.stuid 7 and a.sbjid = b.sbjid 8 and b.cdate > a.cdate 9 where b.cdate IS NULL 10 order by a.stuid,a.sbjid; ID STUID SBJID SCORE CDATE ---------- ---------- ---------- ---------- -------------- 48 0 0 69 23-1月 -20 39 0 3 26 14-3月 -20 2 0 4 26 11-2月 -20 15 1 0 14 06-3月 -20 3 1 1 43 17-2月 -20 26 1 2 71 23-2月 -20 12 1 3 65 02-3月 -20 32 1 5 15 11-3月 -20 36 2 2 57 06-1月 -20 28 2 4 91 28-2月 -20 16 3 2 95 26-3月 -20 ID STUID SBJID SCORE CDATE ---------- ---------- ---------- ---------- -------------- 7 3 4 15 07-1月 -20 13 4 0 5 26-2月 -20 49 4 2 14 05-4月 -20 47 4 3 87 18-3月 -20 19 4 5 30 06-1月 -20 37 5 1 15 14-3月 -20 5 5 2 13 21-1月 -20 44 6 4 2 09-3月 -20 45 7 2 83 21-1月 -20 20 7 3 88 23-3月 -20 21 7 5 68 09-4月 -20 ID STUID SBJID SCORE CDATE ---------- ---------- ---------- ---------- -------------- 38 8 4 82 09-3月 -20 43 9 2 28 26-3月 -20 4 10 0 24 20-3月 -20 31 10 2 19 01-4月 -20 18 10 4 75 12-3月 -20 17 11 3 42 27-1月 -20 33 12 2 15 27-3月 -20 35 12 4 74 06-2月 -20 34 13 2 19 04-4月 -20 40 13 3 48 26-1月 -20 27 13 4 85 23-2月 -20 ID STUID SBJID SCORE CDATE ---------- ---------- ---------- ---------- -------------- 42 14 3 50 18-2月 -20 10 14 4 65 25-3月 -20 30 15 2 2 06-4月 -20 46 15 3 44 26-3月 -20 29 15 4 6 07-1月 -20 已选择38行。
groupby内连接方案，此法方案先进行中规中矩的按学号和科目id分组，得到最近日期，然后再与a表内联，这是比较容易想到的方案：
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
数据：
SQL> select 2 a.* 3 from 4 tb_scoreflow a , 5 (select stuid,sbjid,max(cdate) as cdate from tb_scoreflow 6 group by stuid,sbjid) b 7 where 8 a.stuid=b.stuid and 9 a.sbjid=b.sbjid and 10 a.cdate=b.cdate 11 order by a.stuid,a.sbjid; ID STUID SBJID SCORE CDATE ---------- ---------- ---------- ---------- -------------- 48 0 0 69 23-1月 -20 39 0 3 26 14-3月 -20 2 0 4 26 11-2月 -20 15 1 0 14 06-3月 -20 3 1 1 43 17-2月 -20 26 1 2 71 23-2月 -20 12 1 3 65 02-3月 -20 32 1 5 15 11-3月 -20 36 2 2 57 06-1月 -20 28 2 4 91 28-2月 -20 16 3 2 95 26-3月 -20 ID STUID SBJID SCORE CDATE ---------- ---------- ---------- ---------- -------------- 7 3 4 15 07-1月 -20 13 4 0 5 26-2月 -20 49 4 2 14 05-4月 -20 47 4 3 87 18-3月 -20 19 4 5 30 06-1月 -20 37 5 1 15 14-3月 -20 5 5 2 13 21-1月 -20 44 6 4 2 09-3月 -20 45 7 2 83 21-1月 -20 20 7 3 88 23-3月 -20 21 7 5 68 09-4月 -20 ID STUID SBJID SCORE CDATE ---------- ---------- ---------- ---------- -------------- 38 8 4 82 09-3月 -20 43 9 2 28 26-3月 -20 4 10 0 24 20-3月 -20 31 10 2 19 01-4月 -20 18 10 4 75 12-3月 -20 17 11 3 42 27-1月 -20 33 12 2 15 27-3月 -20 35 12 4 74 06-2月 -20 34 13 2 19 04-4月 -20 40 13 3 48 26-1月 -20 27 13 4 85 23-2月 -20 ID STUID SBJID SCORE CDATE ---------- ---------- ---------- ---------- -------------- 42 14 3 50 18-2月 -20 10 14 4 65 25-3月 -20 30 15 2 2 06-4月 -20 46 15 3 44 26-3月 -20 29 15 4 6 07-1月 -20 已选择38行。
not exist方案，此方案利用了反连接，在找不到b表中学号和科目id相等，而时间大于a表时间时，确定a表中时间最近的记录，这也是比较容易从字面理解的方案：
select a.* from tb_scoreflow a where not exists( select null from tb_scoreflow b where b.stuid=a.stuid and b.sbjid=a.sbjid and b.cdate>a.cdate) order by a.stuid,a.sbjid
数据：
SQL> select 2 a.* 3 from tb_scoreflow a 4 where not exists( select null 5 from tb_scoreflow b 6 where b.stuid=a.stuid and 7 b.sbjid=a.sbjid and 8 b.cdate>a.cdate) 9 order by a.stuid,a.sbjid; ID STUID SBJID SCORE CDATE ---------- ---------- ---------- ---------- -------------- 48 0 0 69 23-1月 -20 39 0 3 26 14-3月 -20 2 0 4 26 11-2月 -20 15 1 0 14 06-3月 -20 3 1 1 43 17-2月 -20 26 1 2 71 23-2月 -20 12 1 3 65 02-3月 -20 32 1 5 15 11-3月 -20 36 2 2 57 06-1月 -20 28 2 4 91 28-2月 -20 16 3 2 95 26-3月 -20 ID STUID SBJID SCORE CDATE ---------- ---------- ---------- ---------- -------------- 7 3 4 15 07-1月 -20 13 4 0 5 26-2月 -20 49 4 2 14 05-4月 -20 47 4 3 87 18-3月 -20 19 4 5 30 06-1月 -20 37 5 1 15 14-3月 -20 5 5 2 13 21-1月 -20 44 6 4 2 09-3月 -20 45 7 2 83 21-1月 -20 20 7 3 88 23-3月 -20 21 7 5 68 09-4月 -20 ID STUID SBJID SCORE CDATE ---------- ---------- ---------- ---------- -------------- 38 8 4 82 09-3月 -20 43 9 2 28 26-3月 -20 4 10 0 24 20-3月 -20 31 10 2 19 01-4月 -20 18 10 4 75 12-3月 -20 17 11 3 42 27-1月 -20 33 12 2 15 27-3月 -20 35 12 4 74 06-2月 -20 34 13 2 19 04-4月 -20 40 13 3 48 26-1月 -20 27 13 4 85 23-2月 -20 ID STUID SBJID SCORE CDATE ---------- ---------- ---------- ---------- -------------- 42 14 3 50 18-2月 -20 10 14 4 65 25-3月 -20 30 15 2 2 06-4月 -20 46 15 3 44 26-3月 -20 29 15 4 6 07-1月 -20 已选择38行。
大家不妨想一下，都能达到目的，哪一种效率最高呢？
--2020年2月18日--
利用rank函数还能提供一种可行性方案，详情请见：https://www.cnblogs.com/xiandedanteng/p/12467058.html
--2020-03-12--