blocks|key|2954692|text|按计数聚合列，然后使用HAVING子句查找出现次数超过一次的值。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2954693|SELECT+column_name,+COUNT(column_name)
FROM+table_name
GROUP+BY+column_name
HAVING+COUNT(column_name)+>+1;|code-block|syntax|javascript|2954694|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Aggregate the column by COUNT, then use a HAVING clause to find values that appear greater than one time.
<pre><code>SELECT column_name, COUNT(column_name)
FROM table_name
GROUP BY column_name
HAVING COUNT(column_name) &gt; 1;
</code></pre>

blocks|key|2954751|text|另一种方式：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2954752|SELECT+*
FROM+TABLE+A
WHERE+EXISTS+(
++SELECT+1+FROM+TABLE
++WHERE+COLUMN_NAME+=+A.COLUMN_NAME
++AND+ROWID+<+A.ROWID
)|code-block|syntax|javascript|2954753|当在column_name上有索引时，工作正常(足够快)。这是删除或更新重复行的更好方法。|offset|length|style|CODE|2954754|entityMap^0|0|0|2|B|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@$I|R|J|S|K|L]]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

Another way:

<pre><code>SELECT *
FROM TABLE A
WHERE EXISTS (
 SELECT 1 FROM TABLE
 WHERE COLUMN_NAME = A.COLUMN_NAME
 AND ROWID &lt; A.ROWID
)
</code></pre>

Works fine (quick enough) when there is index on <code>column_name</code>. And it's better way to delete or update duplicate rows.

blocks|key|5210236|text|我能想到的最简单的：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|5210237|select+job_number,+count(*)
from+jobs
group+by+job_number
having+count(*)+>+1;|code-block|syntax|javascript|5210238|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Simplest I can think of:

<pre><code>select job_number, count(*)
from jobs
group by job_number
having count(*) &gt; 1;
</code></pre>

blocks|key|5210997|text|如果您不需要知道实际的重复数，那么您甚至不需要在返回的列中包含计数。例如：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|5210998|SELECT+column_name
FROM+table
GROUP+BY+column_name
HAVING+COUNT(*)+>+1|code-block|syntax|javascript|5210999|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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.

<pre><code>SELECT column_name
FROM table
GROUP BY column_name
HAVING COUNT(*) &gt; 1
</code></pre>

blocks|key|730479|text|这样如何：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|730480|SELECT+<column>,+count(*)
FROM+<table>
GROUP+BY+<column>+HAVING+COUNT(*)+>+1;|code-block|syntax|javascript|730481|要回答上面的示例，它将如下所示：|730482|SELECT+job_number,+count(*)
FROM+jobs
GROUP+BY+job_number+HAVING+COUNT(*)+>+1;|730483|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

How about:

<pre><code>SELECT &lt;column&gt;, count(*)
FROM &lt;table&gt;
GROUP BY &lt;column&gt; HAVING COUNT(*) &gt; 1;
</code></pre>

To answer the example above, it would look like:

<pre><code>SELECT job_number, count(*)
FROM jobs
GROUP BY job_number HAVING COUNT(*) &gt; 1;
</code></pre>

blocks|key|5211095|text|在多个列标识唯一行(例如关系表)的情况下，您可以使用以下命令|type|unstyled|depth|inlineStyleRanges|entityRanges|data|5211096|使用行id，例如emp_dept(+empid，deptid，startdate，enddate)假设empid和deptid是唯一的，并在这种情况下标识行|5211097|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);|code-block|syntax|javascript|5211098|如果这样的表有主键，那么使用主键而不是rowid，例如id是pk，那么|5211099|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);|5211100|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|Q|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|R|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|S|8|@]|9|@]|A|$G|H]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

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

<pre><code>select oed.empid, count(oed.empid) 
from emp_dept oed 
where exists ( select * 
 from emp_dept ied 
 where oed.rowid &lt;&gt; ied.rowid and 
 ied.empid = oed.empid and 
 ied.deptid = oed.deptid ) 
 group by oed.empid having count(oed.empid) &gt; 1 order by count(oed.empid);
</code></pre>

and if such table has primary key then use primary key instead of rowid, e.g id is pk then

<pre><code>select oed.empid, count(oed.empid) 
from emp_dept oed 
where exists ( select * 
 from emp_dept ied 
 where oed.id &lt;&gt; ied.id and 
 ied.empid = oed.empid and 
 ied.deptid = oed.deptid ) 
 group by oed.empid having count(oed.empid) &gt; 1 order by count(oed.empid);
</code></pre>

blocks|key|5210279|text|正在做什么|type|unstyled|depth|inlineStyleRanges|entityRanges|data|5210280|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|code-block|syntax|javascript|5210281|将为您提供重复行的ids。|5210282|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Doing

<pre><code>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
</code></pre>

will give you the duplicated rows' ids.

blocks|key|3800972|text|SELECT+++SocialSecurity_Number,+Count(*)+no_of_rows
FROM+++++SocialSecurity+
GROUP+BY+SocialSecurity_Number
HAVING+++Count(*)+>+1
Order+by+Count(*)+desc+|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|3800973|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>SELECT SocialSecurity_Number, Count(*) no_of_rows
FROM SocialSecurity 
GROUP BY SocialSecurity_Number
HAVING Count(*) &gt; 1
Order by Count(*) desc 
</code></pre>

