如何删除oracle表/视图中的重复记录？
我在 Oracle 中有一个视图 nat_gas_readings_view，我发现一些重复项不知从何而来。几个月来，直到上周才出现重复的迹象。以下是我从我的观点中收集的一些示例数据。我想编写一个查询来删除这些重复项。如果可能的话，任何人都可以解释这是如何发生的，并帮助我创建一个查询以从视图中删除重复项。谢谢。
DATETIMESTAMP BOF EAF LEVY SHOP
10/31/2011 13:00:01 3564729 933776 12459 307
10/31/2011 13:00:01 3564729 933776 12459 307
10/31/2011 12:00:08 3563079 933446 12459 307
10/31/2011 12:00:08 3563079 933446 12459 307
10/31/2011 11:00:09 3561659 933121 12459 307
10/31/2011 11:00:09 3561659 933121 12459 307
10/31/2011 10:00:10 3560636 932802 12458 307
10/31/2011 09:00:08 3559548 932481 12450 307
10/31/2011 09:00:08 3559548 932481 12450 307
I have a view, nat_gas_readings_view, in Oracle that I have discovered some duplicates popping up from nowhere. For months, there were no signs of duplicates until this past week. Below is some sample data that I collected from my view. I would like to write a query to remove this duplicates. If possible could anyone explain how this happens and help me create a query to remove the duplicates from the view. Thank you.
DATETIMESTAMP BOF EAF LEVY SHOP
10/31/2011 13:00:01 3564729 933776 12459 307
10/31/2011 13:00:01 3564729 933776 12459 307
10/31/2011 12:00:08 3563079 933446 12459 307
10/31/2011 12:00:08 3563079 933446 12459 307
10/31/2011 11:00:09 3561659 933121 12459 307
10/31/2011 11:00:09 3561659 933121 12459 307
10/31/2011 10:00:10 3560636 932802 12458 307
10/31/2011 09:00:08 3559548 932481 12450 307
10/31/2011 09:00:08 3559548 932481 12450 307
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
如果这是一个视图，您需要首先查看创建该视图的查询，可能是基础查询中的数据不重复，或者有更多字段使其不重复。
消除重复的一种方法是通过在创建视图的选择中添加 DISTINCT 来更改视图中的查询。
我会通过检查或创建唯一密钥来消除输入时的重复项。
If this a view you need to look at the query that creates this view first, may be the data in the underlying queries is not duplicated or has more fields that make it not duplicated.
One way to get rid of the dups is to change the query in the view by adding DISTINCT in the select that makes the view.
And I would eliminate the duplicated when they are entered by checking or creating unique key.
删除重复项的最简单方法可能是使用 rowid，例如：
但是视图不会有 rowid。请发布您的视图定义 - 也许正是这个造成了重复！如果视图 def 正常，则可能使用上面的 rowid 从基础表中删除重复项。最好应用唯一的约束以确保将来不会发生这种情况。
Easiest way to delete dups is probably using rowid like:
But a view won't have a rowid. Please post your view definition - perhaps it is this that is creating the dups! If view def is OK then perhaps delete dups from underlying table using rowid as above. Best to apply a unique constraint to make sure this can't happen in future.