oracle数据重复相关语句处理

1】针对指定列，查出去重后的结果集 distinct
select distinct t1.* from v_resi_lis_info_rm t1;
方法局限性很大，因为它只能对全部查询的列做去重。如果我想对item_name,col3去重，那我的结果集中就只能有item_name,col_3列，而不能有其他列。
select distinct t.item_name,t.col_3 from v_resi_lis_info_rm t;

写法上要麻烦不少，但是有更大的灵活性。 row_number()
select *
from (select t1.*,
row_number() over(partition by t1.item_name order by 1) rn
from v_resi_lis_info_rm t1) t
where t.rn = 1;

2】针对指定列，查出所有重复的行
要查两次表，效率会比较低。不推荐。
select t.*
from v_resi_lis_info_rm t
where (t.item_name) in (select t1.item_name
from v_resi_lis_info_rm t1
group by t1.item_name
having count(1) > 1);

只需要查一次表，推荐。count over
select *
from (select t1.*,
count(1) over(partition by t1.col_2, t1.col_3) rn
from nayi224_180824 t1) t1
where t1.rn > 1;

3】删除所有重复的行
就是上面的语句稍作修改。
delete from nayi224_180824 t
where t.rowid in (
select rid
from (select t1.rowid rid,
count(1) over(partition by t1.col_2, t1.col_3) rn
from nayi224_180824 t1) t1
where t1.rn > 1);

删除重复数据并保留一条
拥有分析函数一贯的灵活性高的特点。可以为所欲为的分组，并通过改变orderby从句来达到像”保留最大id“这样的要求。
分析函数法
delete from nayi224_180824 t
where t.rowid in (select rid
from (select t1.rowid rid,
row_number() over(partition by t1.col_2, t1.col_3 order by 1) rn
from nayi224_180824 t1) t1
where t1.rn > 1);

牺牲了一部分灵活性，换来了更高的效率。group by
delete from nayi224_180824 t
where t.rowid not in
(select max(rowid) from nayi224_180824 t1 group by t1.col_2, t1.col_3);