查重

--查重复记录

--数据

scott@OCMDB> select * from emp1 order by 1;                                               
                                                                                          
        NO NAME                           JOB                                SAL     DEPTNO
---------- ------------------------------ --------------------------- ---------- ----------
      7369 SMITH                          CLERK                              100         20
      7369 SMITH                          CLERK                              100         20
      7499 ALLEN                          SALESMAN                          1600         30
      7499 ALLEN                          SALESMAN                          1600         30
      7499 ALLEN                          SALESMAN                          1600         30
      7499 ALLEN                          SALESMAN                          1600         30
      7521 WARD                           SALESMAN                          1250         30
      7566 JONES                          MANAGER                           2975         20
      7654 MARTIN                         SALESMAN                          1250         30
                                                                                          
9 rows selected.

1、统计 no,name相同的重复记录数

scott@OCMDB> select no,name,count(*) from emp1 group by no,name having count(*) >1;
                                                                                  
        NO NAME                             COUNT(*)                                                                                                        
---------- ------------------------------ ----------                              
      7499 ALLEN                                   4                              
      7369 SMITH                                   2  

2、查询重复记录

scott@OCMDB> select * from emp1 where rowid in (select min(rowid) from emp1 group by no,name having count(*)>1);

        NO NAME                           JOB                                SAL     DEPTNO
---------- ------------------------------ --------------------------- ---------- ----------
      7499 ALLEN                          SALESMAN                          1600         30
      7369 SMITH                          CLERK                              100         20
     

scott@OCMDB> select * from emp1 where (no,name) in (select no,name from emp1 group by no,name having count(*)>1)
                                                                                                               
        NO NAME                           JOB                                SAL     DEPTNO                    
---------- ------------------------------ --------------------------- ---------- ----------                    
      7499 ALLEN                          SALESMAN                          1600         30                    
      7499 ALLEN                          SALESMAN                          1600         30                    
      7499 ALLEN                          SALESMAN                          1600         30                    
      7499 ALLEN                          SALESMAN                          1600         30                    
      7369 SMITH                          CLERK                              100         20                    
      7369 SMITH                          CLERK                              100         20                    
                                                                                                               
6 rows selected.        

3、删除重复记录，保留一条 

delete from emp1 where (no,name) in (select no,name from emp1 group by no,name having count(*)>1)
 and rowid not in (select min(rowid) from emp1 group by no,name having count(*)>1)
 
 
4、两个表查重复数据
--数据
scott@OCMDB> select * from emp1;

        NO NAME                           JOB                                SAL     DEPTNO
---------- ------------------------------ --------------------------- ---------- ----------
      7369 SMITH                          CLERK                              100         20
      7499 ALLEN                          SALESMAN                          1600         30
      7521 WARD                           SALESMAN                          1250         30
      7566 JONES                          MANAGER                           2975         20
      7654 MARTIN                         SALESMAN                          1250         30

Elapsed: 00:00:00.00
scott@OCMDB> select * from emp;

     EMPNO ENAME                          JOB                                MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ------------------------------ --------------------------- ---------- ------------------- ---------- ---------- ----------
      7369 SMITH                          CLERK                             7902 1980-12-17 00:00:00        100                    20
      7499 ALLEN                          SALESMAN                          7698 1981-02-20 00:00:00       1600        300         30
      7521 WARD                           SALESMAN                          7698 1981-02-22 00:00:00       1250        500         30
      7566 JONES                          MANAGER                           7839 1981-04-02 00:00:00       2975                    20
      7654 MARTIN                         SALESMAN                          7698 1981-09-28 00:00:00       1250       1400         30
      7698 BLAKE                          MANAGER                           7839 1981-05-01 00:00:00       2850                    30
      7782 CLARK                          MANAGER                           7839 1981-06-09 00:00:00       2450                    10
      7788 SCOTT                          ANALYST                           7566 1987-04-19 00:00:00        100                    20
      7839 KING                           PRESIDENT                              1981-11-17 00:00:00       5000                    10
      7844 TURNER                         SALESMAN                          7698 1981-09-08 00:00:00       1500          0         30
      7876 ADAMS                          CLERK                             7788 1987-05-23 00:00:00       1100                    20
      7900 JAMES                          CLERK                             7698 1981-12-03 00:00:00        950                    30
      7902 FORD                           ANALYST                           7566 1981-12-03 00:00:00       3000                    20
      7934 MILLER                         CLERK                             7782 1982-01-23 00:00:00       1300                    10 

--使用exists
  
scott@OCMDB> select * from emp1 a where exists(select 1 from emp b where a.no=b.empno);

        NO NAME                           JOB                                SAL     DEPTNO
---------- ------------------------------ --------------------------- ---------- ----------
      7369 SMITH                          CLERK                              100         20
      7499 ALLEN                          SALESMAN                          1600         30
      7521 WARD                           SALESMAN                          1250         30
      7566 JONES                          MANAGER                           2975         20
      7654 MARTIN                         SALESMAN                          1250         30

Elapsed: 00:00:00.00     

--使用 in
scott@OCMDB> select * from emp1 where (no,name) in (select empno,ename from emp);

        NO NAME                           JOB                                SAL     DEPTNO
---------- ------------------------------ --------------------------- ---------- ----------
      7369 SMITH                          CLERK                              100         20
      7499 ALLEN                          SALESMAN                          1600         30
      7521 WARD                           SALESMAN                          1250         30
      7566 JONES                          MANAGER                           2975         20
      7654 MARTIN                         SALESMAN                          1250         30

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22193071/viewspace-1179864/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22193071/viewspace-1179864/