I have an Oracle table with 35 columns, one of which is a unique id.
我有一个包含35列的Oracle表，其中一个是唯一id。
This table has a few entries / rows that are duplicate.
这个表有一些重复的条目/行。
When I do a select distinct (*) I get a number x
当我做一个select distinct(*)时，会得到一个数字x
When I do a select distinct ("every column but id") I get a number y < x
当我做一个select distinct(“除id外的每一列”)时，会得到一个数字y < x
Is there anyway I can get those columns that are duplicate, from this table? A minus doesn't help.
我是否可以从这个表中得到那些重复的列?-没有帮助。
1 个解决方案
#1
2
SELECT ("every column but id")
FROM yourTable
GROUP BY
("every column but id")
HAVING COUNT(*) > 1
To delete them
删除他们
- insert the rows from above query into a temporary table
- 将上面查询的行插入到临时表中
- delete all rows from above query including their duplicates from your table
- 从上面的查询中删除所有行，包括表中的重复行
- insert the rows from the temporary table into your table again
- 将临时表中的行插入到表中。
#1
2
SELECT ("every column but id")
FROM yourTable
GROUP BY
("every column but id")
HAVING COUNT(*) > 1
To delete them
删除他们
- insert the rows from above query into a temporary table
- 将上面查询的行插入到临时表中
- delete all rows from above query including their duplicates from your table
- 从上面的查询中删除所有行，包括表中的重复行
- insert the rows from the temporary table into your table again
- 将临时表中的行插入到表中。