Oracle找重复数据

Oracle找重复数据

select * from t_yewuhezuo t1   where  t1.c_ply_nop in(select c_ply_nop from t_yewuhezuo group by c_ply_nop having count(c_ply_nop)>1) 

select * from dailixieyi d1 where d1.DAILINO in(select dailino from dailixieyi group by dailino having count(dailino)>1)

select * from  t_taizhang t1 where t1.C_PLY_NO in(select C_PLY_NO from t_taizhang group by C_PLY_NO having count(C_PLY_NO)>1)

select * from  web_ply_shouxufei t1 where t1.C_PLY_NO in(select C_PLY_NO from web_ply_shouxufei group by C_PLY_NO having count(C_PLY_NO)>1)

select * from  t_fndywtest  t1 where t1.CLAIM_NO in(select CLAIM_NO from t_fndywtest group by CLAIM_NO having count(CLAIM_NO)>1)

select * from  t_rtywtest  t1 where t1.CLAIM_NO in(select CLAIM_NO    from  t_rtywtest group by CLAIM_NO having count(CLAIM_NO)>1)

select * from test_chechuanshi2  t1 where t1.c_ply_NO in(select c_ply_no    from  test_chechuanshi2 group by c_ply_no having count(C_ply_no)>1)




DELETE from 表 WHERE (id) IN ( SELECT id FROM 表 GROUP BY id HAVING COUNT(id) > 1) AND ROWID NOT IN (SELECT MIN(ROWID) FROM 表 GROUP BY id HAVING COUNT(*) > 1);

DELETE from t_taizhang WHERE (c_ply_no) IN ( SELECT c_ply_no FROM t_taizhang GROUP BY c_ply_no HAVING COUNT(c_ply_no) > 1) AND ROWID NOT IN (SELECT MIN(ROWID) FROM t_taizhang GROUP BY c_ply_no HAVING COUNT(*) > 1);

--菲耐德未决
DELETE from t_fndywtest  WHERE (CLAIM_NO) IN ( SELECT CLAIM_NO FROM t_fndywtest GROUP BY CLAIM_NO HAVING COUNT(CLAIM_NO) > 1) AND ROWID NOT IN (SELECT MIN(ROWID) FROM t_fndywtest GROUP BY CLAIM_NO HAVING COUNT(*) > 1);
---车船税
DELETE from test_chechuanshi2  WHERE (c_ply_no) IN ( SELECT c_ply_no FROM  test_chechuanshi2 GROUP BY c_ply_no HAVING COUNT(c_ply_no) > 1) AND ROWID NOT IN (SELECT MIN(ROWID) FROM test_chechuanshi2 GROUP BY c_ply_no HAVING COUNT(*) > 1);



----合并重复的数据

select distinct aa.REPORT_NO,  aa.REPORT_DATE, sum(aa.STL_ENDCASE_AMT),aa.CHECK_MAN 
from yaicuser.F_CLM_SETTLED_LIST aa  where aa.REPORT_NO='45001000030001140000658'  group by  aa.REPORT_NO,  aa.REPORT_DATE,aa.CHECK_MAN