求学生单科流水表中单科最近/最新的考试成绩表的三种方案（结果集鉴别篇）

在上一篇 https://www.cnblogs.com/xiandedanteng/p/12327809.html 里，我们发现有三种SQL语句可以达到目的，而且也把查询结果打印了出来，通过肉眼鉴别发现结果是一致的。

但是，这是结果集有限的情况，当结果集成千上万时，如何知道不会出现一条例外呢？

用程序进行鉴别是一种方法，其实有一句SQL可以帮助我们解决问题，那就是Full join excluding inner join：

上图来自：https://www.cnblogs.com/xiandedanteng/p/12267949.html

 

将这种连接用到实例中就是：

select t1.*,t2.* 
from 
test01 t1 full join 
test02 t2 
on (t1.id=t2.id)
where (t1.id IS NULL or t2.id IS NULL)

把上篇提出的左连方案，group内连方案，not exists方案放入test01，test02里，如果查不出结果，那就证明test01，test02代表的结果集是一致的。

左联和group内连方案比较的sql语句是：

select t1.*,t2.* 
from 
(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) 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)

运行结果是：

SQL> select t1.*,t2.*
  2  from
  3  (SELECT
  4        a.*
  5    from
  6        tb_scoreflow a
  7        left JOIN tb_scoreflow b on
  8        a.stuid = b.stuid
  9        and a.sbjid = b.sbjid
 10        and b.cdate > a.cdate
 11    where b.cdate IS NULL
 12    order by a.stuid,a.sbjid) t1 full join
 13  (  select
 14        a.*
 15    from
 16        tb_scoreflow a ,
 17        (select stuid,sbjid,max(cdate) as cdate from tb_scoreflow
 18               group by stuid,sbjid) b
 19    where
 20        a.stuid=b.stuid and
 21        a.sbjid=b.sbjid and
 22        a.cdate=b.cdate
 23    order by a.stuid,a.sbjid) t2
 24  on (t1.id=t2.id)
 25  where (t1.id IS NULL or t2.id IS NULL);

未选定行

这说明左连和group内连方案的结果集是一致的。

 

group内连和not exists方案进行比较的sql是：

select t1.*,t2.* 
from 
(  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) t1 full join 
(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) t2 
on (t1.id=t2.id)
where (t1.id IS NULL or t2.id IS NULL)

执行结果是：

SQL> select t1.*,t2.*
  2  from
  3  (  select
  4        a.*
  5    from
  6        tb_scoreflow a ,
  7        (select stuid,sbjid,max(cdate) as cdate from tb_scoreflow
  8               group by stuid,sbjid) b
  9    where
 10        a.stuid=b.stuid and
 11        a.sbjid=b.sbjid and
 12        a.cdate=b.cdate
 13    order by a.stuid,a.sbjid) t1 full join
 14  (select
 15      a.*
 16  from tb_scoreflow a
 17  where not exists( select null
 18                    from tb_scoreflow b
 19                    where b.stuid=a.stuid and
 20                          b.sbjid=a.sbjid and
 21                          b.cdate>a.cdate)
 22  order by a.stuid,a.sbjid) t2
 23  on (t1.id=t2.id)
 24  where (t1.id IS NULL or t2.id IS NULL);

未选定行

这也说明group内联方案和not exists方案的结果集是一致的。

 

至此，已经可以证明 左连接==group内连==not exists方案，无论结果集大小，我们都无需担心出现一条例外了。

--2020年2月22日--