删除重复记录(单表找出重复最大值后并全部相加)

一般删除重复例题(如何改变没有建立任何索引等则不实用,一般需要时该sql都不现实,没有这么好的环境提供给你做，呵呵!!!)：
DELETE FROM EMP E
WHERE E.ROWID > (SELECT MIN(X.ROWID)
                   FROM EMP X
                   WHERE X.EMP_NO = E.EMP_NO .......);
                  

这是一个test_test测试表
pid paymoney    getmoney 
12 3204.00     4.00
12 3204.00     3200.00
13 12816.00    12816.00

 

对paymoney和getmoney求和,paymoeny在统计的时候根据pid去除重复
查出来要这个效果：
paymoney    getmoney
16020.00    16016.00

 

SQL> desc test_test;
Name     Type   Nullable Default Comments
-------- ------ -------- ------- --------
PID      NUMBER Y                        
PAYMONEY NUMBER Y                        
GETMONEY NUMBER Y

SQL> select * from test_test;
 
       PID   PAYMONEY   GETMONEY
---------- ---------- ----------
        12 3204.00     4.00
        12 3204.00     3200.00
       13 12816.00    12816.00    

   
方法一：
select sum(getmoney),sum(paymoney) from (
  select pid,paymoney,max(getmoney) getmoney from test_test
  where (pid,paymoney) in (select pid,paymoney
                       from test_test
                       group by pid,paymoney
                       having count(pid)>1)
   group by pid,paymoney
   union
     select pid,paymoney,max(getmoney) getmoney from test_test
  where (pid,paymoney) not in (select pid,paymoney
                       from test_test
                       group by pid,paymoney
                       having count(pid)>1)
   group by pid,paymoney ) ;
  
方法二：
   select sum(getmoney),sum(paymoney) from test_test emp_x where getmoney=
(select max(getmoney) from test_test emp_y where emp_x.pid=emp_y.pid 
  and emp_x.paymoney=emp_y.paymoney);
 
 
########################################################
删除对方存在的记录,但数据量非常大时删除了半天也没有反映,不知道那里
还会存在公认为速度最快的删除方法的SQL语句呀！！！！！!
delete from temp_dingou_s di where exists(select fee_terminal_id
from t_act_mmsdz_user_info_bak1106 dz where di.PHONENO=zd.fee_terminal_id
     and dz.service_id='011001' and dz.act_id='7206831')

##########################################################

 

 

###版权-----found2008 ------邮箱:hiho1128@126.com