SQL 如何在 Oracle 中的表中查找重复值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/59232/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I find duplicate values in a table in Oracle?
提问by Andrew
What's the simplest SQL statement that will return the duplicate values for a given column and the count of their occurrences in an Oracle database table?
将返回给定列的重复值及其在 Oracle 数据库表中出现次数的最简单 SQL 语句是什么？
For example: I have a JOBStable with the column JOB_NUMBER. How can I find out if I have any duplicate JOB_NUMBERs, and how many times they're duplicated?
例如：我有一个JOBS带有列的表JOB_NUMBER。我怎样才能知道我是否有任何重复的JOB_NUMBERs，以及它们被重复了多少次？
回答by Bill the Lizard
SELECT column_name, COUNT(column_name)
FROM table_name
GROUP BY column_name
HAVING COUNT(column_name) > 1;
回答by Grrey
Another way:
其它的办法：
SELECT *
FROM TABLE A
WHERE EXISTS (
SELECT 1 FROM TABLE
WHERE COLUMN_NAME = A.COLUMN_NAME
AND ROWID < A.ROWID
)
Works fine (quick enough) when there is index on column_name. And it's better way to delete or update duplicate rows.
当 上有索引时工作正常（足够快）column_name。这是删除或更新重复行的更好方法。
回答by JosephStyons
Simplest I can think of:
我能想到的最简单的：
select job_number, count(*)
from jobs
group by job_number
having count(*) > 1;
回答by Evan
You don't need to even have the count in the returned columns if you don't need to know the actual number of duplicates. e.g.
如果您不需要知道实际的重复数，您甚至不需要在返回的列中进行计数。例如
SELECT column_name
FROM table
GROUP BY column_name
HAVING COUNT(*) > 1
回答by Andrew
How about:
怎么样：
SELECT <column>, count(*)
FROM <table>
GROUP BY <column> HAVING COUNT(*) > 1;
To answer the example above, it would look like:
要回答上面的示例，它看起来像：
SELECT job_number, count(*)
FROM jobs
GROUP BY job_number HAVING COUNT(*) > 1;
回答by Jitendra Vispute
In case where multiple columns identify unique row (e.g relations table ) there you can use following
如果多列标识唯一行（例如关系表），您可以使用以下
Use row id e.g. emp_dept(empid, deptid, startdate, enddate) suppose empid and deptid are unique and identify row in that case
使用行 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);
and if such table has primary key then use primary key instead of rowid, e.g id is pk then
如果这样的表有主键，那么使用主键而不是 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);
回答by Wahid Haidari
SELECT SocialSecurity_Number, Count(*) no_of_rows
FROM SocialSecurity
GROUP BY SocialSecurity_Number
HAVING Count(*) > 1
Order by Count(*) desc
回答by agnul
Doing
正在做
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
will give you the duplicated rows' ids.
会给你重复行的 ID。
回答by J. Chomel
I usually use Oracle Analyticfunction ROW_NUMBER().
我通常使用Oracle 分析函数ROW_NUMBER()。
Say you want to check the duplicates you have regarding a unique index or primary key built on columns (c1, c2, c3).
Then you will go this way, bringing up ROWIDs of rows where the number of lines brought by ROW_NUMBER()is >1:
假设您要检查关于在列 ( c1, c2, c3)上构建的唯一索引或主键的重复项。然后你会走这条路，带来ROWIDs 行，其中带来的行数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)
回答by Chaminda Dilshan
Here is an SQL request to do that:
这是执行此操作的 SQL 请求：
select column_name, count(1)
from table
group by column_name
having count (column_name) > 1;
