创建测试表
create table dups(id number,title_name varchar2(15),type varchar2(15),price number);
insert into dups values(1,'Book Title 5','children',15.00);
insert into dups values(2,'Book Title 3','biography',7.00);
insert into dups values(3,'Book Title 1','history',10.00);
insert into dups values(4,'Book Title 2','children',20.00);　
insert into dups values(5,'Book Title 4','history',15.00);
insert into dups values(6,'Book Title 1','history',10.00);
insert into dups values(7,'Book Title 3','biography',7.00);
insert into dups values(8,'Book Title 1','history',10.00);

SQL> select * from dups;
ID TITLE_NAME TYPE PRICE
---------- --------------- --------------- ----------
1 Book Title 5 children 15
2 Book Title 3 biography 7
3 Book Title 1 history 10
4 Book Title 2 children 20
5 Book Title 4 history 15
6 Book Title 1 history 10
7 Book Title 3 biography 7
8 Book Title 1 history 10

已选择8行。

列出重复值
SQL> select title_name,type,price from dups group by title_name,type,price
2 having count(*) > 1;
TITLE_NAME TYPE PRICE
--------------- --------------- ----------
Book Title 3 biography 7
Book Title 1 history 10

列出每一行和它的重复数
SQL> select title_name,type,price,count(*) as NumDups from dups
2 group by title_name,type,price
3 having count(*) >= 1
4 order by count(*) desc;

TITLE_NAME TYPE PRICE NUMDUPS
--------------- --------------- ---------- ----------
Book Title 1 history 10 3
Book Title 3 biography 7 2
Book Title 4 history 15 1
Book Title 2 children 20 1
Book Title 5 children 15 1

删除适当位置的冗余重复行
delete from dups
where id < (
select max(d.id) from dups d
where d.title_name = dups.title_name
and d.type = dups.type
and d.price = dups.price)
SQL> /
已删除3行。

在oracle中可以使用ROWID伪列id也同样可以删除重复值
SQL> delete from dups
2 where rowid < (
3 select max(d.rowid) from dups d
4 where d.title_name = dups.title_name
5 and d.type = dups.type
6 and d.price = dups.price);
已删除3行。

ITPub博客

SQL 处理重复值

最新文章