有ＩＤ列吗 delect from testdict1 where id not in (select top 1 id from testdict1 group by c_name )
先确定一个唯一的id 如果没有请添加一个 alter table testdict1 add rownum int identified ; 然后删除delete from testdict1 where rowno not in (select c_name,min(id) as id from testdict1 group by c_name having count(*) > 1) ;
不能编辑了，第一个应该是 alter table testdict1 add rownum int identity ;
后发抖，又错了 第二个字段写错了，更正为 1,alter table testdict1 add rownum int identity ;2,delete from testdict1 where rownum not in (select min(rownum) as id from testdict1 group by c_name having count(*) > 1) ;
之一:{ --测试数据 /*----------------------------- select * from tt -----------------------------*/ id pid ----------- ----------- 1 1 1 1 2 2 3 3 3 3 3 3（所影响的行数为 6 行）首先，如何查询table中有重复记录 select *,count(1) as rownum from tt group by id, pid having count(1) > 1 id pid rownum ----------- ----------- ----------- 1 1 2 3 3 3（所影响的行数为 2 行）方法一:使用distinct和临时表 if object_id(tempdb..#tmp) is not null drop table #tmp select distinct * into #tmp from tt truncate table tt insert into tt select * from #tmp方法二:添加标识列 alter table tt add newid int identity(1,1) go delete from tt where exists(select 1 from tt a where a.newid>tt.newid and tt.id=a.id and tt.pid=a.pid) go alter table tt drop column newid go--测试结果 /*----------------------------- select * from tt -----------------------------*/ id pid ----------- ----------- 1 1 2 2 3 3（所影响的行数为 3 行） } 本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/WalkerToMoon/archive/2007/06/08/1643437.aspx
忙了一天，晕了，道歉 2,delete from testdict1 where rownum not in (select min(rownum) as id from testdict1 group by c_name )
不要后面的count(*)>1吗？
delete from testdict1 where rownum not in (select min(rownum) as id from testdict1）and select count(*) from testdict1 group by c_name>1
没有ID列的话，可以找出重复的记录，然后delete top 1 多条记录的话，需要循环删除。
select count(yu)重复, yu from chengji group by yu having count(yu) >1 --列出重复的记录数，并列出他的name属性 group by yu having count(yu) >1 --按num分组后找出表中num列重复，即出现次数大于一次 delete from student ---删数据： group by num having count(num) >1
delect from testdict1 where id not in (select top 1 id from testdict1 group by c_name )
然后删除delete from testdict1 where rowno not in (select c_name,min(id) as id from testdict1 group by c_name having count(*) > 1) ;
alter table testdict1 add rownum int identity ;
--测试数据
/*-----------------------------
select * from tt
-----------------------------*/
id pid
----------- -----------
1 1
1 1
2 2
3 3
3 3
3 3（所影响的行数为 6 行）首先，如何查询table中有重复记录
select *,count(1) as rownum
from tt
group by id, pid
having count(1) > 1
id pid rownum
----------- ----------- -----------
1 1 2
3 3 3（所影响的行数为 2 行）方法一:使用distinct和临时表
if object_id(tempdb..#tmp) is not null
drop table #tmp
select distinct * into #tmp from tt
truncate table tt
insert into tt select * from #tmp方法二:添加标识列
alter table tt add newid int identity(1,1)
go
delete from tt where exists(select 1 from tt a where a.newid>tt.newid and tt.id=a.id and tt.pid=a.pid)
go
alter table tt drop column newid
go--测试结果
/*-----------------------------
select * from tt
-----------------------------*/
id pid
----------- -----------
1 1
2 2
3 3（所影响的行数为 3 行）
}
本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/WalkerToMoon/archive/2007/06/08/1643437.aspx
2,delete from testdict1 where rownum not in (select min(rownum) as id from testdict1 group by c_name )
不要后面的count(*)>1吗？
多条记录的话，需要循环删除。
group by yu
having count(yu) >1 --按num分组后找出表中num列重复，即出现次数大于一次
delete from student ---删数据：
group by num
having count(num) >1