怎么通过SQL语句删除重复的记录,只保留一条

[青蛙头-冰山]

怎么通过SQL语句删除重复的记录,只保留一条

[MarcuseXiao]

加关键字distinct
SELECT DISTINCT au_id FROM titleauthor

[acewang]

delete from table_name where table_name.id in
(select b.id from table_name b where table_name.id<>b.id)

[fq_ln]

SELECT DISTINCT id FROM mytable where ......

其实就是关键字distinct，它可以筛选数据，删除完全重复的数据

[青蛙头-冰山]

我是想把其他重复的删除掉,有没有什么办法

[v192]

不知道加强制条件约束能不能行。把不想重复的字段添加唯一性约束。
我瞎说的，错了，别骂我！

[popu2]

select distinct * from mytable into #temptable
delete from mytable
insert into mytable (select * from #temptable)

[v192]

SQL Server 能支持上面那种写法嘛？
我知道Informix是可以得。

[青蛙头-冰山]

select distinct * from mytable into #temptable
delete from mytable
insert into mytable (select * from #temptable)
要是有几W条记录,那不就死了

[yongxin800]

试试：
delete from
(select ID,count(*) from tablename
group by ID
having count(*)>=2 )

[popu2]

我的写法写在存储过程里不就可以了吗?几万条又怎样,不过经常这样调用就占资源了。
如果表中有唯一字段有加过索引,
可以
delete from mytable where yourid not in (select distinct id from mytable)

[loulanlouzhu]

怎么会有重复记录呢！？

重复的就不应该加近来！

[kysyuan]

假设你的表中有一个主健ID,其它列上有重复,但ID上不应该有重复,数据库中是不应该出现两条记录完全一样的,如果出现了,那也只能说是错误!!!,如果是我说的前面的那种情况解决办法如下:
delete from table1 where ID not in(select dist ID from table1)

[ayine]

最好的方法就是
select distinct * into #TempTable from mytable
delete from mytable
insert into mytable (select * from #TempTable)
GO
你在查询分析器里试试,我以前也碰到过类似的问题,只有这个方法解决!!
因为你用其它方法删除时,会报个键列值影响过多的行的错!!!Good Luck

[popu2]

可以结贴啦

[码视野]

接分！

[wzhgsww]

up

[kuangren]

加上 distinct不就可以了～……

[spiketang]

up

[青蛙头-冰山]

都不能用的