在 SQL 查询中查找下一行并仅在前一行匹配时将其删除 [英] Finding next row in SQL query and deleting it only if previous row matches

问题描述

我有一张这样的桌子.

|-DT--------- |-ID------|
|5/30 12:00pm |10       |
|5/30 01:00pm |30       |
|5/30 02:30pm |30       |
|5/30 03:00pm |50       |
|5/30 04:30pm |10       |
|5/30 05:00pm |10       |
|5/30 06:30pm |10       |
|5/30 07:30pm |10       |
|5/30 08:00pm |50       |
|5/30 09:30pm |10       |

仅当前一行与下一行具有相同的 ID 时，我才想删除任何重复的行.我想在未来保留日期时间最远的重复行.例如，上表将如下所示.

I want to remove any duplicate rows only if the previous row has the same ID as the following row. I want to keep the duplicate row with the datetime furthest in the future. For example the above table would look like this.

|-DT--------- |-ID------|
|5/30 12:00pm |10       |
|5/30 02:30pm |30       |
|5/30 03:00pm |50       |
|5/30 07:30pm |10       |
|5/30 08:00pm |50       |
|5/30 09:30pm |10       |

我可以获得有关如何完成此操作的任何提示吗?

Can I get any tips on how this can be done?

推荐答案

with C as
(
  select ID,
         row_number() over(order by DT) as rn
  from YourTable
)
delete C1
from C as C1
  inner join C as C2
    on C1.rn = C2.rn-1 and
       C1.ID = C2.ID

SE-Data

这篇关于在 SQL 查询中查找下一行并仅在前一行匹配时将其删除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！