Oracle найти дубли в таблице
Как я могу запросить базу данных Oracle для отображения имен всех таблиц в ней?
Для лучшего просмотра с помощью sqlplus
Если вы используете sqlplus , вы можете сначала настроить несколько параметров для более удобного просмотра, если ваши столбцы становятся искалеченными (эти переменные не должны сохраняться после выхода из сеанса sqlplus ):
1. USING GROUP BY CLAUSE
- the GROUP BY clause groups the rows into groups by values in both name and email columns.
- Then, the COUNT() function returns the number of occurrences of each group (name,email).
- Then, the HAVING clause keeps only duplicate groups, which are groups that have more than one occurrence.
ОТВЕТЫ
Ответ 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, чтобы избавиться от дубликатов. Вы также можете отфильтровать по имени и получить всех с этим именем на столе.
Результаты
Это должно привести к тому, что выглядит довольно приемлемым, например:
Ответ 5
Простой запрос для выбора таблиц для текущего пользователя:
Ответ 6
Ответ 7
Попробуйте просмотреть словарные данные ниже.
Ответ 8
Существует 3 данных для этого
DBA_TABLES описывает все реляционные таблицы в базе данных.
Описание реляционных таблиц, доступных пользователю
USER_TABLES описывает реляционные таблицы, принадлежащие текущему пользователю. Это представление не отображает столбец ВЛАДЕЛЕЦ.
Ответ 9
Попробуйте выбрать user_tables, в котором перечислены таблицы, принадлежащие текущему пользователю.
Ответ 10
С помощью любого из них вы можете выбрать:
Ответ 11
База данных Oracle для отображения имен всех таблиц, используя запрос ниже
Ответ 12
предоставляет все таблицы всех пользователей только в том случае, если пользователь, с которым вы вошли в систему, имеет привилегии sysdba .
Ответ 13
Ниже приведен закомментированный фрагмент SQL-запросов, описывающий, как можно использовать параметры:
Ответ 14
Вы можете использовать Oracle Data Dictionary, чтобы получить информацию об объектах oracle.
Вы можете получить список таблиц по-разному:
Затем вы можете получить столбцы таблицы, используя имя таблицы:
Затем вы можете получить список зависимостей (триггеры, представления и т.д.):
Затем вы можете получить текстовый источник этих объектов:
И вы можете использовать USER или ALL представления вместо DBA , если хотите.
Ответ 15
Я не нашел ответа, который указывал бы на использование
Ответ 16
Ответ 17
Мы можем получить все таблицы, включая детали столбцов, из запроса ниже:
Ответ 18
Следующий запрос содержит только список необходимых данных, тогда как другие ответы дали мне дополнительные данные, которые меня только смутили.
Ответ 19
Новая функция, доступная в SQLcl (это бесплатный интерфейс командной строки для Oracle Database),
Tables псевдоним.
Вот несколько примеров, показывающих использование и дополнительные аспекты функции. Сначала подключитесь к sql.exe командной строки sql ( sql.exe в windows). Рекомендуется вводить эту конкретную команду sqlcl перед выполнением любых других команд или запросов, которые отображают данные.
SQL> tables
Чтобы узнать, на что ссылается псевдоним tables , вы можете просто использовать alias List
Вам не нужно определять этот псевдоним, так как он используется по умолчанию в SQLcl. Если вы хотите получить список таблиц из определенной схемы, используя новый пользовательский псевдоним и передавая имя схемы в качестве аргумента привязки с отображением только набора столбцов, вы можете сделать это, используя
SQL> alias tables_schema = select owner, table_name, last_analyzed from all_tables where owner = :ownr;
После этого вы можете просто передать имя схемы в качестве аргумента.
SQL> tables_schema HR
Более сложный предопределенный псевдоним известен как Tables2 , который отображает несколько других столбцов.
Чтобы узнать, какой запрос выполняется в фоновом режиме, введите
Это покажет вам немного более сложный запрос вместе с предопределенными определениями column обычно используемыми в SQL * Plus.
Джефф Смит объясняет больше об псевдонимах здесь
Ответ 20
Я искал список всех имен столбцов, принадлежащих таблице схемы, отсортированный по порядку идентификатора столбца.
Вот запрос, который я использую: -
Ответ 21
Действительно, список таблиц можно получить с помощью запросов SQL. Это можно сделать также с помощью инструментов, которые позволяют создавать словари данных, такие как ERWIN, Toad Data Modeler или ERBuilder. С этими инструментами, в дополнение к именам таблиц, у вас будут поля, их типы, объекты типа (триггеры, последовательности, домен, представления. )
Ниже приведены шаги, которые необходимо выполнить для создания определения таблиц:
- Вы должны перепроектировать вашу базу данных
- В Toad Data Modeler: Меню → Файл → Реверс инжиниринг → Мастер реинжиниринга
- В ERBuilder Data Modeler: Меню → Файл → Обратный инженер
Ваша база данных будет отображаться в программном обеспечении в виде диаграммы отношений сущностей.
Я тестирую что-то в Oracle и заполняю таблицу некоторыми образцами данных, но в процессе я случайно загрузил дубликаты записей, поэтому теперь я не могу создать первичный ключ, используя некоторые столбцы.
Как удалить все повторяющиеся строки и оставить только одну из них?
использовать rowid псевдостолбцом.
здесь column1 , column2 и column3 составьте идентифицирующий ключ для каждой записи. Вы можете перечислить все свои колонки.
(исправлена отсутствующая скобка)
где столбец1, столбец2 и т. д. это ключ, который вы хотите использовать.
создать таблицу t2 как выбрать distinct * from t1;
для выбора дубликатов только формат запроса может быть:
таким образом, правильный запрос в соответствии с другим предложением:
этот запрос сохранит самую старую запись в базе данных для критериев, выбранных в WHERE CLAUSE .
Oracle Certified Associate (2008)
использование self join -
1. решение
2. натра
3.решение
4. решение
5. решение
и вы также можете удалить дубликаты записей другим способом
вы должны сделать небольшой блок pl / sql, используя курсор для цикла и удалить строки, которые вы не хотите сохранять. Например:
самый быстрый способ для действительно больших таблиц
создать таблицу исключений со структурой ниже: exceptions_table
если количество строк для удаления велико, то создайте новую таблицу (со всеми грантами и индексами) антисоединение с exceptions_table по rowid и переименуйте исходную таблицу в таблицу original_dups и переименуйте new_table_with_no_dups в исходную таблицу
Проверьте ниже сценарии -
вы увидите здесь 6-записи.
4.выполнить запрос ниже -вы увидите, что дубликаты записей были удалены.
Надеюсь, это решит ваш запрос. Спасибо :)что-то в примечание:
1)мы проверяем только дублирование полей в предложении partition.
2) Если у вас есть причина выбрать один дубликат над другими, вы можете использовать предложение order by, чтобы эта строка имела row_number () = 1
3) Вы можете изменить номер дубликата, сохраненный изменение предложения final where на "Where RN > N" с N >= 1 (я думал, что N = 0 удалит все строки, которые имеют дубликаты, но он просто удалит все строки).
4) добавлено поле Sum partition запрос CTE, который будет помечать каждую строку числовыми строками в группе. Поэтому для выбора строк с дубликатами, включая первый элемент, используйте "где cnt > 1".
This query will give us John, Sam, Tom, Tom because they all have the same email .
However, what I want is to get duplicates with the same email and name .
That is, I want to get "Tom", "Tom".
The reason I need this: I made a mistake, and allowed inserting duplicate name and email values. Now I need to remove/change the duplicates, so I need to find them first.
I don't think it would let you select name in your first sample since it's not in an aggregate function. "What is the count of matching email addresses and their name" is some tricky logic.
24 Answers 24
Use the rowid pseudocolumn.
Where column1 , column2 , and column3 make up the identifying key for each record. You might list all your columns.
+1 I had to find two duplicate phone numbers buried in 12,000+ records. Changed the DELETE to SELECT and this found them in seconds. Saved me a ton of time, thank you.
This approach did not work for me. I don't know why. When I replaced "DELETE" with "SELECT *", it returned the rows I wanted to delete, but when I executed with "DELETE" it was just hanging indefinitely.
If the select works, but the delete does not, that might be due to the size of the resulting subquery. It might be interesting to first do a create table with the subquery result, build an index on the min(rowid) column, and then run the delete statement.
(fixed the missing parenthesis)
Where column1, column2, etc. is the key you want to use.
create table t2 as select distinct * from t1;
not an answer - distinct * will take every record which differs in at least 1 symbol in 1 column. All you need is to select distinct values only from columns you want to make primary keys - Bill's answer is great example of this approach.
Another disadvantage of this method is that you have to create a copy of your table. For huge tables, this implies providing additional tablespace, and deleting or shrinking the tablespace after the copy. Bill's method has more benefits, and no additional disadvantages.
You should do a small pl/sql block using a cursor for loop and delete the rows you don't want to keep. For instance:
I believe the downvote is because you are using PL/SQL when you can do it in SQL, incase you are wondering.
Just because you can do it in SQL, doesn't mean its the only solution. I posted this solution, after I had seen the SQL-only solution. I thought down votes were for incorrect answers.
To select the duplicates only the query format can be:
So the correct query as per other suggestion is:
This query will keep the oldest record in the database for the criteria chosen in the WHERE CLAUSE .
Oracle Certified Associate (2008)
This blog post was really helpful for general cases:
If the rows are fully duplicated (all values in all columns can have copies) there are no columns to use! But to keep one you still need a unique identifier for each row in each group. Fortunately, Oracle already has something you can use. The rowid. All rows in Oracle have a rowid. This is a physical locator. That is, it states where on disk Oracle stores the row. This unique to each row. So you can use this value to identify and remove copies. To do this, replace min() with min(rowid) in the uncorrelated delete:
The Fastest way for really big tables
Create exception table with structure below: exceptions_table
Try create a unique constraint or primary key which will be violated by the duplicates. You will get an error message because you have duplicates. The exceptions table will contain the rowids for the duplicate rows.
Join your table with exceptions_table by rowid and delete dups
If the amount of rows to delete is big, then create a new table (with all grants and indexes) anti-joining with exceptions_table by rowid and rename the original table into original_dups table and rename new_table_with_no_dups into original table
Using self join-
dense rank with partition by gives the rank for duplicate rows with same number for example three rows having rank 1 , 1 , 1 and rowid create for every row as unic and we are trying to delete those rowids which are not matching.
1. solution
2. sloution
3.solution
4. solution
5. solution
and you can also delete duplicate records in another way
For best performance, here is what I wrote :
(see execution plan)Check below scripts -
You will see that duplicate records have been deleted.
Hope this solves your query. Thanks :)I didn't see any answers that use common table expressions and window functions. This is what I find easiest to work with.
Somethings to note:
1) We are only checking for duplication on the fields in the partition clause.
2) If you have some reason to pick one duplicate over others you can use an order by clause to make that row will have row_number() = 1
3) You can change the number duplicate preserved by changing the final where clause to "Where RN > N" with N >= 1 (I was thinking N = 0 would delete all rows that have duplicates, but it would just delete all rows).
4) Added the Sum partition field the CTE query which will tag each row with the number rows in the group. So to select rows with duplicates, including the first item use "WHERE cnt > 1".
Этот запрос даст нам Джона, Сэма, Тома, Тома, потому что все они имеют одинаковый email .
Однако я хочу получить дубликаты с тем же email и name .
То есть, я хочу получить "Том", "Том".
Причина, в которой я нуждаюсь в этом: я допустил ошибку и разрешил вставлять повторяющиеся name и значения email . Теперь мне нужно удалить/изменить дубликаты, поэтому мне нужно сначала их найти.
Показать все таблицы
Затем вы можете использовать что-то вроде этого, чтобы увидеть все имена таблиц:
ОТВЕТЫ
Ответ 1
Предполагается, что у вас есть доступ к представлению словаря данных DBA_TABLES . Если у вас нет этих привилегий, но они нуждаются в них, вы можете запросить, чтобы администратор базы данных явно предоставлял вам привилегии в этой таблице или что администратор базы данных предоставляет вам привилегию SELECT ANY DICTIONARY или SELECT_CATALOG_ROLE (любой из которых позволит вам для запроса любой таблицы словаря данных). Конечно, вы можете исключить некоторые схемы, такие как SYS и SYSTEM , которые имеют большое количество таблиц Oracle, которые вам, вероятно, не волнует.
В качестве альтернативы, если у вас нет доступа к DBA_TABLES , вы можете увидеть все таблицы, к которым ваша учетная запись имеет доступ, через представление ALL_TABLES :
Хотя это может быть подмножество таблиц, доступных в базе данных ( ALL_TABLES показывает вам информацию для всех таблиц, которым был предоставлен ваш пользователь).
Если вас интересуют только те таблицы, которые у вас есть, а не те, к которым у вас есть доступ, вы можете использовать USER_TABLES :
Так как USER_TABLES имеет только информацию о собственных таблицах, у нее нет столбца OWNER - владелец, по определению, вы.
Oracle также имеет ряд устаревших видов словарей данных - TAB , DICT , TABS и CAT например - которые могут быть использованы. В общем, я бы не предложил использовать эти устаревшие представления, если вам не нужно полностью использовать ваши сценарии для Oracle 6. Oracle не изменил эти представления за долгое время, поэтому у них часто возникают проблемы с новыми типами объектов. Например, представления TAB и CAT отображают информацию о таблицах, которые находятся в корзине пользователя, в то время как теги [DBA|ALL|USER]_TABLES все фильтруют их. CAT также показывает информацию о материализованных журналах просмотра с TABLE_TYPE в таблице "ТАБЛИЦА", которая вряд ли будет тем, что вы действительно хотите. DICT объединяет таблицы и синонимы и не говорит вам, кому принадлежит этот объект.
Ответ 2
Запрос user_tables и dba_tables не работает.
Это сделал:Ответ 3
Идя еще на один шаг, существует другое представление, называемое cols (all_tab_columns), которое может использоваться для определения того, какие таблицы содержат заданное имя столбца.
чтобы найти все таблицы, имеющие имя, начинающееся с EST, и столбцы, содержащие CALLREF в любом месте их имен.
Это может помочь при разработке тех столбцов, к которым вы хотите присоединиться, например, в зависимости от ваших соглашений об именах таблиц и столбцов.
Ответ 4
Creating Sample Data
I will start with setting up some sample data from this question only.
Не забывайте о представлениях
Имейте в виду, что некоторые "таблицы" могут быть "видами", поэтому вы также можете попробовать запустить что-то вроде:
2. Using CTE:
To return the entire row for each duplicate row, you join the result of the above query with the NewTable table using a common table expression (CTE):
3. Using ROW_NUMBER() function
- ROW_NUMBER() distributes rows of the NewTable table into partitions by values in the name and email columns. The duplicate rows will have repeated values in the name and email columns, but different row numbers
- Outer query removes the first row in each group.
Well Now I believe, you can have sound Idea of how to find duplicates and apply the logic to find duplicate in all possible scenarios. Thanks.
I'm testing something in Oracle and populated a table with some sample data, but in the process I accidentally loaded duplicate records, so now I can't create a primary key using some of the columns.
How can I delete all duplicate rows and leave only one of them?
38 Answers 38
Simply group on both of the columns.
Note: the older ANSI standard is to have all non-aggregated columns in the GROUP BY but this has changed with the idea of "functional dependency":
In relational database theory, a functional dependency is a constraint between two sets of attributes in a relation from a database. In other words, functional dependency is a constraint that describes the relationship between attributes in a relation.
Support is not consistent:
- Recent PostgreSQL supports it.
- SQL Server (as at SQL Server 2017) still requires all non-aggregated columns in the GROUP BY.
- MySQL is unpredictable and you need sql_mode=only_full_group_by :
-
; (see comments in accepted answer).
@gbn Is it possible to include the Id in the results? Then it would be easier to delete those duplicates afterwards.
@user797717: you'd need to have MIN(ID) and then delete for ID values not in the last if MIN(ID) values
Thanks so much for this, and yes it does work in Oracle, though I needed uniqueness of the condition, so rather than >1 =1
if you want the IDs of the dups use this:
to delete the duplicates try:
* Table names are case sensitivearray(3) < [0]=>string(5) "42000" [1]=> int(1064) [2]=> string(226) "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(PARTITION BY y.employee_id, y.leave_type_id ) AS RowRank ' at line 1" >
If you want to delete the duplicates, here's a much simpler way to do it than having to find even/odd rows into a triple sub-select:
And so to delete:
Much more easier to read and understand IMHO
Note: The only issue is that you have to execute the request until there is no rows deleted, since you delete only 1 of each duplicate each time
Fails for me. I get: "DBD::CSV::st execute failed: Use of uninitialized value $_[1] in hash element at /Users/hornenj/perl5/perlbrew/perls/perl-5.26.0/lib/site_perl/5.26.0/SQL/Eval.pm line 43"
I think that where clause should be " u.name = u2.name AND u.email = u2.email AND (u.id > u2.id OR u2.id > u.id)" isn't it?
In contrast to other answers you can view the whole records containing all columns if there are any. In the PARTITION BY part of row_number function choose the desired unique/duplicit columns.
When you want to select ALL duplicated records with ALL fields you can write it like
A slight change to SELECT * helped me solved an hour search. I have never used the OVER(PARTITION BY before. I never cease to be amazed at how many ways to do the same thing in SQL!
A little late to the party but I found a really cool workaround to finding all duplicate IDs:
Keep in mind that GROUP_CONCAT will stop after some predetermined length, so you might not get all the id s.
This selects/deletes all duplicate records except one record from each group of duplicates. So, the delete leaves all unique records + one record from each group of the duplicates.
Be aware of larger amounts of records, it can cause performance problems.
There is neither table 'cities' nor update clause. What do you mean? Where is an error in the delete query?
In case you work with Oracle, this way would be preferable:
Code only answers are frowned upon on Stack Overflow, could you explain why this answers the question?
@RichBenner: I didn't find the response such as, each & every row in the result and which tells us which all are duplicate rows and which are not in one glance and that to not group by, because if we want to combine this query with any other query group by is not a good option.
Adding Id to the select statement and filtering on duplicated , it give you the possibility to delete the duplicated ids and keep on of each.
If you wish to see if there is any duplicate rows in your table, I used below Query:
SELECT id, COUNT(id) FROM table1 GROUP BY id HAVING COUNT(id)>1;
I think this will work properly to search repeated values in a particular column.
This doesn't quite add anything to the top answer, and technically doesn't even really differ from the code OP's posted in the question.
This is the easy thing I've come up with. It uses a common table expression (CTE) and a partition window (I think these features are in SQL 2008 and later).
This example finds all students with duplicate name and dob. The fields you want to check for duplication go in the OVER clause. You can include any other fields you want in the projection.
How we can count the duplicated values?? either it is repeated 2 times or greater than 2. just count them, not group wise.
How would this work for the question as asked? This does not give rows that duplicate information in multiple columns (e.g. "email" and "name") in different rows.
By Using CTE also we can find duplicate value like this
I think this will help you
Well this question has been answered very neatly in all the above answers. But I would like to list all the possible manners, we can do this in various ways which may impart the understanding how we can do it and seeker can pick one of the solution which best fits to his/her need as this is one of the most common query SQL developer come across different business usecases or sometime in interviews as well.
Показать таблицы, которыми вы владеете
Как упоминает @Justin Cave, вы можете использовать это, чтобы отображать только те таблицы, которые у вас есть:
Читайте также: