Oracle使用row_number() over (partition order by)和DISTINCT去除重複記錄____Oracle

            最近做的一個模組涉及到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上看，我們是去重成功的。