------------------- 3、去除表中重复行 ------------------ (1) DELETE FROM table_name A WHERE ROWID > ( SELECT min(rowid) FROM table_name B WHERE A.key_values = B.key_values); (2)create table table2 as select distinct * from table1; drop table1; rename table2 to table1; (3) Delete from mytable where rowid not in( select max(rowid) from mytable group by column_name ); (4)delete from mytable t1 where exists (select 'x' from my_table t2 where t2.key_value1 = t1.key_value1 and t2.key_value2 = t1.key_value2 ... and t2.rowid > t1.rowid);你自己根据自己的情况写一下。
jlandzpa(jlandzpa) 的怎么不对,由什么错误么? SQL> select * from e;AAA BBB CCC -------------------- --------- --------- 101 1 102 2 22 103 3 103 43 102 5 55 aaa 6 6 SQL> select aaa,count(*) from e group by aaa having count(*)>1;AAA COUNT(*) -------------------- --------- 102 2 103 2
3、去除表中重复行
------------------
(1) DELETE FROM table_name A WHERE ROWID > (
SELECT min(rowid) FROM table_name B
WHERE A.key_values = B.key_values);
(2)create table table2 as select distinct * from table1;
drop table1;
rename table2 to table1;
(3) Delete from mytable where rowid not in(
select max(rowid) from mytable
group by column_name );
(4)delete from mytable t1
where exists (select 'x' from my_table t2
where t2.key_value1 = t1.key_value1
and t2.key_value2 = t1.key_value2
...
and t2.rowid > t1.rowid);你自己根据自己的情况写一下。
SQL> select * from e;AAA BBB CCC
-------------------- --------- ---------
101 1
102 2 22
103 3
103 43
102 5 55
aaa 6 6
SQL> select aaa,count(*) from e group by aaa having count(*)>1;AAA COUNT(*)
-------------------- ---------
102 2
103 2