转 sql删除重复记录-mysql教程-PHP中文网

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>
注：此处&ldquo;<strong>重复</strong>&rdquo;非完全<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><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 ><</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>(<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>
登录后复制
mybatis语法和介绍中文WORD版
本文档主要讲述的是mybatis语法和介绍；MyBatis 是一个可以自定义SQL、存储过程和高级映射的持久层框架。MyBatis 摒除了大部分的JDBC代码、手工设置参数和结果集重获。MyBatis 只使用简单的XML 和注解来配置和映射基本数据类型、Map 接口和POJO 到数据库记录。相对Hibernate和Apache OJB等“一站式”ORM解决方案而言，Mybatis 是一种“半自动化”的ORM实现。感兴趣的朋友可