基于Oracle中的去重处理
有表test如下:
cola | colb |
---|---|
aa | bb |
aa | bbbbb |
aa | bb |
aa | bb |
- 获取表中的cola,colb列去重后的结果有以下几种方式:
select distinct cola,colb from test;
select unique cola,colb from test;
select cola,colb from test group by cola,colb;
select cola,colb from test union select cola,colb from test where 1=2;
- 删除cola,colb重复的数据只保留一条,删除后test表中的数据如下:
cola | colb |
---|---|
aa | bb |
aa | bbbbb |
delete from test where test in(select min(rowid) from test group by cola,colb);
delete from test where test in(select max(rowid) from test group by cola,colb);