如何删除重复的记录

[csteven]

？？

[happydreamer]

比较赞成sky_blue的方法

[happydreamer]

chumpg和lmcglmc你们把所有相同记录都delete了，sky_blue做法是保留了一项

[glmcglmc]

delete from table
where id in (
select id from (
select id,count(id) as dup_count from talbe group by id)
where dup_count>1
)

[glmcglmc]

delete from tmp
where id in (
select id from (
select id,count(id) as dup_count from tmp group by id) a
where a.dup_count>1
)

[chump]

delete table1
from table1 a,
(select id from table1 group by id having count(id)>1) b
where a.id=b.id

[蓝天]

都删啊？一条都不留？

[蓝天]

select distinct * into #tmp from yourtable
truncate table yourtable
insert yourtable select * from #tmp

[newyu1127]

老纳的方法很好，我赞同，问题是：如果我没有ID这一字段，而我又要作如下的判断，如果表中有重复的记录，则执行老纳所言的方法，如果没有重复的记录则不做任何事。这个判断怎么写呢？请赐教。

[CrazyFor]

delete from table
where id not in (select id from table)

[CrazyFor]

修正：
delete from table
where id not in (select max(id) from table group by id)

[playyuer]

参阅《程序员》杂志 2002年11月: 《一道褒贬不一的 SQL 考试题》一文,大概是第一题: