很棘手的记录数重复问题，用了网上的方法不行啊

Question

select a.*
from tb_b_ft_ofr_rev_200807_bk a
where a.rowid <>(select max(b.rowid) from tb_b_ft_ofr_rev_200807_bk b where a.chrg=b.chrg
                 and a.acct_item_type_id=b.acct_item_type_id and a.chrg_level=b.chrg_level)我的这张表里面的确有重复的记录，我想找到然后删除，我这样查询居然没有查询到，
用了这个sql  select * from tb_b_ft_ofr_rev_200807_bk a
where exists
(
select 'x' from tb_b_ft_ofr_rev_200807_bk b
where a.cust_id=b.cust_id and a.chrg=b.chrg and a.acct_item_type_id=b.acct_item_type_id and instr(a.rowid,rowid)=0
); 目前为止还在跑估计也不行，请问还有什么好方法？

Answer 1

MULTI>1 都是重复的
SQL> SELECT * FROM T;                                    ID1                                     ID2
--------------------------------------- ---------------------------------------
                                      1                                       1
                                      1                                       1
                                      1                                       2
                                      1                                       2
                                      2                                       1SQL> SELECT ID1,ID2,ROW_NUMBER()OVER(PARTITION BY ID1,ID2 ORDER BY ID1,ID2) MULTI FROM T;                                    ID1                                     ID2      MULTI
--------------------------------------- --------------------------------------- ----------
                                      1                                       1          1
                                      1                                       1          2
                                      1                                       2          1
                                      1                                       2          2
                                      2                                       1          1

Answer 2

用下面的循环可以找到重复的数据
select * from tb_b_ft_ofr_rev_200807_bk
where (id,chrg,acct_item_type_id) in
(select id,chrg,acct_item_type_id from tb_b_ft_ofr_rev_200807_bk 
group by id,chrg,acct_item_type_id
having count(*)>1)
用下面语句,每次可以删除一条重复数据,写个小循环,删到没重复记录为止
delete tb_b_ft_ofr_rev_200807_bk
where (id,chrg,acct_item_type_id) in
(select id,chrg,acct_item_type_id from tb_b_ft_ofr_rev_200807_bk 
group by id,chrg,acct_item_type_id
having count(*)>1) and rownum=1

Answer 3

最高效的一种方法：
SQL> select * from test;       AAA BBB
---------- -------------
         1 vv
         2 vv
         1 vv
         3 vv
         1 vv
         2 vv
         4 vv已选择7行。SQL> delete from test
  2  where rowid in(select rowid
  3  from (select rowid,row_number() over(partition by aaa order by aaa) rn
  4  from test)
  5  where rn>1);已删除3行。SQL> select * from test;       AAA BBB
---------- --------------------
         1 vv
         2 vv
         3 vv
         4 vv

Answer 4

EXP:select line,value,k0 from kk1 10 A
1 10 A
1 10 A
3 31 B
3 31 B
1 10 A
1 10 A
1 10 A
3 31 B
3 31 B解决快速的语句:delete from kk a where a.rowid NOT in(select max(b.rowid) from kk b where a.line=b.line and a.value=b.value and a.k0=b.k0)----------------------------------------------------------------如果表的列数少的，这个语句就好用！利用分析函数：
delete from kk where rowid in(
select row_id from (
select rowid row_id,row_number() over(partition by line,k0,value order by rowid ) rn from kk
) where rn<>1)
RESULT:1 1 10 A
2 2 40 B
3 3 31 B
4 1 22 A
5 1 31 B----------------------------------------------------------------
http://cosio.itpub.net/post/10244/467245

Answer 5

SELECT *
  FROM TABLE1 A
 WHERE ROWID < (SELECT MAX(ROWID)
                  FROM S_TABLE1 B
                 WHERE A.ID = B.ID
                 GROUP BY ID);

Answer 6

delete tb_b_ft_ofr_rev_200807_bk 
where (id,chrg,acct_item_type_id) in 
(select id,chrg,acct_item_type_id from tb_b_ft_ofr_rev_200807_bk 
group by id,chrg,acct_item_type_id 
having count(*)>1) and 
rownum not in (select min(rownum) from tb_b_ft_ofr_rev_200807_bk 
group by id,chrg,acct_item_type_id)
试试这个