日期:2014-05-16  浏览次数:20408 次

orcale 删除重复
select tid,tname ,rowid from t_temp_test ; 
select tid,tname ,max(rowid) from t_temp_test   group by tid,tname;

delete from t_temp_test t1 where rowid not in  
  ( select max(rowid) from t_temp_test  t2  where t1.tid=t2.tid
  and t1.tname=t2.tname);

insert into t_temp_test(tid,tname) values('0001','lisi');
insert into t_temp_test(tid,tname) values('0001','lisi');
insert into t_temp_test(tid,tname) values('0001','lisi');
insert into t_temp_test(tid,tname) values('0001','lisi');
insert into t_temp_test(tid,tname) values('0001','lisi');
insert into t_temp_test(tid,tname) values('0002','zhangsan');
insert into t_temp_test(tid,tname) values('0002','zhangsan');
insert into t_temp_test(tid,tname) values('0002','zhangsan');
insert into t_temp_test(tid,tname) values('0002','zhangsan');

select * from t_mm_dir_info

delete from employee e2 where rowid not in (
  select max(e1.rowid) from employee e1 where 
 e1.emp_id=e2.emp_id and e1.emp_name=e2.emp_name and e1.salary=e2.salary);--这里用min(rowid)也可以。



select * from t_temp_test where rownum > 5