建立測試表
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行。

SQL 處理重複值

相關文章