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

最新推荐文章于 2022-09-18 11:13:30 发布

原创最新推荐文章于 2022-09-18 11:13:30 发布 · 497 阅读

0 ·

CC 4.0 BY-SA版权

本文介绍如何在DB2、SQL Server及Oracle三种不同的数据库系统中，使用SQL语句删除指定表内的重复记录，确保数据的唯一性和准确性。

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);