求学生单科流水表中单科最近/最新的考试成绩表的三种方案(可行性篇)

现在有这么一个学生单科流水表如下:

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--

posted @ 2020-02-18 20:28  逆火狂飙  阅读(148)  评论(0)    收藏  举报
生当作人杰 死亦为鬼雄 至今思项羽 不肯过江东