ORACLE,SQLSERVER,DB2 删除重复数据SQL

catch不住的爱

于 2012-10-18 18:49:48 发布

阅读量467

点赞数

本文链接：https://blog.csdn.net/javaecrainbow/article/details/8086681

版权

1.创建TUser表：

CREATE TABLE TUser(
  Name varchar(50),
  Region varchar(50),
  Remark varchar(100)
)

2.插入重复数据到TUser表中

INSERT into TUser VALUES ('Tom','Franc', 'test1')
INSERT into TUser VALUES ('Tom','Japan', 'test3')
INSERT into TUser VALUES ('Mary','Japan', 'test4')
INSERT into TUser VALUES ('Tom','German', 'test5')

DB2:

DELETE FROM (SELECT ROW_NUMBER() OVER (partition  BY name ORDER BY name) AS RN
FROM TUSER) AS E  where RN>1

SQLSERVER:

WITH [CTE_DUPLICATE] AS 
(SELECT ROW_NUMBER() OVER (partition  BY name ORDER BY name) AS RN FROM TUser
)
delete from  [CTE_DUPLICATE] where RN>1

ORACLE:

DELETE from TUser where C_STP_CODE IN
(SELECT NAME FROM TUser GROUP BY NAME HAVING COUNT(NAME)>1)
 AND ROWID NOT
IN (SELECT MIN(ROWID) FROM TUser GROUP BY NAME HAVING COUNT(NAME) >1);