转 sql删除重复记录

sqlserver 删除 重复 记录 处理（转）发布：mdxy - dxy 字体： [ 增加 减小 ] 类型：转载 删除 重复 记录 有大小关系时,保留大或小其中一个 记录 注：此处 重复 非完全 重复 ,意为某字段数据 重复 HZT表结构 ID int Title nvarchar ( 50 ) AddDate datetime

<span>sqlserver  <strong>删除</strong><strong>重复</strong><strong>记录</strong>处理（转）
发布：mdxy</span><span>-</span>dxy 字体：<span>[</span><span>增加 减小 </span><span>]</span><span> 类型：转载
<strong>删除</strong><strong>重复</strong><strong>记录</strong>有大小关系时,保留大或小其中一个<strong>记录</strong>
注：此处“<strong>重复</strong>”非完全<strong>重复</strong>,意为某字段数据<strong>重复</strong> 
HZT表结构 
ID　</span><span>int</span><span> 
Title　</span><span>nvarchar</span>(<span>50</span><span>) 
AddDate　</span><span>datetime</span><span> 
数据 
一. 查找<strong>重复</strong><strong>记录</strong> 
</span><span>1</span><span>. 查找全部<strong>重复</strong><strong>记录</strong> 
</span><span>Select</span> <span>*</span> <span>From</span> 表 <span>Where</span> <strong>重复</strong>字段 <span>In</span> (<span>Select</span> <strong>重复</strong>字段 <span>From</span> 表 <span>Group</span> <span>By</span> <strong>重复</strong>字段 <span>Having</span> <span>Count</span>(<span>*</span>)<span>></span><span>1</span><span>) 

</span><span>2</span><span>. 过滤<strong>重复</strong><strong>记录</strong>(只显示一条) 
</span><span>Select</span> <span>*</span> <span>From</span> HZT <span>Where</span> ID <span>In</span> (<span>Select</span> <span>Max</span>(ID) <span>From</span> HZT <span>Group</span> <span>By</span><span> Title) 
注：此处显示ID最大一条<strong>记录</strong> 
二. <strong>删除</strong><strong>重复</strong><strong>记录</strong> 
　</span><span>1</span><span>. <strong>删除</strong>全部<strong>重复</strong><strong>记录</strong>（慎用） 
</span><span>Delete</span> 表 <span>Where</span> <strong>重复</strong>字段 <span>In</span> (<span>Select</span> <strong>重复</strong>字段 <span>From</span> 表 <span>Group</span> <span>By</span> <strong>重复</strong>字段 <span>Having</span> <span>Count</span>(<span>*</span>)<span>></span><span>1</span><span>) 

</span><span>2</span><span>. 保留一条（这个应该是大多数人所需要的） 
</span><span>Delete</span> HZT <span>Where</span> ID <span>Not</span> <span>In</span> (<span>Select</span> <span>Max</span>(ID) <span>From</span> HZT <span>Group</span> <span>By</span><span> Title) 
注：此处保留ID最大一条<strong>记录</strong> 

其它相关:
<strong>删除</strong><strong>重复</strong><strong>记录</strong>有大小关系时,保留大或小其中一个<strong>记录</strong>

</span><span>--</span><span>> --> (Roy)生成測試數據</span>

<span>if</span> <span>not</span> <span>object_id</span>(<span>'</span><span>Tempdb..#T</span><span>'</span>) <span>is</span> <span>null</span>
  <span>drop</span> <span>table</span><span> #T
</span><span>Go</span>
<span>Create</span> <span>table</span> #T(<span>[</span><span>ID</span><span>]</span> <span>int</span>,<span>[</span><span>Name</span><span>]</span> <span>nvarchar</span>(<span>1</span>),<span>[</span><span>Memo</span><span>]</span> <span>nvarchar</span>(<span>2</span><span>))
</span><span>Insert</span><span> #T
</span><span>select</span> <span>1</span>,N<span>'</span><span>A</span><span>'</span>,N<span>'</span><span>A1</span><span>'</span> <span>union</span> <span>all</span>
<span>select</span> <span>2</span>,N<span>'</span><span>A</span><span>'</span>,N<span>'</span><span>A2</span><span>'</span> <span>union</span> <span>all</span>
<span>select</span> <span>3</span>,N<span>'</span><span>A</span><span>'</span>,N<span>'</span><span>A3</span><span>'</span> <span>union</span> <span>all</span>
<span>select</span> <span>4</span>,N<span>'</span><span>B</span><span>'</span>,N<span>'</span><span>B1</span><span>'</span> <span>union</span> <span>all</span>
<span>select</span> <span>5</span>,N<span>'</span><span>B</span><span>'</span>,N<span>'</span><span>B2</span><span>'</span>
<span>Go</span>

