数据比对的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<B.TOTAL
    )
);