ORACLE删除部分重复行,sqlserver / oracle 删除重复行的办法

在日常维护 经常会有一些表少某些索引约束导致一些表出现重复行，使后期再转换的时候多了数据，导致数据不对。

今天发个删除完全一样的数据的记录。

sql查询案例:删除2条完全一样的数据2011-03-14 17:10删除2条完全一样的数据

今天百度知道上面，有看到一个帖，说要求：

2条完全一样的数据删除一条保留一条，用SQL语句删除。

SQL Server 的处理办法

(对sql server 2005后有效)

-- 首先创建测试表

CREATE TABLE test_delete(

name  varchar(10),

value INT

);

go

-- 测试数据，其中 张三100 与 王五80 是完全一样的

INSERT INTO test_delete

SELECT '张三',100

UNION ALL SELECT '张三',100

UNION ALL SELECT '李四',80

UNION ALL SELECT '王五',80

UNION ALL SELECT '赵六',90

UNION ALL SELECT '赵六',70

go

-- 首先查询一下， ROW_NUMBER 效果是否满足预期

SELECT

ROW_NUMBER() OVER (PARTITION BY name,value ORDER BY (SELECT 1) ) AS no,

name,

value

FROM

test_delete

no    name       value

----- ---------- -----------

1 李四                  80

1 王五                  80

2 王五                  80

1 张三                 100

2 张三                 100

1 赵六                  70

1 赵六                  90

从结果上可以看到，如果有重复的，完全一样的话， no 是有大于1的。

-- 创建视图

CREATE VIEW tmp_view AS

SELECT

ROW_NUMBER() OVER (PARTITION BY name,

value

FROM

test_delete

-- 删除 视图中的 no 不等于 1 的数据。

1> DELETE FROM tmp_view WHERE no != 1

2> go

(2 行受影响)

1>

2> select * from test_delete;

3> go

name       value

---------- -----------

张三                 100

李四                  80

王五                  80

赵六                  90

赵六                  70

(5 行受影响)

结果看上去是满足预期的。

SQL SERVER 2000

有两个意义上的重复记录，一是完全重复的记录，也即所有字段均重复的记录，二是部分关键字段重复的记录，比如Name字段重复，而其他字段不一定重复或都重复可以忽略。

1、对于第一种重复，比较容易解决，使用

select distinct * from tableName

就可以得到无重复记录的结果集。

如果该表需要删除重复的记录(重复记录保留1条)，可以按以下方法删除

select distinct * into #Tmp from tableName

drop table tableName

select * into tableName from #Tmp

drop table #Tmp

发生这种重复的原因是表设计不周产生的，增加唯一索引列即可解决。

2、这类重复问题通常要求保留重复记录中的第一条记录，操作方法如下

假设有重复的字段为Name,Address，要求得到这两个字段唯一的结果集

select identity(int,1,1) as autoID,* into #Tmp from tableName

select min(autoID) as autoID into #Tmp2 from #Tmp group by Name,autoID

select * from #Tmp where autoID in(select autoID from #tmp2)

最后一个select即得到了Name，Address不重复的结果集(但多了一个autoID字段，实际写时可以写在select子句中省去此列)

Oracle 的处理办法

使用 Oracle 的  ROWID 来删除的处理步骤如下：

SQL> CREATE TABLE test_delete(

2    name  varchar(10),

3    value INT

4  );

表已创建。

SQL> INSERT INTO test_delete

2  SELECT '张三',100 FROM dual

3  UNION ALL SELECT '张三',100 FROM dual

4  UNION ALL SELECT '李四',80  FROM dual

5  UNION ALL SELECT '王五',80  FROM dual

6  UNION ALL SELECT '王五',80  FROM dual

7  UNION ALL SELECT '赵六',90  FROM dual

8  UNION ALL SELECT '赵六',70  FROM dual;

已创建7行。

SQL> SELECT

2    ROWID,

3    name,

4    value

5  FROM

6    test_delete;

ROWID              NAME            VALUE

------------------ ---------- ----------

AAAM2mAAGAAAAOXAAA 张三              100

AAAM2mAAGAAAAOXAAB 张三              100

AAAM2mAAGAAAAOXAAC 李四               80

AAAM2mAAGAAAAOXAAD 王五               80

AAAM2mAAGAAAAOXAAE 王五               80

AAAM2mAAGAAAAOXAAF 赵六               90

AAAM2mAAGAAAAOXAAG 赵六               70

已选择7行。

SQL> DELETE

2    test_delete

3  WHERE

4    (name,value)

5      IN (SELECT

6            name,value

7          FROM

8            test_delete

9          GROUP BY

10            name,value

11          HAVING COUNT(1) > 1)

12      AND rowid NOT IN

13         (SELECT

14            MIN(rowid)

15          FROM

16            test_delete

17          GROUP BY

18            name,value

19          HAVING

20            COUNT(1) > 1);

已删除2行。

SQL> SELECT

2    ROWID,

4    value

5  FROM

6    test_delete;

ROWID              NAME            VALUE ------------------ ---------- ---------- AAAM2mAAGAAAAOXAAA 张三              100 AAAM2mAAGAAAAOXAAC 李四               80 AAAM2mAAGAAAAOXAAD 王五               80 AAAM2mAAGAAAAOXAAF 赵六               90 AAAM2mAAGAAAAOXAAG 赵六               70