sql server 删除重复的数据

1--删除name,sex重复的数据,只留下一条数据；

select * into #lin1 from student   --先将student的数据插入临时表#lin1中；
select min(id) as iidd into #lin2 from student group by name,sex   --根据name,sex分组，并获取每组中最小的id；
select * from student where id not in(select iidd from #lin2)      --确认查询出的这些数据，是否是重复的并且可以删除的数据；
delete student where id not in(select iidd from #lin2)             --确认无误后，删除重复的数据；
select * from student                                              --查看数据；

insert into student select * from #lin1     --删除后又想回复删除前的数据时，执行这条语句；
drop table #lin1        
drop table #lin2                            --最后删除这两张临时表；

2--只删除一个字段（name）的重复数据时；

select min(id) from student group by name
delete student where id not in (select min(id) from student group by name)

将删除语句整合成一条：

DELETE a FROM student AS a  JOIN (SELECT name,COUNT(*) AS num,MAX(id) FROM student GROUP BY name HAVING COUNT(*)>1) AS b ON a.name=b.name WHERE a.id<b.id;