SQL: как найти дубликаты на основе двух полей?

У меня есть строки в таблице базы данных Oracle, которые должны быть уникальными для комбинации двух полей, но уникальный ограничитель не настроен в таблице, поэтому мне нужно найти все строки, которые сами нарушают ограничение, используя SQL. К сожалению, мои скудные навыки SQL не справляются с задачей.

Моя таблица имеет три столбца, которые имеют значение: entity_id, station_id и obs_year. Для каждой строки комбинация station_id и obs_year должна быть уникальной, и я хочу узнать, есть ли строки, которые нарушают это, очищая их SQL-запросом.

Я пробовал следующий SQL (предложенный этот предыдущий вопрос), но он не работает для меня (я получаю столбец ORA-00918 двусмысленно):

SELECT
entity_id, station_id, obs_year
FROM
mytable t1
INNER JOIN (
SELECT entity_id, station_id, obs_year FROM mytable 
GROUP BY entity_id, station_id, obs_year HAVING COUNT(*) > 1) dupes 
ON 
t1.station_id = dupes.station_id AND
t1.obs_year = dupes.obs_year

Может кто-нибудь предложить, что я делаю неправильно, и/или как это решить?

Ответ 1

SELECT  *
FROM    (
        SELECT  t.*, ROW_NUMBER() OVER (PARTITION BY station_id, obs_year ORDER BY entity_id) AS rn
        FROM    mytable t
        )
WHERE   rn > 1

Ответ 2

SELECT entity_id, station_id, obs_year
FROM mytable t1
WHERE EXISTS (SELECT 1 from mytable t2 Where
       t1.station_id = t2.station_id
       AND t1.obs_year = t2.obs_year
       AND t1.RowId <> t2.RowId)

Ответ 3

Повторно написать запрос

SELECT
t1.entity_id, t1.station_id, t1.obs_year
FROM
mytable t1
INNER JOIN (
SELECT entity_id, station_id, obs_year FROM mytable 
GROUP BY entity_id, station_id, obs_year HAVING COUNT(*) > 1) dupes 
ON 
t1.station_id = dupes.station_id AND
t1.obs_year = dupes.obs_year

Я думаю, что неоднозначная ошибка столбца (ORA-00918) заключалась в том, что вы были select столбцами, имена которых отображались как в таблице, так и в подзапросе, но вы не указали, хотите ли вы ее от dupes или от mytable (с псевдонимом t1).

Ответ 4

Измените 3 поля в начальном выборе, чтобы быть

SELECT
t1.entity_id, t1.station_id, t1.obs_year

Ответ 5

Не удалось создать новую таблицу, которая включает в себя уникальное ограничение, а затем скопировать данные по строке, игнорируя ошибки?

Ответ 6

Вам нужно указать таблицу для столбцов основного выбора. Кроме того, предполагая, что entity_id является уникальным ключом для mytable и не имеет отношения к поиску дубликатов, вы не должны группировать его в подзапросах dupes.

Try:

SELECT t1.entity_id, t1.station_id, t1.obs_year
FROM mytable t1
INNER JOIN (
SELECT station_id, obs_year FROM mytable 
GROUP BY station_id, obs_year HAVING COUNT(*) > 1) dupes 
ON 
t1.station_id = dupes.station_id AND
t1.obs_year = dupes.obs_year

Ответ 7

SELECT  *
FROM    (
        SELECT  t.*, ROW_NUMBER() OVER (PARTITION BY station_id, obs_year ORDER BY entity_id) AS rn
        FROM    mytable t
        )
WHERE   rn > 1

Quassnoi является наиболее эффективным для больших таблиц. У меня был этот анализ стоимости:

SELECT a.dist_code, a.book_date, a.book_no
FROM trn_refil_book a
WHERE EXISTS (SELECT 1 from trn_refil_book b Where
       a.dist_code = b.dist_code and a.book_date = b.book_date and a.book_no = b.book_no
       AND a.RowId <> b.RowId)
       ;

дал стоимость 1322341

SELECT a.dist_code, a.book_date, a.book_no
FROM trn_refil_book a
INNER JOIN (
SELECT b.dist_code, b.book_date, b.book_no FROM trn_refil_book b 
GROUP BY b.dist_code, b.book_date, b.book_no HAVING COUNT(*) > 1) c 
ON 
 a.dist_code = c.dist_code and a.book_date = c.book_date and a.book_no = c.book_no
;

предоставила стоимость 1271699

while

SELECT  dist_code, book_date, book_no
FROM    (
        SELECT  t.dist_code, t.book_date, t.book_no, ROW_NUMBER() OVER (PARTITION BY t.book_date, t.book_no
          ORDER BY t.dist_code) AS rn
        FROM    trn_refil_book t
        ) p
WHERE   p.rn > 1
;

дал стоимость 1021984

Таблица не была проиндексирована....

Ответ 8

  SELECT entity_id, station_id, obs_year
    FROM mytable
GROUP BY entity_id, station_id, obs_year
HAVING COUNT(*) > 1

Укажите поля для поиска дубликатов как для SELECT, так и для GROUP BY.

Работает с помощью GROUP BY, чтобы найти любые строки, которые соответствуют любым другим строкам на основе указанных столбцов. HAVING COUNT(*) > 1 говорит, что нас интересуют только любые строки, которые происходят более одного раза (и, следовательно, дублируются)

Ответ 9

Я думал, что многие решения здесь были громоздкими и сложными для понимания, так как у меня было ограничение первичного ключа из 3 столбцов, и мне нужно было найти дубликаты. Так вот вариант

SELECT id, name, value, COUNT(*) FROM db_name.table_name
GROUP BY id, name, value
HAVING COUNT(*) > 1