大家好,又见面了,我是你们的朋友全栈君。
今天主要介绍一下Oracle、MySQL、sqlserver、pg数据库在删除重复数据时是怎么实现的。这里用实例来说明。
可以看到“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次
delete from hwb a
where a.id in
(select min(id) FROM hwb a group by name having count(name) > 1)
DELETE FROM hwb a
WHERE EXISTS (SELECT 1
FROM hwb b
WHERE a.name = b.name
AND a.id > b.id);
DELETE FROM hwb a
WHERE EXISTS (SELECT 1
FROM hwb b
WHERE a.name = b.name
AND a.rowid > b.rowid);
语法格式: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);
DELETE FROM hwb a
WHERE a.ROWID > (SELECT MIN(b.ROWID) FROM hwb b WHERE a.name = b.name);
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;
需删除两次
--重复删除多次
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)
根据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)
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 );
在 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:可考虑表切换完成去重步骤
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');
GO
需删除两次
--重复删除多次
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 )
这样就查询出了所有的重复数据(除了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)
DELETE FROM hwb a
WHERE EXISTS (SELECT 1
FROM hwb b
WHERE a.name = b.name
AND a.id > b.id);
%%lockres%%会返回聚集索引键的hash值, 非聚集索引,%%lockres%%会返回非聚集索引键的hash值。
select *,%%lockres%% RID FROM hwb a
-- delete from hwb a
WHERE EXISTS (SELECT 1
FROM hwb b
WHERE a.name = b.name
AND a.%%lockres%% > b.%%lockres%%);
语法格式: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 );
DELETE FROM hwb a
WHERE a.%%lockres%% > (SELECT MIN(b.%%lockres%%) FROM hwb b WHERE a.name = b.name);
$ psql
\l --list database
\c database_name
\dn --list schemas
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;
需删除两次
--重复删除多次
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);
根据name分组,查找出id最小的,然后再查找id不包含刚才查出来的。这样就查询出了所有的重复数据(除了id最小的那行)
delete from hwb
WHERE id NOT IN (SELECT min(c.id ) as id FROM hwb c GROUP BY NAME );
delete from hwb a
WHERE EXISTS (SELECT 1
FROM hwb b
WHERE a.name = b.name
AND a.id > b.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 );
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));
版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。
发布者:全栈程序员栈长,转载请注明出处:https://javaforall.cn/187235.html原文链接:https://javaforall.cn