首页 > 开发 > SQLServer > 正文

sql server 删除重复的数据

2016-07-12 21:06:26  来源:慕课网
  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)