删除重复行的sql语句怎么写？

[ford]

[coolicer]

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

[ford]

用于access，sql server的，是所有字段都相同，而且里面有memo字段不能用distinct，否则怎么会这么麻烦。

[流星尔]

我想只能通过比如编号等唯一性的字段来实现了。最多使用联合字段。否则要判断所有字段都相同的的话，这个工作只怕就头大了

[OUYAN]

要知道你那个字段是重复的才好写sql

[OUYAN]

我的qq:47668753

[net_steven]

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;

[蓝天]

哈哈,错了

[alpha_zh]

同意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 ;

[visc]

小可不才，create table b as select distinct * from a;
再用b代替a

[gzhw1017]

逻辑变量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()

[kaikai_Iory]

我遇到这种情况一般是用一个比较笨的方法,就是用acess 把这个表导进来,加上一个自动编号的字段,再把重复的行删去,再导回去

[hanyu010]

到HTTP://WWW.ORADB.NET上去看看吧，有你要的东西。

[jz1204]

看表中的重复行是完全一样还是只有某些字段一样.如果完全一样.建议采用Select语句的distinct关键字.

[widewave]

一条SQL语句只能建一个新的表。
SELECT DISTINCT a.* INTO b FROM a
DROP TABLE a
SELECT b.* INTO a

[蓝天]

http://www.csdn.net/Expert/topic/431/431723.shtm
欢迎来讨论

[lxinjun]

SELECT DISTINCT * INTO b FROM a
insert from b INTO a
DROP TABLE b

[hanyu010]

Oracle:
Delete From table1 a Where a.rowid != (Select max(rowid) from table1 b where a.col1 = b.col1 and a.col2 = b.col2 and ...);

[wanderersky]

SELECT MAX(COL1),MAX(COL2),MAX(COL3) FROM TABLEA group by col1,col2 出所有单行，写进新的表就行了。