repetir - sql eliminar registros repetidos dejando solo 1

¿Cómo encuentro entradas duplicadas en una tabla de base de datos?

Question

La siguiente consulta mostrará todos los números decimales de Dewey que se han duplicado en la tabla "libro":

SELECT dewey_number,
COUNT(dewey_number) AS NumOccurrences
FROM book
GROUP BY dewey_number
HAVING ( COUNT(dewey_number) > 1 )

Sin embargo, lo que me gustaría hacer es que mi consulta muestre el nombre de los autores asociados con la entrada duplicada (la tabla "libro" y la tabla "autor" están conectadas por "author_id"). En otras palabras, la consulta anterior arrojaría lo siguiente:

dewey_number | NumOccurrences
------------------------------
5000 | 2
9090 | 3

Lo que me gustaría que mostraran los resultados es algo similar a lo siguiente:

author_last_name | dewey_number | NumOccurrences
-------------------------------------------------
Smith | 5000 | 2
Jones | 5000 | 2
Jackson | 9090 | 3
Johnson | 9090 | 3
Jeffers | 9090 | 3

Cualquier ayuda que puedas darnos es muy apreciada. Y, en caso de que entre en juego, estoy usando una base de datos Postgresql.

ACTUALIZACIÓN : tenga en cuenta que "author_last_name" no está en la tabla "libro".

Answer 1

Probablemente quieras esto

SELECT dewey_number, author_last_name,
COUNT(dewey_number) AS NumOccurrences
FROM book
GROUP BY dewey_number,author_last_name
HAVING ( COUNT(dewey_number) > 1 )

Answer 2

Una consulta anidada puede hacer el trabajo.

SELECT author_last_name, dewey_number, NumOccurrences
FROM author INNER JOIN
( SELECT author_id, dewey_number, COUNT(dewey_number) AS NumOccurrences
FROM book
GROUP BY author_id, dewey_number
HAVING ( COUNT(dewey_number) > 1 ) ) AS duplicates
ON author.id = duplicates.author_id

(No sé si esta es la forma más rápida de lograr lo que quieres).

Actualización: aquí están mis datos

SELECT * FROM author;
id | author_last_name
----+------------------
1 | Fowler
2 | Knuth
3 | Lang
SELECT * FROM book;
id | author_id | dewey_number | title
----+-----------+--------------+------------------------
1 | 1 | 600 | Refactoring
2 | 1 | 600 | Refactoring
3 | 1 | 600 | Analysis Patterns
4 | 2 | 600 | TAOCP vol. 1
5 | 2 | 600 | TAOCP vol. 1
6 | 2 | 600 | TAOCP vol. 2
7 | 3 | 500 | Algebra
8 | 3 | 500 | Undergraduate Analysis
9 | 1 | 600 | Refactoring
10 | 2 | 500 | Concrete Mathematics
11 | 2 | 500 | Concrete Mathematics
12 | 2 | 500 | Concrete Mathematics

Y aquí está el resultado de la consulta anterior:

 author_last_name | dewey_number | numoccurrences
------------------+--------------+----------------
Fowler | 600 | 4
Knuth | 600 | 3
Knuth | 500 | 3
Lang | 500 | 2

Answer 3

SELECT dewey_number, author_last_name,
COUNT(dewey_number) AS NumOccurrences
FROM book
JOIN author USING (author_id)
GROUP BY dewey_number,author_last_name
HAVING COUNT(dewey_number) > 1

Si book.author_id puede ser nulo, cambie la unión a:

LEFT OUTER JOIN author USING (author_id)

Si la columna author_id tiene un nombre diferente en cada tabla, entonces no puede usar USING, use ON en su lugar:

JOIN author ON author.id = book.author_id

o

LEFT OUTER JOIN author ON author.id = book.author_id

Answer 4

select author_name,dewey_number,Num_of_occur
from author a,(select author_id,dewey_number,count(dewey_number) Num_of_occur
from book
group by author_id,dewey_number
having count(dewey_number) > 1) dup
where a.author_id = dup.author_id

Answer 5

La forma más simple y efectiva que encontré es la que se muestra a continuación:

SELECT
p.id
, p.full_name
, (SELECT count(id) FROM tbl_documents as t where t.person_id = p.id) as rows
FROM tbl_people as p
WHERE
p.id
IN (SELECT d.person_id FROM tbl_documents as d
GROUP BY d.person_id HAVING count(d.id) > 1)
ORDER BY
p.full_name