分享SQLServer删除重复行的6个方法

SQL Server删除重复行是我们最常见的操作之一，下面就为您介绍六种适合不同情况的SQL Server删除重复行的方法，供您参考。

<br>delect table where id not in ( <br>select max(id) from table group by col1,col2,col3... <br>) <br> <br>group by 子句后跟的字段就是你用来判断重复的条件，如只有col1，那么只要col1字段内容相同即表示记录相同。 <br><br>2. 如果是判断所有字段也可以这样 <br> 代码如下:<pre class="brush:php;toolbar:false layui-box layui-code-view layui-code-notepad"><ol class="layui-code-ol"><li><br>select * into #aa from table group by id1,id2,.... <br>delete table <br>insert into table <br>select * from #aa <br> <br>3. 没有ID的情况 <br> 代码如下:<pre class="brush:php;toolbar:false layui-box layui-code-view layui-code-notepad"><ol class="layui-code-ol"><li><br>select identity(int,1,1) as id,* into #temp from tabel <br>delect # where id not in ( <br>select max(id) from # group by col1,col2,col3...) <br>delect table <br>inset into table(...) <br>select ..... from #temp <br> <br>4. col1+','+col2+','...col5 联合主键 <br> 代码如下:<pre class="brush:php;toolbar:false layui-box layui-code-view layui-code-notepad"><ol class="layui-code-ol"><li><br>select * from table where col1+','+col2+','...col5 in ( <br>select max(col1+','+col2+','...col5) from table <br>where having count(*)>1 <br>group by col1,col2,col3,col4 <br>) <br> <br>group by 子句后跟的字段就是你用来判断重复的条件，如只有col1，那么只要col1字段内容相同即表示记录相同。 <br><br>5. <br> 代码如下:<pre class="brush:php;toolbar:false layui-box layui-code-view layui-code-notepad"><ol class="layui-code-ol"><li><br>select identity(int,1,1) as id,* into #temp from tabel <br>select * from #temp where id in ( <br>select max(id) from #emp where having count(*)>1 group by col1,col2,col3...) <br> <br>6. <br> 代码如下:<pre class="brush:php;toolbar:false layui-box layui-code-view layui-code-notepad"><ol class="layui-code-ol"><li><br>select distinct * into #temp from tablename <br>delete tablename <br>go <br>insert tablename select * from #temp Sqlclub <br>go <br>drop table #temp <br> <br>以上就是SQL Server删除重复行的方法介绍。                    </li></ol></pre></li></ol></pre></li></ol></pre></li></ol></pre></li></ol></pre>