频道导航

删除Table表中的重复行的方法

2019-01-04 MsSQL 前端之家

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

利用sql Server 2005的新功能NOWNUMBER和CTE可以很好的实现. @H5020@举例说明如下: @H5020@建立测试数据: @H502_0@<div class="codetitle"><a style="CURSOR: pointer" data="42033" class="copybut" id="copybut42033" onclick="doCopy('code42033')"> 代码如下:

<div class="codebody" id="code42033"> @H_5020@create table Dup1 @H5020@( @H5020@Col1 int null,@H5020@Col2 varchar(20) null @H5020@) @H5020@insert into Dup1 values @H5020@(1,'aaa'),@H5020@(2,@H5020@(3,'bbb'),@H5020@(4,'ccc'),'ddd'),@H5020@(5,'eee') @H5020@select * from Dup1 @H5020@

@H5020@可以查看到重复的数据有: @H502_0@<div class="codetitle"><a style="CURSOR: pointer" data="33747" class="copybut" id="copybut33747" onclick="doCopy('code33747')"> 代码如下:

<div class="codebody" id="code33747"> @H_5020@SELECT Col1,Col2,COUNT() AS DupCountFROM Dup1GROUP BY Col1,Col2HAVING COUNT() > 1 @H5020@

@H5020@<IMG src="http:https://files.jb51.cc/upload/201006/20100608002211701.jpg">@H5020@

接下来介绍如何delete掉重复的数据: @H502_0@

1.NOW_NUMBER:sql Server 2005添加了很好用的RANKING函数(NOW_NUMBER,RANK,DENSE_RANK,NTILE),利用NOW_NUMBER()OVER(PARTITION GY)最为直接,也最为方便,不能修改表或者产生多余的列. @H_5020@首先会分配一个列号码,以Col1,Col2组合来分区排序. @H502_0@<div class="codetitle"><a style="CURSOR: pointer" data="11720" class="copybut" id="copybut11720" onclick="doCopy('code11720')"> 代码如下:

<div class="codebody" id="code11720"> @H_502_0@SELECT Col1,ROWNUMBER() OVER (PARTITION BY Col1,Col2 ORDER BY Col1) AS rnFROM Dup1 @H5020@

得到的序号如下: @H5020@<IMG src="http:https://files.jb51.cc/upload/201006/20100608002211200.jpg">@H5020@很明显的是重复列都分组分割排序,只需要delete掉排序序号>1的即可. @H502_0@<div class="codetitle"><a style="CURSOR: pointer" data="51241" class="copybut" id="copybut51241" onclick="doCopy('code51241')"> 代码如下:

<div class="codebody" id="code51241"> @H_5020@--用到CTE @H5020@WITH DupsD @H5020@AS ( @H5020@SELECT Col1,@H502_0@ROWNUMBER() OVER (PARTITION BY Col1,Col2 ORDER BY Col1) AS rn @H5020@FROM Dup1 @H5020@) @H5020@DELETE DupsD @H5020@WHERE rn > 1; @H5020@--或者 @H5020@DELETE A FROM ( @H5020@SELECT Col1,Col2 ORDER BY Col1) AS rn @H5020@FROM Dup1) A WHERE A.rn>1 @H5020@ @H5020@2.创建一个标识键唯一的表记一列. @H502_0@<div class="codetitle"><a style="CURSOR: pointer" data="32678" class="copybut" id="copybut32678" onclick="doCopy('code32678')"> 代码如下:<div class="codebody" id="code32678"> @H_5020@ALTER TABLE dbo.Dup1 @H5020@ADD @H5020@PK INT IDENTITY @H5020@NOT NULL @H502_0@CONSTRAINT PKDup1 PRIMARY KEY; @H5020@SELECT * @H5020@FROM Dup1; @H5020@ @H502_0@删除找出与Col1,Col2相同并且比Dup1.PK大的记录,也就是保留重复值中PK最小的记录. @H_502_0@<div class="codetitle"><a style="CURSOR: pointer" data="14925" class="copybut" id="copybut14925" onclick="doCopy('code14925')"> 代码如下:<div class="codebody" id="code14925"> @H_5020@DELETE Dup1 @H5020@WHERE EXISTS ( SELECT * @H5020@FROM Dup1 AS D1 @H5020@WHERE D1.Col1 = Dup1.Col1 @H5020@AND D1.Col2 = Dup1.Col2 @H5020@AND D1.PK > Dup1.PK ); @H5020@ @H502_0@3.select distant into,这种方法借助一个新的table,把不重复的结果集转移到新table中. @H_502_0@<div class="codetitle"><a style="CURSOR: pointer" data="41628" class="copybut" id="copybut41628" onclick="doCopy('code41628')"> 代码如下:<div class="codebody" id="code41628"> @H_5020@SELECT distinct Col1,Col2 INTO NoDupsFROM Dup1;select * from NoDups @H5020@ @H502_0@建议采用第一种和第三种方法,第一种多见于T-sql的编程中,第三种在ETL中常常使用.

重复行