<span>--</span><span>I、Name相同ID最小的<strong>记录</strong>(推荐用1,2,3),保留最小一条</span>
<span>方法1:
</span><span>delete</span> a <span>from</span> #T a <span>where</span> <span>exists</span>(<span>select</span> <span>1</span> <span>from</span> #T <span>where</span> Name<span>=</span>a.Name <span>and</span> ID<span><span>a.ID)
方法2:
</span><span>delete</span> a <span>from</span> #T a <span>left</span> <span>join</span> (<span>select</span> <span>min</span>(ID)ID,Name <span>from</span> #T <span>group</span> <span>by</span> Name) b <span>on</span> a.Name<span>=</span>b.Name <span>and</span> a.ID<span>=</span><span>b.ID 
</span><span>where</span> b.Id <span>is</span> <span>null</span><span>
方法3:
</span><span>delete</span> a <span>from</span> #T a <span>where</span> ID <span>not</span> <span>in</span> (<span>select</span> <span>min</span>(ID) <span>from</span> #T <span>where</span> Name<span>=</span><span>a.Name)
方法4(注:ID为唯一时可用):
</span><span>delete</span> a <span>from</span> #T a <span>where</span> ID <span>not</span> <span>in</span>(<span>select</span> <span>min</span>(ID)<span>from</span> #T <span>group</span> <span>by</span><span> Name)
方法5:
</span><span>delete</span> a <span>from</span> #T a <span>where</span> (<span>select</span> <span>count</span>(<span>1</span>) <span>from</span> #T <span>where</span> Name<span>=</span>a.Name <span>and</span> ID<span>a.ID)<span>></span><span>0</span><span>
方法6:
</span><span>delete</span> a <span>from</span> #T a <span>where</span> ID<span></span>(<span>select</span> <span>top</span> <span>1</span> ID <span>from</span> #T <span>where</span> Name<span>=</span>a.name <span>order</span> <span>by</span><span> ID)
方法7:
</span><span>delete</span> a <span>from</span> #T a <span>where</span> ID<span>>any</span>(<span>select</span> ID <span>from</span> #T <span>where</span> Name<span>=</span><span>a.Name)
</span><span>select</span> <span>*</span> <span>from</span><span> #T

生成结果:
</span><span>/*</span><span>
ID     Name Memo
----------- ---- ----
1      A  A1
4      B  B1

(2 行受影响)
</span><span>*/</span>


<span>--</span><span>II、Name相同ID保留最大的一条<strong>记录</strong>:</span>
<span>方法1:
</span><span>delete</span> a <span>from</span> #T a <span>where</span> <span>exists</span>(<span>select</span> <span>1</span> <span>from</span> #T <span>where</span> Name<span>=</span>a.Name <span>and</span> ID<span>></span><span>a.ID)
方法2:
</span><span>delete</span> a <span>from</span> #T a <span>left</span> <span>join</span> (<span>select</span> <span>max</span>(ID)ID,Name <span>from</span> #T <span>group</span> <span>by</span> Name) b <span>on</span> a.Name<span>=</span>b.Name <span>and</span> a.ID<span>=</span><span>b.ID
</span><span>where</span> b.Id <span>is</span> <span>null</span><span>
方法3:
</span><span>delete</span> a <span>from</span> #T a <span>where</span> ID <span>not</span> <span>in</span> (<span>select</span> <span>max</span>(ID) <span>from</span> #T <span>where</span> Name<span>=</span><span>a.Name)
方法4(注:ID为唯一时可用):
</span><span>delete</span> a <span>from</span> #T a <span>where</span> ID <span>not</span> <span>in</span>(<span>select</span> <span>max</span>(ID)<span>from</span> #T <span>group</span> <span>by</span><span> Name)
方法5:
</span><span>delete</span> a <span>from</span> #T a <span>where</span> (<span>select</span> <span>count</span>(<span>1</span>) <span>from</span> #T <span>where</span> Name<span>=</span>a.Name <span>and</span> ID<span>></span>a.ID)<span>></span><span>0</span><span>
方法6:
</span><span>delete</span> a <span>from</span> #T a <span>where</span> ID<span></span>(<span>select</span> <span>top</span> <span>1</span> ID <span>from</span> #T <span>where</span> Name<span>=</span>a.name <span>order</span> <span>by</span> ID <span>desc</span><span>)
方法7:
</span><span>delete</span> a <span>from</span> #T a <span>where</span> ID<span><any>(<span>select</span> ID <span>from</span> #T <span>where</span> Name<span>=</span><span>a.Name)
</span><span>select</span> <span>*</span> <span>from</span><span> #T
</span><span>/*</span><span>
ID     Name Memo
----------- ---- ----
3      A  A3
5      B  B2

(2 行受影响)
</span><span>*/</span>


<span>--</span><span>3、<strong>删除</strong><strong>重复</strong><strong>记录</strong>没有大小关系时，处理<strong>重复</strong>值</span><span>
--</span><span>> --> (Roy)生成測試數據</span>

