Oracle найти дубли в таблице

Обновлено: 05.07.2024

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

Например: у меня есть таблица JOBS со столбцом JOB_NUMBER . Как я могу узнать, есть ли у меня дубликаты JOB_NUMBER с, и сколько раз они дублируются?

Работает нормально (достаточно быстро) при наличии индекса на column_name . И это лучший способ удалить или обновить дублирующиеся строки.

1. решение

ответ дан Bill the Lizard, с репутацией 283013, 12.09.2008

Самое простое, что я могу придумать:

ответ дан JosephStyons, с репутацией 35927, 12.09.2008

GROUP BY HAVING COUNT(*) > 1;

Чтобы ответить на пример выше, это будет выглядеть так:

Я знаю, что это старая тема, но это может помочь кому-то.

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

также может добавить некоторые дополнительные фильтры в предложении where, если это необходимо.

даст вам идентификаторы дублированных строк.

Допустим, вы хотите проверить имеющиеся у вас дубликаты относительно уникального индекса или первичного ключа, построенного на столбцах ( c1 , c2 , c3 ). Затем вы пойдете этим путем, получив ROWID с строк, где число строк, принесенных ROW_NUMBER() , равно >1 :

Вот SQL-запрос для этого:

ответ дан Chaminda Dilshan, с репутацией 1, 12.01.2018

Вам не нужно даже указывать количество в возвращаемых столбцах, если вам не нужно знать фактическое количество дубликатов. е. г.

Также вы можете попробовать что-то вроде этого, чтобы перечислить все повторяющиеся значения в таблице, скажем, reqitem

В случае, когда несколько столбцов идентифицируют уникальную строку (например, г таблица отношений) там можно использовать следующие

Использовать идентификатор строки е. г. emp_dept (empid, deptid, startdate, enddate) предположим, что empid и deptid уникальны и определяют строку в этом случае

и если такая таблица имеет первичный ключ, тогда используйте первичный ключ вместо rowid, e. Идентификатор пользователя pk, то

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

Создание и заполнение таблицы с дублирующимися значениями.
Поиск строк - дубликатов:
Удаление строк дубликатов:

Если в таблице нет уникального ключа или индекса, по которому можно отсеивать дубликаты (в примере используется уникальность поля TEST_DUPLICATE.duplicate_id), то можно использовать псевдо столбец ROWID, значения которого гарантированно уникальны, т.е., например, первый запрос на удаление дубликатов через NOT IN может выглядеть так:

Добавление от Деев И. Удаление дубликатов через EXCEPTIONS INTO :

Подскажите,пожалуйста подробно что делает этот кусочек
"row_number() over (partition by value order by duplicate_id"

Прочитайте пару статеек про аналитические функции(Можно во 2-ом томе Кайта)

Подскажите,пожалуйста подробно что делает этот кусочек
"row_number() over (partition by value order by duplicate_id"

можно исп-ть либо min() либо first_value() в зависимости от задачи

delete from table t
where t.rowid in (select rw from (select rowid as rw
min(rowid) over(partition by <list unique colums>) as min_rw
from table
) where rw <> min_rw)

delete from shit where rowid not in (select min(rowid) from shit group by value)

delete from shit
where rowid in
(select a.rid
from (select rownum rn, rowid rid, s.* from shit s) a
where exists (select 1
from (select rownum rn, rowid rid, t.* from shit t) b
where b.value = a.value
and b.rid <> a.rid
and b.rn > a.rn))

delete from shit where rowid not in (select min(rowid) from shit group by value)

delete from shit
where rowid in
(select a.rid
from (select rownum rn, rowid rid, s.* from shit s) a
where exists (select 1
from (select rownum rn, rowid rid, t.* from shit t) b
where b.value = a.value
and b.rid <> a.rid
and b.rn > a.rn))

попробовал последнее решение от Деев И.
insert into drop_test select object_id, object_name from user_objects where rownum <301

ORA-00001: нарушено ограничение уникальности (SYSMAN.DROP_TEST_PK)
еще на генерации
где то надо подкрутить настройку
наверно чтобы давало вставлять записи с всеми одинаковыми полями -
но где вот ?

