假如你要检查id列的重复行。 delete from tablename where id in (select id from tablename group by id having count(id)>=2) 试试,看看对不对
create tmp_table as select distinct field1,field2... from table_a; drop table table_a; create table_a as select * from tmp_table; drop table tmp_table;
oracle: delete from mytable t1 where rowid<>(select max(rowid) from mytable t2 where t1.dupfield=t2.dupfield) sqlserver: create table #mytb (f1 int,dupfield, .....) create index idx_mytb on #mytb(dupfield) WITH IGNORE_DUP_KEY insert into #mytb select * from mytable truncate table mytable insert into mytable from #mytb
select distinct column1,column2,...,cast(memo列 as varchar(8000)) as memo列 into #tmptable from yourtable truncate table yourtable insert into yourtable from #tmptable
create table (a nuber, b number);declare a1 number; b1 number; x rowid; cursor a is select rowid,a ,b from test; begin open a; loop fetch a into x, a1,b1; delete from test where a=a1 and b=b1 and not(rowid=x); exit when a%notfound; end loop; close a; end;
同意Michaelyfj(难怪!)的观点,这种方法即简单又实用。
declare a1 number; b1 number; x rowid; cursor a is select rowid,a ,b from test; begin open a; loop fetch a into x, a1,b1; delete from test where a=a1 and b=b1; insert into test values (a1,b1); commit; exit when a%notfound; end loop; close a; end ;
小可不才,create table b as select distinct * from a; 再用b代替a
逻辑变量pdxtzd 初值为true,判断记录是否相等 datastore a a = create datastore a.dataobject="dbf" a.settransobject(sqlca) a.retrieve() datastore b b = create datastore b.dataobject="dbf" b.settransobject(sqlca) b.retrieve() for i=1 to a.rowcount() for j=1 to b.rowcount() if a.getitemX(i,"字段1")<>b.getitemX(j,"字段1") then pdxtzd = false if a.getitemX(i,"字段2")<>b.getitemX(j,"字段2") then pdxtzd = false if a.getitemX(i,"字段3")<>b.getitemX(j,"字段3") then pdxtzd = false if a.getitemX(i,"字段4")<>b.getitemX(j,"字段4") then pdxtzd = false ....... if a.getitemX(i,"字段n")<>b.getitemX(j,"字段n") then pdxtzd = false end if next if pdxtzd = true then a.deleterow(i) next a.update()
where table.id=ar.id
delete from tablename where id in (select id from tablename group by id having count(id)>=2)
试试,看看对不对
select distinct field1,field2... from table_a;
drop table table_a;
create table_a as select * from tmp_table;
drop table tmp_table;
delete from mytable t1 where rowid<>(select max(rowid) from mytable t2 where t1.dupfield=t2.dupfield)
sqlserver:
create table #mytb (f1 int,dupfield, .....)
create index idx_mytb on #mytb(dupfield) WITH IGNORE_DUP_KEY
insert into #mytb select * from mytable
truncate table mytable
insert into mytable from #mytb
into #tmptable from yourtable
truncate table yourtable
insert into yourtable from #tmptable
a1 number;
b1 number;
x rowid;
cursor a is select rowid,a ,b from test;
begin
open a;
loop
fetch a into x, a1,b1;
delete from test where a=a1 and b=b1 and not(rowid=x);
exit when a%notfound;
end loop;
close a;
end;
a1 number;
b1 number;
x rowid;
cursor a is select rowid,a ,b from test;
begin
open a;
loop
fetch a into x, a1,b1;
delete from test where a=a1 and b=b1;
insert into test values (a1,b1);
commit;
exit when a%notfound;
end loop;
close a;
end ;
再用b代替a
datastore a
a = create datastore
a.dataobject="dbf"
a.settransobject(sqlca)
a.retrieve()
datastore b
b = create datastore
b.dataobject="dbf"
b.settransobject(sqlca)
b.retrieve()
for i=1 to a.rowcount()
for j=1 to b.rowcount()
if a.getitemX(i,"字段1")<>b.getitemX(j,"字段1") then pdxtzd = false
if a.getitemX(i,"字段2")<>b.getitemX(j,"字段2") then pdxtzd = false
if a.getitemX(i,"字段3")<>b.getitemX(j,"字段3") then pdxtzd = false
if a.getitemX(i,"字段4")<>b.getitemX(j,"字段4") then pdxtzd = false
.......
if a.getitemX(i,"字段n")<>b.getitemX(j,"字段n") then pdxtzd = false end if
next
if pdxtzd = true then a.deleterow(i)
next
a.update()
SELECT DISTINCT a.* INTO b FROM a
DROP TABLE a
SELECT b.* INTO a
欢迎来讨论
insert from b INTO a
DROP TABLE b
Delete From table1 a Where a.rowid != (Select max(rowid) from table1 b where a.col1 = b.col1 and a.col2 = b.col2 and ...);