Как найти повторяющиеся записи в таблице базы данных?

В следующем запросе будут отображаться все десятичные числа Dewey, которые были дублированы в таблице "book":

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

Однако то, что я хотел бы сделать, это указать, что мой запрос отображает имя авторов, связанных с дублированной записью (таблица "книга" и "авторская" таблица связаны "author_id" ). Другими словами, указанный выше запрос даст следующее:

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

Что бы я хотел, чтобы результаты отображались, это похоже на следующее:

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

Любая помощь, которую вы можете предоставить, очень ценится. И, если он вступает в игру, я использую DB Postgresql.

ОБНОВЛЕНИЕ. Обратите внимание, что "author_last_name" не находится в таблице "book".

Ответы

Ответ 1

Вложенный запрос может выполнить задание.

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

(Я не знаю, является ли это самым быстрым способом достижения желаемого.)

Обновление: вот мои данные

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

И вот результат вышеуказанного запроса:

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

Ответ 2

Вероятно, вам нужен этот

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 )

Ответ 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

Если book.author_id может быть нулевым, измените соединение на:

LEFT OUTER JOIN author USING (author_id)

Если столбец author_id имеет другое имя в каждой таблице, вы не можете использовать USING, вместо этого используйте ON:

JOIN author ON author.id = book.author_id

или

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

Ответ 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

Ответ 5

Самый простой и эффективный способ, который я нашел, показан ниже:

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