PL/SQl、oracle 9i、使用sql删除重复行 [英] PL/SQl, oracle 9i, deleting duplicate rows using sql

问题描述

这里有一个场景，我们需要根据时间戳从表中删除所有重复的行.表结构如下所示:

<前>项目 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 <= b.timestampAND a.rowid 

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删除重复行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！