Можно использовать гибрид с row_number и rowid
delete from
Table1 t1
where t1.rowid in (select ri
from
(select rowid as ri, t1.ROW_ID, row_number() over (partition by t1.ROW_ID order by t1.column1) as rw
from Table1 t1
where t1.column1 = value)
where rw > 1);

Проблема интересная - я с ней столкнулся и только благодаря delete TEST_DUPLICATE
where rowid not in (select min(rowid)
from TEST_DUPLICATE
group by value);
смог ее решить.
Остальные решения при реальной проверке на 34 тыс
записей не работают :(.
Извините, но перед тем как себя хвалить (твёрдая пятёрка)))) нужно бы проверить.
А, в целом, большое спасибо - помощь реальная.
С уважением, Tolik_lv.

а что за функция такая LAG ?

и за чем в оптимизированном методе поиска дубликатов используется (t1.rowid!=t2.rowid). на сколько я знаю rowid уникальна в пределах всей базы данных.

Мой метод поиска.
DELETE FROM tmp_data_change
WHERE ROWID IN (
SELECT LAG (ROWID) OVER (PARTITION BY market_key, data_key ORDER BY market_key,
data_key)
FROM tmp_data_change)

Почти аналогичен методу с row_number()

select *
from table_name t1
where exists (
select *
from table_name t2
where ( (t1.column_name = t2.column_name) and (t1.rowid != t2.rowid) )
)
order by group --для наглядности :-)
/

метод поиска строк дубликатов находящийся выше - не оптимизирован, сегодня я родил метод оптимизированный по времени исполнения (он не использует group by):

select *
from table_name t1
where (
select *
from table_name t2
where ( (t1.column_name = t2.column_name) and (t1.rowid != t2.rowid) )
)
oreder by group --для наглядности :-)
/

твёрдая пятёрка)))
*хотя коментиков моно и поболе)))

-1 (или +1) популярный запрос на собеседовании :-)

2gerf
Такого не бывает. Хотя бы исходя из теории реляционных таблиц.

Этот запрос даст нам Джона, Сэма, Тома, Тома, потому что все они имеют одинаковый email .

Однако я хочу получить дубликаты с тем же email и name .

То есть, я хочу получить "Том", "Том".

Причина, в которой я нуждаюсь в этом: я допустил ошибку и разрешил вставлять повторяющиеся name и значения email . Теперь мне нужно удалить/изменить дубликаты, поэтому мне нужно сначала их найти.

ОТВЕТЫ

Ответ 1

Просто группируйтесь на обоих столбцах.

Примечание: более старый стандарт ANSI должен иметь все неагрегированные столбцы в GROUP BY, но это изменилось с идеей "функциональной зависимости":

