频道导航

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

2019-01-04 MsSQL 前端之家

前端之家收集整理的这篇文章主要介绍了分享SQL Server删除重复行的6个方法，前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。

1.如果有ID字段，就是具有唯一性的字段
<div class="codetitle"><a style="CURSOR: pointer" data="71195" class="copybut" id="copybut71195" onclick="doCopy('code71195')"> 代码如下:

<div class="codebody" id="code71195">
delect table where id not in (
select max(id) from table group by col1,col2,col3...
)

group by 子句后跟的字段就是你用来判断重复的条件，如只有col1，那么只要col1字段内容相同即表示记录相同。 2. 如果是判断所有字段也可以这样
<div class="codetitle"><a style="CURSOR: pointer" data="62909" class="copybut" id="copybut62909" onclick="doCopy('code62909')"> 代码如下:

<div class="codebody" id="code62909">
select into #aa from table group by id1,id2,....
delete table
insert into table
select from #aa

3. 没有ID的情况
<div class="codetitle"><a style="CURSOR: pointer" data="40883" class="copybut" id="copybut40883" onclick="doCopy('code40883')"> 代码如下:

<div class="codebody" id="code40883">
select identity(int,1,1) as id, into #temp from tabel
delect # where id not in (
select max(id) from # group by col1,col3...)
delect table
inset into table(...)
select ..... from #temp

4. col1+','+col2+','...col5 联合主键
<div class="codetitle"><a style="CURSOR: pointer" data="80404" class="copybut" id="copybut80404" onclick="doCopy('code80404')"> 代码如下:

<div class="codebody" id="code80404">
select from table where col1+','...col5 in (
select max(col1+','...col5) from table
where having count()>1
group by col1,col3,col4
)

group by 子句后跟的字段就是你用来判断重复的条件，如只有col1，那么只要col1字段内容相同即表示记录相同。 5.
<div class="codetitle">@L_403_4@ 代码如下:<div class="codebody" id="code61841">
select identity(int, into #temp from tabel
select from #temp where id in (
select max(id) from #emp where having count()>1 group by col1,col3...)

6.
<div class="codetitle"><a style="CURSOR: pointer" data="44087" class="copybut" id="copybut44087" onclick="doCopy('code44087')"> 代码如下:<div class="codebody" id="code44087">
select distinct into #temp from tablename
delete tablename
go
insert tablename select from #temp sqlclub
go
drop table #temp

以上就是sql Server删除重复行的方法介绍。

重复行