Oracle使用row_number() over (partition order by)和DISTINCT去除重复记录

            最近做的一个模块涉及到8张表的联合查询，由于这8张表中有很多主从表的关联，结果在使用模糊查询的时候查询结果集出现了重复记录。如下：                                                                                                   

      

      执行的SQL语句如下：

      

select 
cf.id id, 
o.OBJECTCODE,
o.OBJECTNAME objNAME1,
cf.name name,
o1.OBJECTNAME objNAME2,
o2.OBJECTNAME objNAME3,
cf.no no,
bc.cert_no,
bc.FIELD3_VALUE,
to_char(bc.CREATE_DATE,'yyyy-MM-dd'),
trim(to_char(nvl(cf.FORMAMOUNT,0),'99,999,999,999,999,990.99')),
o3.OBJECTNAME objNAME4,
ap.PERSONNELNAME apName,
t.actorname,
to_char(cf.CREATETIME,'yyyy-MM-dd'),
cf.TEMPLETEID,
cf.orgentityid 
FROM 
t_cc_ct_bc bc,
T_CC_BillDetailData detail,
T_CC_BILLMAINDATA main,
cc_form cf,
t_sys_user ap,
T_CC_OBJECT o,
T_CC_OBJECT o1,
T_CC_OBJECT o2,
T_CC_OBJECT o3,
t_sys_flow_task t where 1=1 
and cf.no=main.BILLNUMBER  
and main.item109=o.OBJECTID  
and main.REQUISITIONUSER=ap.USERID 
and detail.BILLMAINDATAID=main.BILLMAINDATAID  
and main.billMainDataID=bc.BILLMAINDATAID  
and o1.OBJECTID=detail.DIMACCOUNT  
and o2.OBJECTID=detail.COMPUTATIONACCOUNT  
and o3.OBJECTID=main.RequisitionUserDepartment   
and main.billnumber=t.bono   
and t.tasktype='finishTask'   
and t.activityname='共享审核会计'  
and (t.ACTORNAME LIKE '%黄%')    
order by cf.createtime desc; 

    一、使用DISTINCT去重

     

select 
DISTINCT(cf.ID), 
o.OBJECTCODE,
o.OBJECTNAME objNAME1,
cf.name name,
o1.OBJECTNAME objNAME2,
o2.OBJECTNAME objNAME3,
cf.no no,
bc.cert_no,
bc.FIELD3_VALUE,
to_char(bc.CREATE_DATE,'yyyy-MM-dd'),
trim(to_char(nvl(cf.FORMAMOUNT,0),'99,999,999,999,999,990.99')),
o3.OBJECTNAME objNAME4,
ap.PERSONNELNAME apName,
t.actorname,
to_char(cf.CREATETIME,'yyyy-MM-dd') as cfcreatetime,
cf.TEMPLETEID,
cf.orgentityid 
FROM 
t_cc_ct_bc bc,
T_CC_BillDetailData detail,
T_CC_BILLMAINDATA main,
cc_form cf,
t_sys_user ap,
T_CC_OBJECT o,
T_CC_OBJECT o1,
T_CC_OBJECT o2,
T_CC_OBJECT o3,
t_sys_flow_task t  
where 1=1 and cf.no=main.BILLNUMBER  
and main.item109=o.OBJECTID  
and main.REQUISITIONUSER=ap.USERID 
and detail.BILLMAINDATAID=main.BILLMAINDATAID  
and main.billMainDataID=bc.BILLMAINDATAID  
and o1.OBJECTID=detail.DIMACCOUNT  
and o2.OBJECTID=detail.COMPUTATIONACCOUNT  
and o3.OBJECTID=main.RequisitionUserDepartment   
and main.billnumber=t.bono   
and t.tasktype='finishTask'   
and t.activityname='共享审核会计'  
and (t.ACTORNAME LIKE '%刘%') 
order by cfcreatetime desc

     这里就不过多的解释了，在这里我们使用ID去重，只需要将ID字段使用DISTINCT()处理一下就可以了。

     注意，在这之前遇到一个问题：

     

     这是由于DISTINCT函数和ORDER BY冲突导致的，从上面的SQL中可以看出，最后是要根据某一表的日期字段进行排序的，之前我用的是cf.createtime，引发了上面的sql错误，后来将cf.createtime这一列添加了一个别名，就这样，问题完美的解决了，然后再ORDER BY的时候使用别名，这样就完美的解决了。

      to_char(cf.CREATETIME,'yyyy-MM-dd') as cfcreatetime

    二、使用row_number() over (partition order by)去重

      

with ect as(
select 
cf.id id, 
o.OBJECTCODE,
o.OBJECTNAME objNAME1,
cf.name name,
o1.OBJECTNAME objNAME2,
o2.OBJECTNAME objNAME3,
cf.no no,
bc.cert_no,
bc.FIELD3_VALUE,
to_char(bc.CREATE_DATE,'yyyy-MM-dd'),
trim(to_char(nvl(cf.FORMAMOUNT,0),'99,999,999,999,999,990.99')),
o3.OBJECTNAME objNAME4,
ap.PERSONNELNAME apName,
t.actorname,
to_char(cf.CREATETIME,'yyyy-MM-dd') as create1,
cf.TEMPLETEID,
cf.orgentityid 
FROM 
t_cc_ct_bc bc,
T_CC_BillDetailData detail,
T_CC_BILLMAINDATA main,
cc_form cf,
t_sys_user ap,
T_CC_OBJECT o,
T_CC_OBJECT o1,
T_CC_OBJECT o2,
T_CC_OBJECT o3,
t_sys_flow_task t  where 1=1 
and cf.no=main.BILLNUMBER  
and main.item109=o.OBJECTID  
and main.REQUISITIONUSER=ap.USERID 
and detail.BILLMAINDATAID=main.BILLMAINDATAID  
and main.billMainDataID=bc.BILLMAINDATAID  
and o1.OBJECTID=detail.DIMACCOUNT  
and o2.OBJECTID=detail.COMPUTATIONACCOUNT  
and o3.OBJECTID=main.RequisitionUserDepartment   
and main.billnumber=t.bono   
and t.tasktype='finishTask'   
and t.activityname='共享审核会计'  
and (t.ACTORNAME LIKE '%黄%'))
select * from(
	select ss.*,row_number() over (partition by id order by create1) rid from ect ss 
) a where rid=1

    说明：

    1.with ect as() 

    括号内的内容，就是查询的sql结果，其中包含重复数据，于是用with as() 创建一个临时表ect为临时表的名字。

    2.select ss.*,row_number() over (partition by id order by create1) rid from ect ss

    这句sql的意思是，查询临时表ect 别名为ss，row_number() over(partition by 需要检索重复的列 order by 排序的列名)  别名为 rid  form ect ss，这时候就会查询获得一个rid列，如果id列存在多条相同值就以1开始递增。

    3.select * from (↑) a where rid = 1

    这句sql是筛选rid为1，也就是id只出现1次的数据，这时候就去重复了。

    最后执行结果如下；

    

    从ID上看，我们是去重成功的。