邪恶八进制信息安全团队技术讨论组's Archiver

ring04h 2006-6-27 18:00

[转载]删除数据库中重复数据的几个方法

<p>信息来源: 邪恶八进制信息安全团队</p><p>数据库的使用过程中由于程序方面的问题有时候会碰到重复数据，重复数据导致了数据库部分设置不能正确设置……</p><p /><p /><div id="3">　　方法一</div><p /><div style="SCROLLBAR-HIGHLIGHT-COLOR: buttonhighlight; OVERFLOW: auto; WIDTH: 500px"><pre style="BORDER-RIGHT: black 1px solid; PADDING-RIGHT: 4px; BORDER-TOP: black 1px solid; PADDING-LEFT: 4px; PADDING-BOTTOM: 4px; BORDER-LEFT: black 1px solid; PADDING-TOP: 4px; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ededed"><div id="4">
<div><!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
[url]http://www.CodeHighlighter.com/[/url]
--><img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /><span style="COLOR: #000000">　　</span><span style="COLOR: #0000ff">declare</span><span style="COLOR: #000000"></span><span style="COLOR: #008000">@max</span><span style="COLOR: #000000"></span><span style="FONT-WEIGHT: bold; COLOR: #000000">integer</span><span style="COLOR: #000000">,</span><span style="COLOR: #008000">@id</span><span style="COLOR: #000000"></span><span style="FONT-WEIGHT: bold; COLOR: #000000">integer</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />　　</span><span style="COLOR: #0000ff">declare</span><span style="COLOR: #000000"> cur_rows </span><span style="COLOR: #0000ff">cursor</span><span style="COLOR: #000000"> local </span><span style="COLOR: #0000ff">for</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">select</span><span style="COLOR: #000000"> 主字段,</span><span style="COLOR: #ff00ff">count</span><span style="COLOR: #000000">(</span><span style="COLOR: #808080">*</span><span style="COLOR: #000000">) </span><span style="COLOR: #0000ff">from</span><span style="COLOR: #000000"> 表名 </span><span style="COLOR: #0000ff">group</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">by</span><span style="COLOR: #000000"> 主字段 </span><span style="COLOR: #0000ff">having</span><span style="COLOR: #000000"></span><span style="COLOR: #ff00ff">count</span><span style="COLOR: #000000">(</span><span style="COLOR: #808080">*</span><span style="COLOR: #000000">) </span><span style="COLOR: #808080">></span><span style="COLOR: #000000"></span><span style="FONT-WEIGHT: bold; COLOR: #800000">1</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />　　</span><span style="COLOR: #0000ff">open</span><span style="COLOR: #000000"> cur_rows
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />　　</span><span style="COLOR: #0000ff">fetch</span><span style="COLOR: #000000"> cur_rows </span><span style="COLOR: #0000ff">into</span><span style="COLOR: #000000"></span><span style="COLOR: #008000">@id</span><span style="COLOR: #000000">,</span><span style="COLOR: #008000">@max</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />　　</span><span style="COLOR: #0000ff">while</span><span style="COLOR: #000000"></span><span style="FONT-WEIGHT: bold; COLOR: #008000">@@fetch_status</span><span style="COLOR: #808080">=</span><span style="FONT-WEIGHT: bold; COLOR: #800000">0</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />　　</span><span style="COLOR: #0000ff">begin</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />　　</span><span style="COLOR: #0000ff">select</span><span style="COLOR: #000000"></span><span style="COLOR: #008000">@max</span><span style="COLOR: #000000"></span><span style="COLOR: #808080">=</span><span style="COLOR: #000000"></span><span style="COLOR: #008000">@max</span><span style="COLOR: #000000"></span><span style="COLOR: #808080">-</span><span style="FONT-WEIGHT: bold; COLOR: #800000">1</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />　　</span><span style="COLOR: #0000ff">set</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">rowcount</span><span style="COLOR: #000000"></span><span style="COLOR: #008000">@max</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />　　</span><span style="COLOR: #0000ff">delete</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">from</span><span style="COLOR: #000000"> 表名 </span><span style="COLOR: #0000ff">where</span><span style="COLOR: #000000"> 主字段 </span><span style="COLOR: #808080">=</span><span style="COLOR: #000000"></span><span style="COLOR: #008000">@id</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />　　</span><span style="COLOR: #0000ff">fetch</span><span style="COLOR: #000000"> cur_rows </span><span style="COLOR: #0000ff">into</span><span style="COLOR: #000000"></span><span style="COLOR: #008000">@id</span><span style="COLOR: #000000">,</span><span style="COLOR: #008000">@max</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />　　</span><span style="COLOR: #0000ff">end</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />　　</span><span style="COLOR: #0000ff">close</span><span style="COLOR: #000000"> cur_rows
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />　　</span><span style="COLOR: #0000ff">set</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">rowcount</span><span style="COLOR: #000000"></span><span style="FONT-WEIGHT: bold; COLOR: #800000">0</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span></div>
</div></pre></div><p /><p>方法二</p><p /><p /><div id="2">　　有两个意义上的重复记录，一是完全重复的记录，也即所有字段均重复的记录，二是部分关键字段重复的记录，比如Name字段重复，而其他字段不一定重复或都重复可以忽略。</div><p /><p /><div id="3">　　1、对于第一种重复，比较容易解决，使用</div><p /><p /><div id="4">　　select distinct * from tableName</div><p /><p /><div id="5">　　就可以得到无重复记录的结果集。</div><p /><p /><div id="6">　　如果该表需要删除重复的记录(重复记录保留1条)，可以按以下方法删除</div><p /><div style="SCROLLBAR-HIGHLIGHT-COLOR: buttonhighlight; OVERFLOW: auto; WIDTH: 500px"><pre style="BORDER-RIGHT: black 1px solid; PADDING-RIGHT: 4px; BORDER-TOP: black 1px solid; PADDING-LEFT: 4px; PADDING-BOTTOM: 4px; BORDER-LEFT: black 1px solid; PADDING-TOP: 4px; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ededed"><div id="7">
<div><!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
[url]http://www.CodeHighlighter.com/[/url]
--><img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /><span style="COLOR: #000000">　　</span><span style="COLOR: #0000ff">select</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">distinct</span><span style="COLOR: #000000"></span><span style="COLOR: #808080">*</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">into</span><span style="COLOR: #000000"> #Tmp </span><span style="COLOR: #0000ff">from</span><span style="COLOR: #000000"> tableName
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />　　</span><span style="COLOR: #0000ff">drop</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">table</span><span style="COLOR: #000000"> tableName
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />　　</span><span style="COLOR: #0000ff">select</span><span style="COLOR: #000000"></span><span style="COLOR: #808080">*</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">into</span><span style="COLOR: #000000"> tableName </span><span style="COLOR: #0000ff">from</span><span style="COLOR: #000000"> #Tmp
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />　　</span><span style="COLOR: #0000ff">drop</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">table</span><span style="COLOR: #000000"> #Tmp
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span></div>
</div></pre></div><p /><div id="8">　　发生这种重复的原因是表设计不周产生的，增加唯一索引列即可解决。</div><p /><p /><div id="9">　　2、这类重复问题通常要求保留重复记录中的第一条记录，操作方法如下</div><p /><p /><div id="10">　　假设有重复的字段为Name,Address，要求得到这两个字段唯一的结果集</div><p /><div style="SCROLLBAR-HIGHLIGHT-COLOR: buttonhighlight; OVERFLOW: auto; WIDTH: 500px"><pre style="BORDER-RIGHT: black 1px solid; PADDING-RIGHT: 4px; BORDER-TOP: black 1px solid; PADDING-LEFT: 4px; PADDING-BOTTOM: 4px; BORDER-LEFT: black 1px solid; PADDING-TOP: 4px; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ededed"><div id="11">
<div><!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
[url]http://www.CodeHighlighter.com/[/url]
--><img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /><span style="COLOR: #000000">　　</span><span style="COLOR: #0000ff">select</span><span style="COLOR: #000000"></span><span style="COLOR: #ff00ff">identity</span><span style="COLOR: #000000">(</span><span style="FONT-WEIGHT: bold; COLOR: #000000">int</span><span style="COLOR: #000000">,</span><span style="FONT-WEIGHT: bold; COLOR: #800000">1</span><span style="COLOR: #000000">,</span><span style="FONT-WEIGHT: bold; COLOR: #800000">1</span><span style="COLOR: #000000">) </span><span style="COLOR: #0000ff">as</span><span style="COLOR: #000000"> autoID, </span><span style="COLOR: #808080">*</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">into</span><span style="COLOR: #000000"> #Tmp </span><span style="COLOR: #0000ff">from</span><span style="COLOR: #000000"> tableName
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />　　</span><span style="COLOR: #0000ff">select</span><span style="COLOR: #000000"></span><span style="COLOR: #ff00ff">min</span><span style="COLOR: #000000">(autoID) </span><span style="COLOR: #0000ff">as</span><span style="COLOR: #000000"> autoID </span><span style="COLOR: #0000ff">into</span><span style="COLOR: #000000"> #Tmp2 </span><span style="COLOR: #0000ff">from</span><span style="COLOR: #000000"> #Tmp </span><span style="COLOR: #0000ff">group</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">by</span><span style="COLOR: #000000"> Name,autoID
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />　　</span><span style="COLOR: #0000ff">select</span><span style="COLOR: #000000"></span><span style="COLOR: #808080">*</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">from</span><span style="COLOR: #000000"> #Tmp </span><span style="COLOR: #0000ff">where</span><span style="COLOR: #000000"> autoID </span><span style="COLOR: #808080">in</span><span style="COLOR: #000000">(</span><span style="COLOR: #0000ff">select</span><span style="COLOR: #000000"> autoID </span><span style="COLOR: #0000ff">from</span><span style="COLOR: #000000"> #tmp2)
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span></div>
</div></pre></div><p /><div id="12">　　最后一个select即得到了Name，Address不重复的结果集(但多了一个autoID字段，实际写时可以写在select子句中省去此列)</div>

页: [1]
© 1999-2008 EvilOctal Security Team