blocks|key|734623|text|我通常使用Oracle+Analytic函数ROW_NUMBER()。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|734624|假设您想要检查在列(c1、c2、c3)上构建的唯一索引或主键的重复项。然后您就可以这样做了，调出由ROW_NUMBER()带来的行数为>1的行数ROWID。|style|CODE|BOLD|734625|Select+*+From+Table_With_Duplicates
++++++Where+Rowid+In
++++++++++++++++++++(Select+Rowid
+++++++++++++++++++++++From+(Select+Rowid,
++++++++++++++++++++++++++++++++++++ROW_NUMBER()+Over+(
++++++++++++++++++++++++++++++++++++++++++++Partition+By+c1+%7C%7C+c2+%7C%7C+c3
++++++++++++++++++++++++++++++++++++++++++++Order+By+c1+%7C%7C+c2+%7C%7C+c3
++++++++++++++++++++++++++++++++++++++++)+nbLines
+++++++++++++++++++++++++++++++From+Table_With_Duplicates)+t2
++++++++++++++++++++++Where+nbLines+>+1)|code-block|syntax|javascript|734626|entityMap|0|LINK|mutability|MUTABLE|url|https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions001.htm#i81407|1|http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions137.htm#i86310^0|5|F|0|M|C|1|0|A|2|D|2|G|2|1D|C|1V|2|20|5|20|5|0|0^^$0|@$1|2|3|4|5|6|7|X|8|@]|9|@$A|Y|B|Z|1|10]|$A|11|B|12|1|13]]|C|$]]|$1|D|3|E|5|6|7|14|8|@$A|15|B|16|F|G]|$A|17|B|18|F|G]|$A|19|B|1A|F|G]|$A|1B|B|1C|F|G]|$A|1D|B|1E|F|G]|$A|1F|B|1G|F|G]|$A|1H|B|1I|F|H]]|9|@]|C|$]]|$1|I|3|J|5|K|7|1J|8|@]|9|@]|C|$L|M]]|$1|N|3|-4|5|6|7|1K|8|@]|9|@]|C|$]]]|O|$P|$5|Q|R|S|C|$T|U]]|V|$5|Q|R|S|C|$T|W]]]]

I usually use <a href="https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions001.htm#i81407" rel="nofollow noreferrer">Oracle Analytic</a> function <a href="http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions137.htm#i86310" rel="nofollow noreferrer">ROW_NUMBER()</a>.

Say you want to check the duplicates you have regarding a unique index or primary key built on columns (<code>c1</code>, <code>c2</code>, <code>c3</code>). 
Then you will go this way, bringing up <code>ROWID</code> s of rows where the number of lines brought by <code>ROW_NUMBER()</code> is <code>&gt;1</code>:

<pre><code>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 &gt; 1)
</code></pre>

blocks|key|730683|text|我知道这是一个古老的帖子，但这可能会对某些人有所帮助。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|730684|如果在检查重复项时需要打印表的其他列，请使用以下内容：|730685|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;|code-block|syntax|javascript|730686|如果需要，还可以在where子句中添加一些额外的过滤器。|730687|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|O|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

I know its an old thread but this may help some one.

If you need to print other columns of the table while checking for duplicate use below:

<pre><code>select * from table where column_name in
(select ing.column_name from table ing group by ing.column_name having count(*) &gt; 1)
order by column_name desc;
</code></pre>

also can add some additional filters in the where clause if needed.

blocks|key|3801185|text|下面是一个执行此操作的SQL请求：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3801186|select+column_name,+count(1)
from+table
group+by+column_name
having+count+(column_name)+>+1;|code-block|syntax|javascript|3801187|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Here is an SQL request to do that:

<pre><code>select column_name, count(1)
from table
group by column_name
having count (column_name) &gt; 1;
</code></pre>

blocks|key|5211196|text|1.解决方案|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|5211197|select+*+from+emp
++++where+rowid+not+in
++++(select+max(rowid)+from+emp+group+by+empno);|code-block|syntax|javascript|5211198|entityMap^0|0|6|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|P|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|D|@]|E|$]]]|L|$]]

1. solution

<pre><code>select * from emp
 where rowid not in
 (select max(rowid) from emp group by empno);
</code></pre>

blocks|key|5211164|text|你也可以尝试这样在一个表中列出所有重复的值，比如reqitem|type|unstyled|depth|inlineStyleRanges|entityRanges|data|5211165|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;|code-block|syntax|javascript|5211166|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Also u can try something like this to list all duplicate values in a table say reqitem

<pre><code>SELECT count(poid) 
FROM poitem 
WHERE poid = 50 
AND rownum &lt; 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) &gt; 1;
</code></pre>

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?

For example: I have a <code>JOBS</code> table with the column <code>JOB_NUMBER</code>. How can I find out if I have any duplicate <code>JOB_NUMBER</code>s, and how many times they're duplicated?

How do I find duplicate values in a table in Oracle?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

哪条最简单的SQL语句将返回给定列的重复值以及它们在Oracle数据库表中出现的次数？例如:我有一个列为JOB_NUMBER的JOBS表。我怎样才能知道我是否有重复的JOB_NUMBER，以及它们被复制了多少次？

问如何在Oracle的表中查找重复的值？
EN

回答 13

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在Oracle的表中查找重复的值？EN

回答 13

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在Oracle的表中查找重复的值？
EN