删除重复行SQL举例

2023-12-22 17:03:51

删除重复行SQL实验简单举例


说明：实验按顺序进行，前后存在关联性，阅读时请注意，打开目录更便于查看。


构造实验环境：


SQL> select count(*) from emp;
  COUNT(*)
----------
        14
SQL> alter table EMP drop constraint PK_EMP CASCADE;
--删除主键约束
SQL> insert into EMP select * from EMP;
--向EMP表中插入重复行
SQL> commit;
SQL> select count(*) from emp;
  COUNT(*)
----------
        28
        
下面举例单字段删除重复行

例1：查询出重复记录（单字段）

SQL> select * from emp 
where empno in (select empno from emp group by empno having count 
(empno) > 1); 
--查找出emp表中多余的重复记录，重复记录是根据empno来判读


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30


28 rows selected.

例2：删除表中多余的重复记录，只留有rowid最小的记录

SQL> delete from emp 
where empno in (select empno from emp group by empno having count 
(empno) > 1) 
and rowid not in (select min(rowid) from emp group by empno having count(empno 
)>1); 
--删除表中多余的重复记录，重复记录是根据单个字段empno来判读
--保留rowid最小的行记录信息
14 rows deleted.


SQL> select * from emp ;  --重复的行已经删除


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7839 KING       PRESIDENT            17-NOV-81       5000                    10


14 rows selected.






SQL> insert into emp select * from emp;


SQL> insert into emp values(8888,‘HYL‘,‘DBA‘,7839,sysdate,2000,‘‘,60);


1 row created.


SQL> commit;


Commit complete.


SQL> select * from emp;      


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      8888 HYL        DBA             7839 06-JUL-14       2000                    60


29 rows selected.




多字段下：

例3：查询表中重复的记录（多字段）

SQL> select * from emp a 
where (a.empno,a.deptno) in (select empno,deptno from emp group by empno,deptno having 
count(*) > 1); 


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30


28 rows selected.

例4：删除表中重复记录（多个字段），只保留rowid最小的记录

SQL> delete from emp a 
where (a.empno,a.deptno) in (select empno,deptno from emp group by empno,deptno having 
count(*) > 1) 
and rowid not in (select min(rowid) from emp group by empno,deptno having count(*)>1); 


14 rows deleted.


SQL> select * from emp;


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      8888 HYL        DBA             7839 06-JUL-14       2000                    60


15 rows selected.






SQL> rollback 
  2  ;


Rollback complete.


SQL> select * from emp;


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      8888 HYL        DBA             7839 06-JUL-14       2000                    60


29 rows selected.

例5：查询表中多余的重复记录（多字段），不包含rowid最小的记录

SQL> select * from emp a 
where (a.empno,a.deptno) in (select empno,deptno from emp group by empno,deptno having 
count(*) > 1) 
and rowid not in (select min(rowid) from emp group by empno,deptno having count(*)>1);


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30


14 rows selected.


SQL> commit;


Commit complete.


SQL> select * from emp;   --emp表实际数据


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      8888 HYL        DBA             7839 06-JUL-14       2000                    60


29 rows selected.

***********************************************声明************************************************ 

原创作品，出自 “深蓝的blog” 博客，欢迎转载，转载时请务必注明出处（http://blog.csdn.net/huangyanlong）。

表述有错误之处，请您留言，不胜感激。

提醒：点击目录，更有助于您的查看。

*****************************************************************************************************






删除重复行SQL举例,布布扣,bubuko.com

删除重复行SQL举例

上一篇：数据库设计过程


下一篇：oracle_linux_6_64（bit）上安装oracle11gR2数据库环境快速准备神器oracle-rdbms-server-11gR2-preinstall