【转】SQL删除重复记录,只保留其中一条

2、删除表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断，只留有rowid最小的记录

<span style="color: #0000ff;">delete</span> <span style="color: #0000ff;">from</span> people <span style="color: #0000ff;">where</span>   peopleName <span style="color: #808080;">in</span> (<span style="color: #0000ff;">select</span> peopleName    <span style="color: #0000ff;">from</span> people <span style="color: #0000ff;">group</span> <span style="color: #0000ff;">by</span> peopleName      <span style="color: #0000ff;">having</span> <span style="color: #ff00ff;">count</span>(peopleName) <span style="color: #808080;">></span> <span style="color: #800000; font-weight: bold;">1</span>) <span style="color: #808080;">and</span>   peopleId <span style="color: #808080;">not</span> <span style="color: #808080;">in</span> (<span style="color: #0000ff;">select</span> <span style="color: #ff00ff;">min</span>(peopleId) <span style="color: #0000ff;">from</span> people <span style="color: #0000ff;">group</span> <span style="color: #0000ff;">by</span> peopleName     <span style="color: #0000ff;">having</span> <span style="color: #ff00ff;">count</span>(peopleName)<span style="color: #808080;">></span><span style="color: #800000; font-weight: bold;">1</span>)

3、查找表中多余的重复记录（多个字段）

<span style="color: #0000ff;">select</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">from</span> vitae a <span style="color: #0000ff;">where</span> (a.peopleId,a.seq) <span style="color: #808080;">in</span> (<span style="color: #0000ff;">select</span> peopleId,seq <span style="color: #0000ff;">from</span> vitae <span style="color: #0000ff;">group</span> <span style="color: #0000ff;">by</span> peopleId,seq <span style="color: #0000ff;">having</span> <span style="color: #ff00ff;">count</span>(<span style="color: #808080;">*</span>) <span style="color: #808080;">></span> <span style="color: #800000; font-weight: bold;">1</span>)

4、删除表中多余的重复记录（多个字段），只留有rowid最小的记录

<span style="color: #0000ff;">delete</span> <span style="color: #0000ff;">from</span> vitae a <span style="color: #0000ff;">where</span> (a.peopleId,a.seq) <span style="color: #808080;">in</span> (<span style="color: #0000ff;">select</span> peopleId,seq <span style="color: #0000ff;">from</span> vitae <span style="color: #0000ff;">group</span> <span style="color: #0000ff;">by</span> peopleId,seq <span style="color: #0000ff;">having</span> <span style="color: #ff00ff;">count</span>(<span style="color: #808080;">*</span>) <span style="color: #808080;">></span> <span style="color: #800000; font-weight: bold;">1</span>) <span style="color: #808080;">and</span> rowid <span style="color: #808080;">not</span> <span style="color: #808080;">in</span> (<span style="color: #0000ff;">select</span> <span style="color: #ff00ff;">min</span>(rowid) <span style="color: #0000ff;">from</span> vitae <span style="color: #0000ff;">group</span> <span style="color: #0000ff;">by</span> peopleId,seq <span style="color: #0000ff;">having</span> <span style="color: #ff00ff;">count</span>(<span style="color: #808080;">*</span>)<span style="color: #808080;">></span><span style="color: #800000; font-weight: bold;">1</span>)

5、查找表中多余的重复记录（多个字段），不包含rowid最小的记录 

<span style="color: #0000ff;">select</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">from</span> vitae a <span style="color: #0000ff;">where</span> (a.peopleId,a.seq) <span style="color: #808080;">in</span> (<span style="color: #0000ff;">select</span> peopleId,seq <span style="color: #0000ff;">from</span> vitae <span style="color: #0000ff;">group</span> <span style="color: #0000ff;">by</span> peopleId,seq <span style="color: #0000ff;">having</span> <span style="color: #ff00ff;">count</span>(<span style="color: #808080;">*</span>) <span style="color: #808080;">></span> <span style="color: #800000; font-weight: bold;">1</span>) <span style="color: #808080;">and</span> rowid <span style="color: #808080;">not</span> <span style="color: #808080;">in</span> (<span style="color: #0000ff;">select</span> <span style="color: #ff00ff;">min</span>(rowid) <span style="color: #0000ff;">from</span> vitae <span style="color: #0000ff;">group</span> <span style="color: #0000ff;">by</span> peopleId,seq <span style="color: #0000ff;">having</span> <span style="color: #ff00ff;">count</span>(<span style="color: #808080;">*</span>)<span style="color: #808080;">></span><span style="color: #800000; font-weight: bold;">1</span>)  

