删除非唯一的 id 行

发布于 2024-07-14 02:25:16 字数 434 浏览 4 评论 0原文

从数据库备份中，我有没有唯一 ID 的记录。

有些记录具有唯一的 ID。一些具有重复 ID 的记录包含不同的 DateCreated 值。一些具有重复 ID 的记录包含相同的 DateCreated 值。

我试图获取 MSSql 2005 查询，该查询将仅留下具有最新 DateCreated 值的唯一 ID 值。

从

ID|    DateCreated  
1 |    1/1/09
2 |    1/2/09
2 |    2/2/09
3 |    1/3/09
3 |    1/3/09

到

ID|    DateCreated  
1 |    1/1/09
2 |    2/2/09
3 |    1/3/09

帮助

原文

From a database backup I have records without unique Ids.

Some records have unique IDs.
Some records with duplicated IDs contains different DateCreated values.
Some records with duplicated IDs contains the same DateCreated values.

I'm trying to get a MSSql 2005 query will leave only unique ID values with the most recent DateCreated value.

From

ID|    DateCreated  
1 |    1/1/09
2 |    1/2/09
2 |    2/2/09
3 |    1/3/09
3 |    1/3/09

ID|    DateCreated  
1 |    1/1/09
2 |    2/2/09
3 |    1/3/09

Help

分享到QQ

分享到微博

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

发布评论

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

我的痛♀有谁懂 2024-07-21 02:25:17

DELETE FROM myTable AS t1 
WHERE EXISTS (
    SELECT 1 FROM myTable AS t2 
    WHERE t1.ID=t2.ID AND t1.DateCreated<t2.DateCreated)

即，删除存在具有相同 id 和较晚创建日期的另一行的任何行。

DELETE FROM myTable AS t1 
WHERE EXISTS (
    SELECT 1 FROM myTable AS t2 
    WHERE t1.ID=t2.ID AND t1.DateCreated<t2.DateCreated)

ie, delete any row where there is another row with the same id and a later creation date.

回复收藏 0 原文

忆伤 2024-07-21 02:25:17

create table #t ( id int, date datetime )

insert #t 
values(1, getdate())

insert #t 
values(1, getdate()+1)

insert #t 
values(1, getdate()-1)

insert #t 
values(2, getdate())

insert #t 
values(2, getdate()+1)

delete t 
from #t t
left join (select id, min(date) as date from #t group by id) as t1 
    on t.id = t1.id and t1.date = t.date
where t1.date is null

create table #t ( id int, date datetime )

insert #t 
values(1, getdate())

insert #t 
values(1, getdate()+1)

insert #t 
values(1, getdate()-1)

insert #t 
values(2, getdate())

insert #t 
values(2, getdate()+1)

delete t 
from #t t
left join (select id, min(date) as date from #t group by id) as t1 
    on t.id = t1.id and t1.date = t.date
where t1.date is null

回复收藏 0 原文

~没有更多了~