oracle中按指定字段排序删除重复记录

       今天看了一下项目上的DBA针对某一模块中的业务写的存储过程,里面数据清洗的过程中时常会进行排重操作,自己总结了一下oracle中排重的思路

1,使用rowid来作为限制条件排重

rowidrownum的区别
两者都是伪列,rowid是物理结构上的,而rownum是逻辑结构上的,rowid是用于定位数据库中某条记录的相对唯一的地址

通常情况下数据在插入数据库中时就已经被确定且唯一,使用rowid可以定位该行的物理地址信息,rownum是动态的,查询   语句的不同,rownum是不同的,当查询语句为以empno升序时SMITH的rownum是1,而当以降序排列时,SMITH的    rownum就是14,由于rowid的唯一性,所以使用rowid来实现排重,即在根据条件查询后的结果上加上rowid<>1就行了

2,使用row_number() over(partition by column1 order by column2) 来实现排重

上面这句语句的意思是在以column1分组后并且组内以column2排序的条件下分级:

over:  在什么条件之上。
partition by e.deptno:  按部门编号划分(分区)。
order by e.sal desc:  按工资从高到低排序(使用rank()/dense_rank()/row_number() 时,必须要带order by否则非法);
rank()/dense_rank()/row_number() :  分级
整个语句的意思就是:
在按部门划分的基础上,按工资从高到低对雇员进行分级,“级别”由从小到大的数字表示(最小值一定为1)
row_number(),rank(),dense_rank()的区别:
在'分级时',如果有两个记录按照当前的筛选条件是相同的排名,那么,rank()会有两个分级为一 的记录,接下来为第三级,

跳跃排序,而dense_rank()则是有两个第一级后接下来还是第二级,即连续排序,row_number()则是第一级后会按照默认的规则排序后显示第二级

举例说明:

1,以部门编号分组,并且组内以工资降序排列后分级:

select e.deptno, e.empno,e.ename,e.sal,rank() over(partition by deptno order by sal desc) from emp e;

结果如下:

部门号为20 的部门中有两个工资为3500的员工,在分级时使用的是rank(),所以两个3500的分级都是1,接着是3

2,使用dense_rank() 分级

select e.deptno, e.empno,e.ename,e.sal,dense_rank() over(partition by deptno order by sal desc) from emp e;

结果如下:


两个工资为3500的员工的分级依旧是1,但是接下来的员工的分级是2

3,使用row_number()分级

select e.deptno, e.empno,e.ename,e.sal,row_number() over(partition by deptno order by sal desc) from emp e;

结果如下:


按照员工的工资来分级出现重复后,会按照默认的顺序来分级,但是不会出现相同的级别

我们就可以利用row_number()的这个特性来

delete from t_visit_customer p where p.rowid in (
select e.rowid from 
(select t.rowid,row_number() over(partition by t.credential_no order by t.is_gen_self desc nulls last) rw
from t_visit_customer t where t.branch_code = in_branch_code) e where e.rw<>1;
) and p.branch_code = in_branch_code;

可以分解为以下三个步骤:

-- 使用row_number()  over(partiton by ...)实现排重
-- 其中t_visit_customer为表名,in_branch_code为存储过程中的输入参数
-- 1,查找记录行的rowid和使用row_number() over(partition by...)来查找对应分组条件和排序条件下的顺序
select t.rowid,row_number() over(partition by t.credential_no order by t.is_gen_self desc nulls last) rw
from t_visit_customer t where t.branch_code = in_branch_code;
-- 2,查找重复的数据的rowid
select e.rowid from 
(select t.rowid,row_number() over(partition by t.credential_no order by t.is_gen_self desc nulls last) rw
from t_visit_customer t where t.branch_code = in_branch_code) e where e.rw<>1;
-- 3,删除原表中rowid为重复数据的rowid的列进行排重
delete from t_visit_customer p where p.rowid in (
select e.rowid from 
(select t.rowid,row_number() over(partition by t.credential_no order by t.is_gen_self desc nulls last) rw
from t_visit_customer t where t.branch_code = in_branch_code) e where e.rw<>1;
) and p.branch_code = in_branch_code;

即:去除公司代码为in_branch_code的公司中credential_no相同的记录并且留下的是is_gen_self最大的记录


小弟是菜鸟,如有错误,欢迎大佬们指教!

已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 编程工作室 设计师:CSDN官方博客 返回首页