根据最大日期/时间删除Oracle中的重复记录

发布于 2024-10-02 07:59:38 字数 503 浏览 1 评论 0原文

我有以下包含重复信息的示例数据：

ID   Date                 Emp_ID    Name    Keep
---------------------------------------------------------
1    17/11/2010 13:45:22  101       AB      *
2    17/11/2010 13:44:10  101       AB
3    17/11/2010 12:45:22  102       SF      *
4    17/11/2010 12:44:10  102       SF
5    17/11/2010 11:45:22  103       RD      *
6    17/11/2010 11:44:10  103       RD

根据上述数据集，如何删除重复的员工 ID 并仅保留指定了最大日期/时间的员工 ID？

所以根据上面的内容，我只会看到 ID：1、3 和 5。

原文

I have the following sample data with duplicate information:

ID   Date                 Emp_ID    Name    Keep
---------------------------------------------------------
1    17/11/2010 13:45:22  101       AB      *
2    17/11/2010 13:44:10  101       AB
3    17/11/2010 12:45:22  102       SF      *
4    17/11/2010 12:44:10  102       SF
5    17/11/2010 11:45:22  103       RD      *
6    17/11/2010 11:44:10  103       RD

Based on the above data set, how can I remove the duplicate Emp IDs and only keep the Emp IDs that have the maximum date/time specified?

So based on the above, I would only see IDs: 1, 3 and 5.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

你是暖光i 2024-10-09 07:59:38

像这样的东西：

DELETE FROM the_table_with_no_name 
WHERE date_column != (SELECT MAX(t2.date_column) 
                      FROM the_table_with_no_name t2 
                      WHERE t2.id = the_table_with_no_name.id);

Something like:

DELETE FROM the_table_with_no_name 
WHERE date_column != (SELECT MAX(t2.date_column) 
                      FROM the_table_with_no_name t2 
                      WHERE t2.id = the_table_with_no_name.id);

回复收藏 0 原文

梦毁影碎の 2024-10-09 07:59:38

您可以生成除具有最大日期的行（对于给定 EMPId）之外的所有行的 ROWID 并删除它们。我发现这是一种高性能的方法，因为它是一种基于集合的方法并使用分析、rowID。

--get list of all the rows to be deleted.

select row_id from (
select rowid row_id,
       row_number() over (partition by emp_id order by date desc) rn
  from <table_name>
) where rn <> 1

然后删除行。

delete from table_name where rowid in (
    select row_id from (
    select rowid row_id,
           row_number() over (partition by emp_id order by date desc) rn
      from <table_name>
    ) where rn <> 1
);

You can generate the ROWIDs of all rows other than the one with the maximum date (for a given EMPIds) and delete them. I have found this to be performant as it is a set-based approach and uses analytics, rowIDs.

--get list of all the rows to be deleted.

select row_id from (
select rowid row_id,
       row_number() over (partition by emp_id order by date desc) rn
  from <table_name>
) where rn <> 1

And then delete the rows.

delete from table_name where rowid in (
    select row_id from (
    select rowid row_id,
           row_number() over (partition by emp_id order by date desc) rn
      from <table_name>
    ) where rn <> 1
);

回复收藏 0 原文

~没有更多了~