<span>if</span> <span>not</span> <span>object_id</span>(<span>'</span><span>Tempdb..#T</span><span>'</span>) <span>is</span> <span>null</span>
  <span>drop</span> <span>table</span><span> #T
</span><span>Go</span>
<span>Create</span> <span>table</span> #T(<span>[</span><span>Num</span><span>]</span> <span>int</span>,<span>[</span><span>Name</span><span>]</span> <span>nvarchar</span>(<span>1</span><span>))
</span><span>Insert</span><span> #T
</span><span>select</span> <span>1</span>,N<span>'</span><span>A</span><span>'</span> <span>union</span> <span>all</span>
<span>select</span> <span>1</span>,N<span>'</span><span>A</span><span>'</span> <span>union</span> <span>all</span>
<span>select</span> <span>1</span>,N<span>'</span><span>A</span><span>'</span> <span>union</span> <span>all</span>
<span>select</span> <span>2</span>,N<span>'</span><span>B</span><span>'</span> <span>union</span> <span>all</span>
<span>select</span> <span>2</span>,N<span>'</span><span>B</span><span>'</span>
<span>Go</span><span>

方法1:
</span><span>if</span> <span>object_id</span>(<span>'</span><span>Tempdb..#</span><span>'</span>) <span>is</span> <span>not</span> <span>null</span>
  <span>drop</span> <span>table</span><span> #
</span><span>Select</span> <span>distinct</span> <span>*</span> <span>into</span> # <span>from</span> #T<span>--</span><span>排除<strong>重复</strong><strong>记录</strong>结果集生成临时表#</span>
<span>truncate</span> <span>table</span> #T<span>--</span><span>清空表</span>
<span>insert</span> #T <span>select</span> <span>*</span> <span>from</span> #  <span>--</span><span>把临时表#插入到表#T中</span>

<span>--</span><span>查看结果</span>
<span>select</span> <span>*</span> <span>from</span><span> #T

</span><span>/*</span><span>
Num     Name
----------- ----
1      A
2      B

(2 行受影响)
</span><span>*/</span>

<span>--</span><span>重新执行测试数据后用方法2</span>
<span>方法2:
</span><span>alter</span> <span>table</span> #T <span>add</span> ID <span>int</span> <span>identity</span><span>--</span><span>新增标识列</span>
<span>go</span>
<span>delete</span> a <span>from</span> #T a <span>where</span> <span>exists</span>(<span>select</span> <span>1</span> <span>from</span> #T <span>where</span> Num<span>=</span>a.Num <span>and</span> Name<span>=</span>a.Name <span>and</span> ID<span>></span>a.ID)<span>--</span><span>只保留一条<strong>记录</strong></span>
<span>go</span>
<span>alter</span> <span>table</span> #T <span>drop</span> <span>column</span> ID<span>--</span><span><strong>删除</strong>标识列</span>

<span>--</span><span>查看结果</span>
<span>select</span> <span>*</span> <span>from</span><span> #T

</span><span>/*</span><span>
Num     Name
----------- ----
1      A
2      B

(2 行受影响)

</span><span>*/</span>

<span>--</span><span>重新执行测试数据后用方法3</span>
<span>方法3:
</span><span>declare</span> Roy_Cursor <span>cursor</span> local <span>for</span>
<span>select</span> <span>count</span>(<span>1</span>)<span>-</span><span>1</span>,Num,Name <span>from</span> #T <span>group</span> <span>by</span> Num,Name <span>having</span> <span>count</span>(<span>1</span>)<span>></span><span>1</span>
<span>declare</span> <span>@con</span> <span>int</span>,<span>@Num</span> <span>int</span>,<span>@Name</span> <span>nvarchar</span>(<span>1</span><span>)
</span><span>open</span><span> Roy_Cursor
</span><span>fetch</span> <span>next</span> <span>from</span> Roy_Cursor <span>into</span> <span>@con</span>,<span>@Num</span>,<span>@Name</span>
<span>while</span> <span>@@Fetch_status</span><span>=</span><span>0</span>
<span>begin</span> 
  <span>set</span> <span>rowcount</span> <span>@con</span><span>;
  </span><span>delete</span> #T <span>where</span> Num<span>=</span><span>@Num</span> <span>and</span> Name<span>=</span><span>@Name</span>
  <span>set</span> <span>rowcount</span> <span>0</span><span>;
  </span><span>fetch</span> <span>next</span> <span>from</span> Roy_Cursor <span>into</span> <span>@con</span>,<span>@Num</span>,<span>@Name</span>
<span>end</span>
<span>close</span><span> Roy_Cursor
</span><span>deallocate</span><span> Roy_Cursor

</span><span>--</span><span>查看结果</span>
<span>select</span> <span>*</span> <span>from</span><span> #T
</span><span>/*</span><span>
Num     Name
----------- ----
1      A
2      B

(2 行受影响)
</span><span>*/</span>
 </any></span></span></span>