select * from ( select id,age,row_number() over(partition by age order by id) nn from person ) where nn>1 删除的动作,你自己改一下!就OK!
delete from person where id in (select id from ( select id,age,row_number() over(partition by age order by id) nn from person ) where nn>1)
row_number() over (partition by col1 order by col2) 表示根据col1分组,在分组内部根据 col2排序 而这个值就表示每组内部排序后的顺序编号(组内连续的唯一的)
over (partition by col1 order by col2) over 这个函数怎么使用呢 。呼~ 高手 帮我 解释一下吧
删掉最小的一个: delete from person where rowid in (select rd from ( select rowid,row_number() over(partition by age order by rowid) nn from person ) where nn<>1) 随机删掉: delete from person where rowid in (select rd from ( select rowid,row_number() over(partition by age order by dbms_random.value) nn from person ) where nn<>1)
不好意思错了一点点 delete from person where rowid in (select rd from (select rowid rd, row_number() over(partition by age order by rowid) nn from person) where nn <> 1) 随机删掉: delete from person where rowid in (select rd from (select rowid rd, row_number() over(partition by age order by dbms_random.value) nn from person) where nn <> 1)
(
select id,age,row_number() over(partition by age order by id) nn from person
)
where nn>1 删除的动作,你自己改一下!就OK!
where id in
(select id from
(
select id,age,row_number() over(partition by age order by id) nn from person
)
where nn>1)
表示根据col1分组,在分组内部根据 col2排序
而这个值就表示每组内部排序后的顺序编号(组内连续的唯一的)
over 这个函数怎么使用呢 。呼~ 高手 帮我 解释一下吧
delete from person
where rowid in
(select rd from
(
select rowid,row_number() over(partition by age order by rowid) nn from person
)
where nn<>1)
随机删掉:
delete from person
where rowid in
(select rd from
(
select rowid,row_number() over(partition by age order by dbms_random.value) nn from person
)
where nn<>1)
delete from person
where rowid in (select rd
from (select rowid rd,
row_number() over(partition by age order by rowid) nn
from person)
where nn <> 1)
随机删掉:
delete from person
where rowid in (select rd
from (select rowid rd,
row_number() over(partition by age order by dbms_random.value) nn
from person)
where nn <> 1)