数据库去重有几种方法_去重数据库(常用的数据网站汇总)
目錄
概述
一、Oracle數據庫去重（推薦放在在第6點）
二、MySQL數據庫去重
三、sqlserver數據庫去重
四、pg數據庫刪除重復數據
概述
轉發這個主要是有時會有重復數據的需求，留一個查詢方法，大家有空也可以測試一下..
一、Oracle數據庫去重（推薦放在在第6點）
1、環境準備
可以看到“ALLEN”和“SMITH”這兩個人的數據重復了，現在要求表中name重復的數據只保留一行，其他的刪除。
CREATE TABLE hwb(
id int,
name varchar(10)
);
INSERT INTO hwb VALUES(1, 'TOM');
INSERT INTO hwb VALUES(2, 'ALLEN');
INSERT INTO hwb VALUES(3, 'ALLEN');
INSERT INTO hwb VALUES(4, 'SMITH');
INSERT INTO hwb VALUES(5, 'SMITH');
INSERT INTO hwb VALUES(6, 'SMITH');
commit;
SELECT * FROM hwb;2、實現方法一：通過分組后，拿最小的id重復刪除
delete from hwb a where a.id in
(select min(id) FROM hwb a group by name having count(name) > 1)3、實現方法二：通過name相同，id不同的方式來判斷
DELETE FROM hwb a
WHERE EXISTS (SELECT 1
FROM hwb b
WHERE a.name = b.name
AND a.id > b.id);4、實現方法三：用rowid代替id，替換刪除
DELETE FROM hwb a
WHERE EXISTS (SELECT 1
FROM hwb b
WHERE a.name = b.name
AND a.rowid > b.rowid);5、實現方法四：通過分析函數根據name分組生成序號，然后刪除序號大于1的數據。
語法格式：row_number() over(partition by 分組列 order by 排序列 desc)
在使用 row_number() over()函數時候，over()里頭的分組以及排序的執行晚于 where 、group by、 order by 的執行。
DELETE FROM hwb a
WHERE ROWID IN (SELECT rid
FROM (SELECT ROWID as rid,
ROW_NUMBER() OVER(PARTITION BY name ORDER BY id) AS seq
FROM hwb)
WHERE seq > 1);6、【推薦】實現方法五：根據rowid刪除
DELETE FROM hwb a
WHERE a.ROWID > (SELECT MIN(b.ROWID) FROM hwb b WHERE a.name = b.name);二、MySQL數據庫去重
1、環境準備
CREATE TABLE hwb(
id BIGINT (4) not null,
name varchar(10),
PRIMARY KEY (id)
) ENGINE = INNODB;
INSERT INTO hwb VALUES(1, 'TOM');
INSERT INTO hwb VALUES(2, 'ALLEN');
INSERT INTO hwb VALUES(3, 'ALLEN');
INSERT INTO hwb VALUES(4, 'SMITH');
INSERT INTO hwb VALUES(5, 'SMITH');
INSERT INTO hwb VALUES(6, 'SMITH');
commit;
SELECT * FROM hwb;2、實現方法一：通過分組后，拿最大/最小的id重復刪除
--重復刪除多次
delete from hwb
where id in (select id from
(select max(b.id) as id FROM hwb b group by name having count(name) > 1)c )
--或者修改為以下，只需刪除一次
delete from hwb
WHERE
NAME IN (select name from ( SELECT NAME FROM hwb b GROUP BY NAME HAVING count( NAME ) > 1 ) t)
AND id NOT IN (select tt.id from (SELECT max(c.id ) as id FROM hwb c GROUP BY NAME HAVING count( NAME ) > 1 ) tt)3、實現方法二：
根據name分組，查找出id最小的,然后再查找id不包含剛才查出來的。這樣就查詢出了所有的重復數據（除了id最小的那行）
delete from hwb
WHERE
id NOT IN (select tt.id from (SELECT min(c.id ) as id FROM hwb c GROUP BY NAME ) tt)4、實現方法三：通過name相同，id不同的方式來判斷
delete from hwb where id in (
select id from (
select * from hwb a
WHERE EXISTS (SELECT 1
FROM hwb b
WHERE a.name = b.name
AND a.id > b.id))c );5、實現方法四：用rowid代替id，替換刪除
在 Oracle 數據庫的表中的每一行數據都有一個唯一的標識符，稱為 rowid ，在 Oracle 內部通常就是使用它來訪問數據的。
而在 MySQL 中也有一個類似的隱藏列 _rowid 來標記唯一的標識。但是需要注意 _rowid 并不是一個真實存在的列，其本質是一個 非空唯一列 的別名。
在某些情況下 _rowid 是不存在的，其只存在于以下情況：
1)當表中存在一個 數字類型 的單列主鍵時， _rowid 其實就是指的是這個主鍵列
2)當表中 不存在主鍵 但存在一個 數字類型 的 非空唯一列 時， _rowid 其實就是指的是對應 非空唯一列 。
需要注意以下情況是不存在 _rowid 的
1)主鍵列 或者 非空唯一列 的類型不是 數字類型
2)主鍵 是聯合主鍵
3)唯一 列不是非空的。
delete from hwb where id in (
select id from (
select * from hwb a
WHERE EXISTS (SELECT 1
FROM hwb b
WHERE a.name = b.name
AND a._rowid > b._rowid))c );ps:還可考慮表切換完成去重步驟。
三、sqlserver數據庫去重
1、環境準備
CREATE TABLE [dbo].[hwb] (
[id] decimal(12) NULL,
[name] nvarchar(10) COLLATE Chinese_PRC_CI_AS NOT NULL
)
GO
ALTER TABLE [dbo].[hwb] SET (LOCK_ESCALATION = TABLE)
GO
INSERT INTO hwb VALUES(1, 'TOM');
INSERT INTO hwb VALUES(2, 'ALLEN');
INSERT INTO hwb VALUES(3, 'ALLEN');
INSERT INTO hwb VALUES(4, 'SMITH');
INSERT INTO hwb VALUES(5, 'SMITH');
INSERT INTO hwb VALUES(6, 'SMITH');
GO2、實現方法一：通過分組后，拿最大/最小的id重復刪除
--重復刪除多次
delete from hwb
where id in
(select min(id) FROM hwb a group by name having count(name) > 1)
--或者修改為以下，只需刪除一次
select * from hwb
-- delete from hwb
WHERE
NAME IN (SELECT NAME FROM hwb b GROUP BY NAME HAVING count( NAME ) > 1 )
AND id NOT IN (SELECT max(c.id ) as id FROM hwb c GROUP BY NAME HAVING count( NAME ) > 1 )3、實現方法二：根據name分組，查找出id最小的,然后再查找id不包含剛才查出來的
這樣就查詢出了所有的重復數據（除了id最小的那行）
delete from hwb
WHERE
id NOT IN (select tt.id from (SELECT min(c.id ) as id FROM hwb c GROUP BY NAME ) tt)4、實現方法三：通過name相同，id不同的方式來判斷
DELETE FROM hwb a
WHERE EXISTS (SELECT 1
FROM hwb b
WHERE a.name = b.name
AND a.id > b.id);5、實現方法四：用hash值代替id，替換刪除
%%lockres%%會返回聚集索引鍵的hash值，
非聚集索引，%%lockres%%會返回非聚集索引鍵的hash值
delete from hwb a
WHERE EXISTS (SELECT 1
FROM hwb b
WHERE a.name = b.name
AND a.%%lockres%% > b.%%lockres%%);6、實現方法五：通過分析函數根據name分組生成序號，然后刪除序號大于1的數據。
語法格式：row_number() over(partition by 分組列 order by 排序列 desc)
在使用 row_number() over()函數時候，over()里頭的分組以及排序的執行晚于 where 、group by、 order by 的執行。
DELETE FROM hwb a
WHERE
id IN (
SELECT b.id
FROM (SELECT id, ROW_NUMBER () OVER ( PARTITION BY name ORDER BY id ) AS seq FROM hwb) b
WHERE b.seq > 1 );7、【推薦】實現方法六：根據hash值刪除
DELETE FROM hwb a
WHERE a.%%lockres%% > (SELECT MIN(b.%%lockres%%) FROM hwb b WHERE a.name = b.name);四、pg數據庫刪除重復數據
1、環境準備
set search_path to 'public';
\dt --list tables
CREATE TABLE hwb(
id INT PRIMARY KEY NOT NULL,
name CHAR(10));
INSERT INTO hwb VALUES(1, 'TOM');
INSERT INTO hwb VALUES(2, 'ALLEN');
INSERT INTO hwb VALUES(3, 'ALLEN');
INSERT INTO hwb VALUES(4, 'SMITH');
INSERT INTO hwb VALUES(5, 'SMITH');
INSERT INTO hwb VALUES(6, 'SMITH');
SELECT * FROM hwb;2、實現方法一：通過分組后，拿最大/最小的id重復刪除
--重復刪除多次
delete from hwb
where id in (select max(b.id) as id FROM hwb b group by name having count(name) > 1)
--或者修改為以下，只需刪除一次
delete from hwb
WHERE
NAME IN (SELECT NAME FROM hwb b GROUP BY NAME HAVING count(NAME) > 1)
AND id NOT IN (SELECT max(c.id ) as id FROM hwb c GROUP BY NAME HAVING count( NAME ) > 1);3、實現方法二：
根據name分組，查找出id最小的,然后再查找id不包含剛才查出來的。這樣就查詢出了所有的重復數據（除了id最小的那行）
delete from hwb
WHERE id NOT IN (SELECT min(c.id ) as id FROM hwb c GROUP BY NAME );4、實現方法三：通過name相同，id不同的方式來判斷
delete from hwb a
WHERE EXISTS (SELECT 1
FROM hwb b
WHERE a.name = b.name
AND a.id > b.id);5、[推薦]實現方法四：用ctid代替id，替換刪除
delete from hwb a
WHERE EXISTS (SELECT 1 FROM hwb b
WHERE a.name = b.name AND a.ctid > b.ctid);
--或者如下：
select * from hwb WHERE ctid NOT IN (SELECT min(ctid) as id FROM hwb GROUP BY NAME );6、[最高效推薦]實現方法五：用ctid代替id，結合row_number替換刪除
delete FROM hwb a WHERE a.ctid =
ANY (array (SELECT ctid FROM (SELECT row_number() over(PARTITION BY NAME) as seq, ctid FROM hwb ) t
WHERE t.seq > 1));本文轉載至頭條用戶“波波說運維”
總結
以上是生活随笔為你收集整理的数据库去重有几种方法_去重数据库(常用的数据网站汇总)的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： http get extension i
- 下一篇： how is view embedded