[Oracle]去除某行,单列重复的数据
- 01.说明
- 02.添加辅助列
- 03.优先级排序
- 04.去除重复项
- 05.批量删除
01.说明
因为实在找不到可以模拟该方法的案例,就简单的说一下大概的数据和处理逻辑,小伙伴们懂这个逻辑就行,到实战里活学活用:
select * from
(
select 1001 id,'小白'name,60 achievement from dual
union all
select 1001 id,'小白'name,100 achievement from dual
union all
select 1001 id,'小白'name,30 achievement from dual)order by id
假设,这3条数据每一条都是做了某些限制得到的,我们将这些数据全部汇集到一起后,就会出现单列重复
而其他数据不重复的数据.我们要如何去除这些数据呢?
02.添加辅助列
每一个数据源的限制都有一个限制的顺序,类似case when xx then xx when xxx then xxx end
所以我们用辅助列来假定他们的优先级.
select * from
(
select 1001 id,'小白'name,60 achievement,1 sort from dual
union all
select 1001 id,'小白'name,100 achievement,2 sort from dual
union all
select 1001 id,'小白'name,30 achievement,3 sort from dual)order by id
03.优先级排序
我们用row_number
函数,并以id
分割,id,sort
排序,得出重复限制的优先级
select t.*,row_number()over(partition by id order by id,sort)a from
(
select 1001 id,'小白'name,60 achievement,1 sort from dual
union all
select 1001 id,'小白'name,100 achievement,2 sort from dual
union all
select 1001 id,'小白'name,30 achievement,3 sort from dual)t order by id
04.去除重复项
我们只要a=1
的数据,剩下的都是单列重复的数据,这样,就完成了去除某行,单列重复的数据
的功能
select * from(select t.*,row_number()over(partition by id order by id,sort)a from
(
select 1001 id,'小白'name,60 achievement,1 sort from dual
union all
select 1001 id,'小白'name,100 achievement,2 sort from dual
union all
select 1001 id,'小白'name,30 achievement,3 sort from dual)t order by id)
where a=1 order by id
05.批量删除
DELETE FROM xx WHERE rowid in (select rowid from (select rowid,post,userid,to_char(UPDATATIME,'yyyy/mm/dd HH24:mi:ss')UPDATATIME,
row_number()over(partition by userid,post order by userid,post,UPDATATIME desc)a
from xx)where a<>1)
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)