В теории реляционных баз данных функциональная зависимость представляет собой ограничение между двумя наборами атрибутов в отношении из базы данных. Другими словами, функциональная зависимость - это ограничение, которое описывает взаимосвязь между атрибутами в отношении.

  • Последние PostgreSQL поддерживает его.
  • SQL Server (как на SQL Server 2017) все еще требует наличия всех неагрегированных столбцов в GROUP BY.
  • MySQL непредсказуем, и вам нужно sql_mode=only_full_group_by :
    • GROUP BY lname ORDER BY показывает неправильные результаты;
    • Это наименее затратная совокупная функция при отсутствии ЛЮБОГО() (см. Комментарии в принятом ответе).

    Ответ 2

    если вы хотите, чтобы идентификаторы дубликатов использовали это:

    для удаления дубликатов попробуйте:

    Ответ 3

    Ответ 4

    Если вы хотите удалить дубликаты, здесь гораздо более простой способ сделать это, чем найти четные/нечетные строки в тройной выбор:

    И чтобы удалить:

    Намного легче читать и понимать IMHO

    Примечание. Единственная проблема заключается в том, что вы должны выполнить запрос до тех пор, пока не удалите строки, поскольку каждый раз удаляйте только по 1 каждого дубликата

    Ответ 5

    Ответ 6

    Ответ 7

    Немного поздно на вечеринку, но я нашел действительно крутое обходное решение для поиска всех повторяющихся идентификаторов:

    Ответ 8

    попробуйте этот код

    Ответ 9

    В случае, если вы работаете с Oracle, этот способ был бы предпочтительнее:

    Ответ 10

    Это выбирает/удаляет все повторяющиеся записи, кроме одной записи из каждой группы дубликатов. Таким образом, удаление удаляет все уникальные записи + одну запись из каждой группы дубликатов.

    Помните о большем количестве записей, это может вызвать проблемы с производительностью.

    Ответ 11

    Ответ 12

    Если вы хотите увидеть, есть ли в вашей таблице повторяющиеся строки, я использовал ниже Query:

    Ответ 13

    Как мы можем считать дублированные значения? либо он повторяется 2 раза или больше 2. просто считайте их, а не групповыми.

    так же просто, как

    Ответ 14

    Это легкая вещь, которую я придумал. Он использует общее табличное выражение (CTE) и окно раздела (я думаю, что эти функции находятся в SQL 2008 и последующих версиях).

    В этом примере найдены все ученики с дублирующимся именем и dob. Поля, которые вы хотите проверить на дублирование, перечислены в предложении OVER. Вы можете включать любые другие поля, которые вы хотите в проекции.

    Ответ 15

    Ответ 16

    SELECT id, COUNT(id) FROM table1 GROUP BY id HAVING COUNT(id)>1;

    Я думаю, что это будет работать правильно, чтобы искать повторяющиеся значения в определенном столбце.

    Ответ 17

    Ответ 18

    Используя CTE, мы также можем найти повторяющееся значение

    Ответ 19

    Это также должно работать, возможно, попробуйте.

    Особенно хорошо в вашем случае. Если вы ищете дубликаты, у которых есть префикс или общие изменения, например, например. новый домен в почте. то вы можете использовать replace() в этих столбцах

    Ответ 20

    Если вы хотите найти повторяющиеся данные (по одному или нескольким критериям) и выбрать фактические строки.

    Ответ 21

    Ответ 22

    SELECT column_name,COUNT(*) FROM TABLE_NAME GROUP BY column1, HAVING COUNT(*) > 1;

    Ответ 23

    Удалить записи, имена которых повторяются

    Ответ 24

    Для проверки из дубликата записи в таблице.

    Удалить дубликат записи в таблице.

    Ответ 25

    Мы можем использовать здесь, которые работают с агрегатными функциями, как показано ниже

    Здесь в качестве двух полей id_account и data используются Count (*). Таким образом, он выдаст все записи, которые имеют более одного раза одинаковые значения в обоих столбцах.

    Мы по какой-то причине ошибочно пропустили добавление каких-либо ограничений в таблицу SQL-сервера, и записи были вставлены дубликаты во все столбцы с интерфейсным приложением. Затем мы можем использовать запрос ниже, чтобы удалить дубликат запроса из таблицы.

    Здесь мы взяли все отдельные записи оригинальной таблицы и удалили записи исходной таблицы. Мы снова вставили все различные значения из новой таблицы в исходную таблицу, а затем удалили новую таблицу.

    Ответ 26

    Удалить записи, имена которых повторяются

    УДАЛИТЬ ИЗ CTE ГДЕ T> 1

    Ответ 27

    Вы можете использовать ключевое слово SELECT DISTINCT, чтобы избавиться от дубликатов. Вы также можете отфильтровать по имени и получить всех с этим именем на столе.

    Какой самый простой оператор SQL, который вернет повторяющиеся значения для данного столбца и количество их вхождений в таблице базы данных Oracle?

    Например: у меня есть таблица JOBS со столбцом JOB_NUMBER . Как я могу узнать, есть ли у меня дубликат JOB_NUMBER s и сколько раз они дублируются?

    ОТВЕТЫ

    Ответ 1

    Ответ 2

    Работает нормально (достаточно быстро), когда есть индекс на column_name . И это лучший способ удалить или обновить повторяющиеся строки.

    Ответ 3

    Проще всего я могу думать:

    Ответ 4

    Вам не нужно даже иметь счет в возвращаемых столбцах, если вам не нужно знать фактическое количество дубликатов. например.

    Ответ 5

    Чтобы ответить на приведенный выше пример, это будет выглядеть так:

    Ответ 6

    В случае, когда несколько столбцов идентифицируют уникальную строку (например, таблицу отношений), вы можете использовать следующие

    Использовать идентификатор строки например emp_dept (empid, deptid, startdate, enddate) предположим, что empid и deptid уникальны и идентифицируют строку в этом случае

    и если в такой таблице есть первичный ключ, используйте первичный ключ вместо rowid, например id - pk, затем

    Ответ 7

    предоставит вам идентификаторы дублированных строк.

    Ответ 8

    Ответ 9

    Предположим, вы хотите проверить дубликаты, которые у вас есть относительно уникального индекса или первичного ключа, построенного на столбцах ( c1 , c2 , c3 ). Затем вы пройдете этот путь, подняв строки ROWID , где количество строк, приведённых ROW_NUMBER() , равно >1 :

    Ответ 10

    Я знаю, что это старая тема, но это может помочь кому-то.

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

    также может добавить некоторые дополнительные фильтры в предложении where, если это необходимо.

    Ответ 11

    Вот SQL-запрос для этого:

    Ответ 12

    Также вы можете попробовать что-то подобное, чтобы перечислить все повторяющиеся значения в таблице, скажем, reqitem

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

    Например: у меня есть JOBS таблица с колонкой JOB_NUMBER . Как я могу узнать, есть ли у меня дубликаты JOB_NUMBER , и сколько раз они дублируются?

    Спасибо - это ответ, который я только что нашел, и ты побил меня, чтобы опубликовать его здесь! : o) Пожалуйста. Теперь я собираюсь опубликовать свой собственный вопрос о различиях между количеством (столбец) и количеством (*). :) +1 через 4 года, все еще работает хорошо и может быть отрегулирован для выбора нескольких столбцов, если они также есть в group by , как в: select column_one, column_two, count(*) from tablename group by column_one, column_two having count(column_one) > 1; и т. Д. +1 более 8 лет спустя, по-прежнему хорошо работает как для последних версий Oracle, так и для MySQL (удалите пробел после счетной функции в строке).

    Работает нормально (достаточно быстро), когда включен индекс column_name . И это лучший способ удалить или обновить дублирующиеся строки.

    +1 хорошо работает для дубликатов с несколькими столбцами (например, когда вы хотите добавить ограничение UNIQUE для нескольких столбцов), я нашел этот подход менее "жестким", чем метод GROUP BY, чтобы вывести список значений дубликатов полей + другие поля, если это необходимо. Просто чтобы уточнить (сначала это не было очевидно для меня), этот запрос возвращает только дубликаты, он не возвращает первую исходную запись, поэтому он хорошо работает для удаления дубликатов, основываясь на уникальном ограничении для более чем 1 столбец. С помощью этого запроса вы можете выбрать идентификаторы дубликатов, а затем использовать их для удаления дубликатов. если вы измените <на! =, вы получите все записи, которые дублируются. не только 2-я или 3-я запись

    Самое простое, что я могу придумать:

    выберите * из заданий, в которых задан номер задания (выберите номер задания из группы заданий по номеру задания, имеющему количество (*)> 1)

    Вам не нужно даже указывать количество в возвращаемых столбцах, если вам не нужно знать фактическое количество дубликатов. например

    Чтобы ответить на пример выше, это будет выглядеть так:

    В случае, когда несколько столбцов идентифицируют уникальную строку (например, таблицу отношений), вы можете использовать следующие

    Используйте идентификатор строки, например, emp_dept (empid, deptid, startdate, enddate), предположите, что empid и deptid уникальны, и в этом случае идентифицируйте строку

    и если такая таблица имеет первичный ключ, тогда используйте rowid вместо rowid, например, id is pk тогда

    Читайте также: