oracle数据比对写法,数据比对的sql（oracle数据库）

一个表中uniquecoding字段有重复值，还有一个cmp_state字段，代表状态，要求对uniquecoding字段重复的记录只留一条不做标记，其它的对其cmp_state全都标记为"重复"

-- 去重，自己比自己

update COMP_ASC_TEST_1 set cmp_state='重复' where id in(

select b.id from

(

select id,uniquecoding,rownum num from(

select id,uniquecoding from COMP_ASC_TEST_1 ascs where uniquecoding in

(

select distinct uniquecoding from COMP_ASC_TEST_1  group by uniquecoding having count(uniquecoding)>1-- order by uniquecoding asc

)order by uniquecoding asc--, uniquecoding asc

)

) a join

(

select id,uniquecoding,rownum-1 num from(

select id,uniquecoding from COMP_ASC_TEST_1 ascs where uniquecoding in

(

select distinct uniquecoding from COMP_ASC_TEST_1  group by uniquecoding having count(uniquecoding)>1-- order by uniquecoding asc

)order by uniquecoding asc--, uniquecoding asc

)

) b

on a.num=b.num and A.UNIQUECODING=b.uniquecoding

)

--获取两个表中uniquecoding一致并且条数一样的记录(N:N)，并跟新为“

id不一致1-1

”

update COMP_ASC_TEST_1 asc_test set asc_test.cmp_state='id不一致1-1'

where

asc_test.uniquecoding in

(

select aa.uniquecoding,TOTAL1 from

(

select count(ascs.uniquecoding) as total1,uniquecoding from COMP_ASC_TEST_1 ascs group by uniquecoding order by total1 desc

) aa,

(

select count(bw.uniquecoding) as total2,uniquecoding from comp_tskf_test_1 bw group by uniquecoding order by total2 desc

) bb

where aa.uniquecoding=bb.uniquecoding

and total1=total2 and total1>1

)

--获取两个表中uniquecoding一致并且条数一样的记录(1:1)且asc目bw非的记录，并跟新状态为“

ASC目BW非

”

update COMP_ASC_TEST_1 asc_test set asc_test.cmp_state='ASC目BW非'

where

asc_test.uniquecoding in

(

select ASCS.ID from comptest_asc ASCS,comptest_by BW  where ASCS.WANQUANYIZHI=BW.WANQUANYIZHI and

ASCS.wanquanyizhi in

(

select aa.wanquanyizhi from

(

select count(ascs.wanquanyizhi) as total1,wanquanyizhi from comptest_asc ascs group by wanquanyizhi order by total1 desc

) aa,

(

select count(bw.wanquanyizhi) as total2,wanquanyizhi from comptest_by bw group by wanquanyizhi order by total2 desc

) bb

where aa.wanquanyizhi=bb.wanquanyizhi

and total1=total2 and total1=1

)  and  substr(ASCS.receiver_id,0,1)='M'

and substr(BW.RECEIVER_ID,0,1)='F'

)

--N:N+N，更新N端，设置状态为：“

客户id不一致2

”UPDATE COMP_ASC_TEST_1 SET CMP_STATE='客户id不一致2' WHERE ID IN(    SELECT A.ID FROM    (        SELECT ID,UNIQUECODING,ROW_NUMBER() OVER (PARTITION BY UNIQUECODING ORDER BY UNIQUECODING) AS ORD FROM COMP_ASC_TEST_1    )A JOIN    (        SELECT ID,UNIQUECODING,ROW_NUMBER() OVER (PARTITION BY UNIQUECODING ORDER BY UNIQUECODING) AS ORD FROM COMP_TSKF_TEST_1    )    B ON A.UNIQUECODING=B.UNIQUECODING AND A.ORD=B.ORD    AND A.UNIQUECODING IN    (        SELECT A.UNIQUECODING FROM        (            SELECT COUNT(*) AS TOTAL,UNIQUECODING FROM COMP_ASC_TEST_1 GROUP BY UNIQUECODING  HAVING COUNT(*)>1        )A JOIN        (            SELECT COUNT(*) AS TOTAL,UNIQUECODING FROM COMP_TSKF_TEST_1 GROUP BY UNIQUECODING  HAVING COUNT(*)>1        )B ON A.UNIQUECODING=B.UNIQUECODING AND A.TOTAL