oracle rowid查重,查重_ITPUB博客

最新推荐文章于 2021-07-29 20:50:02 发布

朱桐辉

最新推荐文章于 2021-07-29 20:50:02 发布

阅读量171

点赞数

文章标签： oracle rowid查重

这篇博客介绍了如何使用SQL查询来检测和处理数据表中的重复记录。通过`SELECT`、`GROUP BY`、`HAVING`和`DELETE`语句，展示了如何统计重复记录、查询重复记录以及删除这些记录。同时，还展示了如何在两个表之间查找并比较重复数据，使用了`EXISTS`和`IN`操作符进行子查询。

摘要由CSDN通过智能技术生成

--查重复记录

--数据

> select * from emp1 order by 1;

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

9 rows selected.

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

> select no,name,count(*) from emp1 group by no,name having count(*) >1;

NO NAME COUNT(*)

---------- ------------------------------ ----------

7499 ALLEN 4

7369 SMITH 2

2、查询重复记录

> 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

> 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

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、两个表查重复数据

--数据

> 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

> 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

> 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

> 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

朱桐辉

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
oracle rowid查重,查重_ITPUB博客

--查重复记录--数据> select * from emp1 order by 1;NO NAME JOB SAL DEPTNO---------- ------------------------------ --------------------------- -...
复制链接

扫一扫