问题描述
这里有一个场景,我们需要根据时间戳从表中删除所有重复的行.表结构如下所示:
项目 Ref1 Ref2 时间戳1 测试1 2/3/2012 10:00:001 测试2 2/3/2012 11:00:001 测试1 2/3/2012 12:00:002 A prod1 2/3/2012 10:00:002 B prod2 2/3/2012 11:00:002 A prod2 2/3/2012 12:00:00所以我们需要根据item和ref1从这个表中删除重复的行.像这里我们应该只有 1 行用于项目 1 和带有最新时间戳的 ref1 A.对于第 2 项,我们应该只有 1 行用于 ref1 A 和最新的时间戳.
任何指针都会很棒
假设您想要的最终结果是一个包含这 3 行的表格
Item Ref1 Ref2 时间戳1 测试1 2/3/2012 12:00:002 B prod2 2/3/2012 11:00:002 A prod2 2/3/2012 12:00:00
类似的东西
DELETE FROM table_name a存在的地方(选择 1FROM table_name b哪里 a.item = b.itemAND a.ref1 = b.ref1和 a.timestamp <b.时间戳);
应该假设没有两行具有相同的 Item
和 Ref1
并且具有相同的 Timestamp
.如果可以有多个具有相同 Item
和 Ref1
的行,并且它们都具有最新的 Timestamp
并且假设您不关心是哪一个保持
DELETE FROM table_name a存在的地方(选择 1FROM table_name b哪里 a.item = b.itemAND a.ref1 = b.ref1AND a.timestamp
we have a scenario here where we need to delete all the duplicate rows from a table based on timestamp. The table structure looks like this:
Item Ref1 Ref2 Timestamp 1 A test1 2/3/2012 10:00:00 1 A test2 2/3/2012 11:00:00 1 A test1 2/3/2012 12:00:00 2 A prod1 2/3/2012 10:00:00 2 B prod2 2/3/2012 11:00:00 2 A prod2 2/3/2012 12:00:00
So we need to delete the duplicate rows from this table based on item and ref1. like here we should have only 1 row for item 1 and ref1 A with the latest timestamp. Same for item 2 we should have only 1 row for ref1 A with latest timestamp.
Any pointers will be great
Assuming that your desired end result is a table with these 3 rows
Item Ref1 Ref2 Timestamp
1 A test1 2/3/2012 12:00:00
2 B prod2 2/3/2012 11:00:00
2 A prod2 2/3/2012 12:00:00
Something like
DELETE FROM table_name a
WHERE EXISTS( SELECT 1
FROM table_name b
WHERE a.item = b.item
AND a.ref1 = b.ref1
AND a.timestamp < b.timestamp );
should work assuming that there are no two rows with the same Item
and Ref1
that both have the same Timestamp
. If there can be multiple rows with the same Item
and Ref1
that both have the latest Timestamp
and assuming that you don't care which one you keep
DELETE FROM table_name a
WHERE EXISTS( SELECT 1
FROM table_name b
WHERE a.item = b.item
AND a.ref1 = b.ref1
AND a.timestamp <= b.timestamp
AND a.rowid < b.rowid);
这篇关于PL/SQl、oracle 9i、使用sql删除重复行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!