oracle 找到重复关键字,sql - 如何在Oracle中的表中找到重复值？

sql - 如何在Oracle中的表中找到重复值？

什么是最简单的SQL语句，它将返回给定列的重复值及其在Oracle数据库表中的出现次数？

例如：我有一个JOBS表，其中包含222196339345876684列。如何查看是否有任何重复的JOB_NUMBER，以及它们被复制了多少次？

13个解决方案

519 votes

select column_name, count(column_name)

from table

group by column_name

having count (column_name) > 1;

Bill the Lizard answered 2019-02-17T04:17:40Z

47 votes

其他方式：

SELECT *

FROM TABLE A

WHERE EXISTS (

SELECT 1 FROM TABLE

WHERE COLUMN_NAME = A.COLUMN_NAME

AND ROWID < A.ROWID

)

当column_name上有索引时工作正常(足够快)。并且它是删除或更新重复行的更好方法。

Grrey answered 2019-02-17T04:18:07Z

30 votes

最简单的我能想到：

select job_number, count(*)

from jobs

group by job_number

having count(*) > 1;

JosephStyons answered 2019-02-17T04:18:32Z

16 votes

如果您不需要知道重复的实际数量，则不需要在返回的列中包含计数。 例如

SELECT column_name

FROM table

GROUP BY column_name

HAVING COUNT(*) > 1

Evan answered 2019-02-17T04:18:58Z

7 votes

怎么样：

SELECT , count(*)

FROM

GROUP BY HAVING COUNT(*) > 1;

要回答上面的例子，它看起来像：

SELECT job_number, count(*)

FROM jobs

GROUP BY job_number HAVING COUNT(*) > 1;

Andrew answered 2019-02-17T04:19:25Z

5 votes

如果多列标识唯一行(例如关系表)，则可以使用以下内容

使用行ID   例如 emp_dept(empid，deptid，startdate，enddate)    假设empid和deptid是唯一的，并在这种情况下识别行

select oed.empid, count(oed.empid)

from emp_dept oed

where exists ( select *

from emp_dept ied

where oed.rowid <> ied.rowid and

ied.empid = oed.empid and

ied.deptid = oed.deptid )

group by oed.empid having count(oed.empid) > 1 order by count(oed.empid);

如果这样的表有主键，那么使用主键而不是rowid，例如id是pk然后

select oed.empid, count(oed.empid)

from emp_dept oed

where exists ( select *

from emp_dept ied

where oed.id <> ied.id and

ied.empid = oed.empid and

ied.deptid = oed.deptid )

group by oed.empid having count(oed.empid) > 1 order by count(oed.empid);

Jitendra Vispute answered 2019-02-17T04:20:06Z

4 votes

干

select count(j1.job_number), j1.job_number, j1.id, j2.id

from jobs j1 join jobs j2 on (j1.job_numer = j2.job_number)

where j1.id != j2.id

group by j1.job_number

会给你重复的行ID。

agnul answered 2019-02-17T04:20:33Z

4 votes

SELECT SocialSecurity_Number, Count(*) no_of_rows

FROM SocialSecurity

GROUP BY SocialSecurity_Number

HAVING Count(*) > 1

Order by Count(*) desc

Wahid Haidari answered 2019-02-17T04:20:52Z

1 votes

我知道这是一个旧线程，但这可能对某人有所帮助。

如果您需要打印表格的其他列，同时检查下面的重复使用：

select * from table where column_name in

(select ing.column_name from table ing group by ing.column_name having count(*) > 1)

order by column_name desc;

如果需要，还可以在where子句中添加一些额外的过滤器。

Parth Kansara answered 2019-02-17T04:21:31Z

0 votes

我通常使用Oracle Analytic函数ROW_NUMBER()。

假设您要检查有关在列上构建的唯一索引或主键的重复项(ROWID,c2,c3)。然后你会这样，带来ROWID的行数，其中ROW_NUMBER()带来的行数是>1：

Select * From Table_With_Duplicates

Where Rowid In

(Select Rowid

From (Select Rowid,

ROW_NUMBER() Over (

Partition By c1 || c2 || c3

Order By c1 || c2 || c3

) nbLines

From Table_With_Duplicates) t2

Where nbLines > 1)

J. Chomel answered 2019-02-17T04:22:05Z

0 votes

这是执行此操作的SQL请求：

select column_name, count(1)

from table

group by column_name

having count (column_name) > 1;

Chaminda Dilshan answered 2019-02-17T04:22:32Z

-1 votes

解决方案

select * from emp

where rowid not in

(select max(rowid) from emp group by empno);

DoOrDie answered 2019-02-17T04:22:58Z

-1 votes

你也可以尝试这样的方法来列出表格中的所有重复值，例如reqitem

SELECT count(poid)

FROM poitem

WHERE poid = 50

AND rownum < any (SELECT count(*) FROM poitem WHERE poid = 50)

GROUP BY poid

MINUS

SELECT count(poid)

FROM poitem

WHERE poid in (50)

GROUP BY poid

HAVING count(poid) > 1;

Stacker answered 2019-02-17T04:23:25Z