使用rowid去除oracle表中的重复项

来源：互联网发布：删除淘宝中差评的公司编辑：程序博客网时间：2024/06/07 23:59

需求，有如下一张b_user表，请去除employee_name重复的项

-- Create table
create table B_USER
(
id            VARCHAR2(32) not null,
employee_id   VARCHAR2(15),
employee_name VARCHAR2(30),
org_id        VARCHAR2(15),
org_filed     VARCHAR2(15)
)

解决方案：利用group by子句找出重复项；利用rowid去除重复项，分析步骤如下：

--1、筛选出重复的雇员名字
select employee_name from b_user group by employee_name having count(*)>1;
--2、筛选出名字重复的雇员信息
select * from b_user where employee_name in (
select employee_name from b_user group by employee_name having count(*)>1
);
--3、筛选min(rowid)或max(rowid)雇员信息
select min(rowid) from b_user where employee_name in (
select employee_name from b_user group by employee_name having count(*)>1
) group by employee_name;

--4、去除重复项
delete from b_user where employee_name in (
select employee_name from b_user group by employee_name having count(*)>1
) and rowid not in (
select min(rowid) from b_user where employee_name in (
select employee_name from b_user group by employee_name having count(*)>1
) group by employee_name
);

执行SQL后的效果如下:

0 0