分组外联接的使用

　　今天在itpub上卡到一个帖子，大致要解决的问题如下：假设有一个公司的考勤记录，数据如下：

　　　　张三，20140619；

　　　　张三，20140620；

　　　　张三，20140621；

　　　　李四，20140619；

　　　　李四，20140621；

　　　　王五，20140619；

　　　　王五，20140620；

假设有需求，需查询出某位员工在某段时间内未考勤的日期，如何写这个sql语句呢？

　　模拟出实验数据：

drop table t02;
create table t02 (names varchar2(20),rq varchar2(20));

select * from t02;

insert into t02(names,rq) 
select 'A','1' from dual
union all 
select 'A','2' from dual
union all 
select 'A','3' from dual
union all 
select 'B','1' from dual
union all 
select 'B','3' from dual
union all 
select 'C','1' from dual
union all 
select 'C','2' from dual

从上面数据可以看到，缺失的数据为names=‘B’ rq=‘2’和names=‘C’ rq=‘3’的数据；

开始我的思路是构造一个日期维度表（范围介于并包括需求的最大和最小日期），然后用t02右外连接这张表，rq is null的结果即为我所需，但事实却不是这样。

例如：

with t03 as
(select level rq
  from dual 
connect by level <=3
)
select * from t02 right join t03 on t02.rq=t03.rq where t02.rq is null;

因为每个员工都存在多个考勤信息，缺失的考勤日期会不一样，所以整表右外连接查询不到预想的t02.rq为空的记录，自然就找不到我们的需求数据。后来在坛子里看到一种方法，即分组外连接，也可以叫分区外连接，具体语法如下：

with t03 as
(select level rq
  from dual 
connect by level <=3
)
select * from t02
partition by (t02.names)
right join t03 
on t02.rq=t03.rq
where t02.rq is null;

使用此语法可以实现将t02表先分组与t03进行连接后，再合并结果得到所需结果。实验证明也确实如此，顺利得到所需结果。

　　

 

转载于:https://www.cnblogs.com/assassinann/p/partition_outer_join.html