6.消除一个字段的左边的第一位：

<span style="color: #0000ff;">update</span> tableName <span style="color: #0000ff;">set</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">Title</span><span style="color: #ff0000;">]</span><span style="color: #808080;">=Right</span>(<span style="color: #ff0000;">[</span><span style="color: #ff0000;">Title</span><span style="color: #ff0000;">]</span>,(<span style="color: #ff00ff;">len</span>(<span style="color: #ff0000;">[</span><span style="color: #ff0000;">Title</span><span style="color: #ff0000;">]</span>)<span style="color: #808080;">-</span><span style="color: #800000; font-weight: bold;">1</span>)) <span style="color: #0000ff;">where</span> Title <span style="color: #808080;">like</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">村%</span><span style="color: #ff0000;">‘</span>

7.消除一个字段的右边的第一位：

<span style="color: #0000ff;">update</span> tableName <span style="color: #0000ff;">set</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">Title</span><span style="color: #ff0000;">]</span><span style="color: #808080;">=left</span>(<span style="color: #ff0000;">[</span><span style="color: #ff0000;">Title</span><span style="color: #ff0000;">]</span>,(<span style="color: #ff00ff;">len</span>(<span style="color: #ff0000;">[</span><span style="color: #ff0000;">Title</span><span style="color: #ff0000;">]</span>)<span style="color: #808080;">-</span><span style="color: #800000; font-weight: bold;">1</span>)) <span style="color: #0000ff;">where</span> Title <span style="color: #808080;">like</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">%村</span><span style="color: #ff0000;">‘</span>

8.假删除表中多余的重复记录（多个字段），不包含rowid最小的记录

 <span style="color: #0000ff;">update</span> vitae <span style="color: #0000ff;">set</span> ispass<span style="color: #808080;">=-</span><span style="color: #800000; font-weight: bold;">1</span> <span style="color: #0000ff;">where</span> peopleId <span style="color: #808080;">in</span> (<span style="color: #0000ff;">select</span> peopleId <span style="color: #0000ff;">from</span> vitae <span style="color: #0000ff;">group</span> <span style="color: #0000ff;">by</span> peopleId,seq <span style="color: #0000ff;">having</span> <span style="color: #ff00ff;">count</span>(<span style="color: #808080;">*</span>) <span style="color: #808080;">></span> <span style="color: #800000; font-weight: bold;">1</span>) <span style="color: #808080;">and</span> seq <span style="color: #808080;">in</span> （<span style="color: #0000ff;">select</span> seq <span style="color: #0000ff;">from</span> vitae <span style="color: #0000ff;">group</span> <span style="color: #0000ff;">by</span> peopleId,seq <span style="color: #0000ff;">having</span> <span style="color: #ff00ff;">count</span>(<span style="color: #808080;">*</span>) <span style="color: #808080;">></span> <span style="color: #800000; font-weight: bold;">1</span>） <span style="color: #808080;">and</span> rowid <span style="color: #808080;">not</span> <span style="color: #808080;">in</span> (<span style="color: #0000ff;">select</span> <span style="color: #ff00ff;">min</span>(rowid) <span style="color: #0000ff;">from</span> vitae <span style="color: #0000ff;">group</span> <span style="color: #0000ff;">by</span> peopleId,seq <span style="color: #0000ff;">having</span> <span style="color: #ff00ff;">count</span>(<span style="color: #808080;">*</span>)<span style="color: #808080;">></span><span style="color: #800000; font-weight: bold;">1</span>)    

本文转自：http://www.cnblogs.com/huangw/archive/2012/06/04/2534676.html

【转】SQL删除重复记录,只保留其中一条

标签：