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上看，我们是去重成功的。