Como faço para encontrar valores duplicados em uma tabela no Oracle?

Qual é a instrução SQL mais simples que retornará os valores duplicados para uma determinada coluna e a contagem de suas ocorrências em uma tabela de database Oracle?

Por exemplo: eu tenho uma tabela JOBS com a coluna JOB_NUMBER . Como posso descobrir se tenho algum JOB_NUMBER duplicado e quantas vezes ele é duplicado?

 select column_name, count(column_name) from table group by column_name having count (column_name) > 1; 

Outra maneira:

 SELECT * FROM TABLE A WHERE EXISTS ( SELECT 1 FROM TABLE WHERE COLUMN_NAME = A.COLUMN_NAME AND ROWID < A.ROWID ) 

Funciona bem (rápido o suficiente) quando há índice em column_name . E é melhor maneira de excluir ou atualizar linhas duplicadas.

Mais simples que eu posso pensar:

 select job_number, count(*) from jobs group by job_number having count(*) > 1; 

Você não precisa nem ter a contagem nas colunas retornadas se não precisar saber o número real de duplicatas. por exemplo

 SELECT column_name FROM table GROUP BY column_name HAVING COUNT(*) > 1 

E se:

 SELECT , count(*) FROM  GROUP BY  HAVING COUNT(*) > 1;

Para responder ao exemplo acima, seria parecido com:

 SELECT job_number, count(*) FROM jobs GROUP BY job_number HAVING COUNT(*) > 1; 

No caso em que várias colunas identificam uma linha única (por exemplo, tabela de relações), você pode usar as seguintes

Use id de linha, por exemplo, emp_dept (empid, deptid, startdate, enddate) supondo que o empid e o deptid sejam únicos e identifiquem a linha nesse caso

 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); 

e se tal tabela tiver chave primária, então use a chave primária ao invés de rowid, por exemplo id é pk então

 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); 

Fazendo

 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 

lhe dará os ids das linhas duplicadas.

 SELECT SocialSecurity_Number, Count(*) no_of_rows FROM SocialSecurity GROUP BY SocialSecurity_Number HAVING Count(*) > 1 Order by Count(*) desc 

Eu costumo usar a function Oracle Analytic ROW_NUMBER () .

Digamos que você queira verificar as duplicatas que você tem em relação a um índice exclusivo ou chave primária construída em colunas ( c1 , c2 , c3 ). Então você irá por este caminho, trazendo ROWID s de linhas onde o número de linhas trazidas por 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) 

Aqui está uma solicitação de SQL para fazer isso:

 select column_name, count(1) from table group by column_name having count (column_name) > 1; 

Eu sei que é um segmento antigo, mas isso pode ajudar alguém.

Se você precisar imprimir outras colunas da tabela enquanto verifica se há uso duplicado abaixo:

 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; 

também pode adicionar alguns filtros adicionais na cláusula where, se necessário.

1. solução

 select * from emp where rowid not in (select max(rowid) from emp group by empno); 

Também vc pode tentar algo assim para listar todos os valores duplicados em uma tabela dizem 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;