删除反复行SQL举例

阅读量：6134 次

发布时间：2019-06-21

本文共 11108 字，大约阅读时间需要 37 分钟。

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

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

构造实验环境：

SQL> select count(*) from emp;

COUNT(*)

----------

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(*)

----------

以下举例单字段删除反复行

例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

7782 CLARK MANAGER 7839 09-JUN-81 2450 10

7839 KING PRESIDENT 17-NOV-81 5000 10

7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

7902 FORD ANALYST 7566 03-DEC-81 3000 20

7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30

7788 SCOTT ANALYST 7566 19-APR-87 3000 20

7566 JONES MANAGER 7839 02-APR-81 2975 20

7934 MILLER CLERK 7782 23-JAN-82 1300 10

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

7876 ADAMS CLERK 7788 23-MAY-87 1100 20

7369 SMITH CLERK 7902 17-DEC-80 800 20

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

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

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” 博客。欢迎转载。转载时请务必注明出处（）。

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

提醒：点击文件夹。更有助于您的查看。

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

你可能感兴趣的文章

重新想象 Windows 8 Store Apps (18) - 绘图: Shape, Path, Stroke, Brush

查看>>

Revit API找到风管穿过的墙(当前文档和链接文档)

查看>>

Scroll Depth – 衡量页面滚动的 Google 分析插件

查看>>

Windows 8.1 应用再出发 - 视图状态的更新

查看>>

自己制作交叉编译工具链

查看>>

Qt Style Sheet实践（四）：行文本编辑框QLineEdit及自动补全

查看>>

[物理学与PDEs]第3章习题1 只有一个非零分量的磁场

查看>>

深入浅出NodeJS——数据通信，NET模块运行机制

查看>>

onInterceptTouchEvent和onTouchEvent调用时序

SSIS从理论到实战,再到应用(3)----SSIS包的变量，约束，常用容器

查看>>

STM32启动过程--启动文件--分析

查看>>

垂死挣扎还是涅槃重生 -- Delphi XE5 公布会归来感想

查看>>

淘宝的几个架构图

查看>>

Android扩展 - 拍照篇（Camera）

查看>>

JAVA数组的定义及用法

查看>>

充分利用HTML标签元素 – 简单的xtyle前端框架

查看>>