SQL-仅当另一列匹配[重复]时才删除重复列

Question

我有下表（TBL_视频），在“TIMESTAMP”中有重复的列条目，我只想在“CAMERA”编号匹配时删除它们。

之前：

ANALYSIS_ID | TIMESTAMP | EMOTION | CAMERA
-------------------------------------------    
 1          | 5         | HAPPY   | 1
 2          | 10        | SAD     | 1
 3          | 10        | SAD     | 1
 4          | 5         | HAPPY   | 2
 5          | 15        | ANGRY   | 2
 6          | 15        | HAPPY   | 2

之后：

ANALYSIS_ID | TIMESTAMP | EMOTION | CAMERA
-------------------------------------------    
 1          | 5         | HAPPY   | 1
 2          | 10        | SAD     | 1
 4          | 5         | HAPPY   | 2
 5          | 15        | ANGRY   | 2

我已尝试此语句，但列不会相应删除。我非常感谢为生成正确的SQL语句所提供的所有帮助。提前谢谢！

delete y
from TBL_VIDEO y 
where exists (select 1 from TBL_VIDEO y2 where y.TIMESTAMP = y2.TIMESTAMP and y2.ANALYSIS_ID < y.ANALYSIS_ID, y.CAMERA = y.CAMERA, y2.CAMERA = y2.CAMERA);

Answer 1

使用行号查找重复项并将其删除

delete from
(select *,row_number() over(partition by TIMESTAMP,CAMERA order by ANALYSIS_ID) as rn from TBL_VIDEO
) t1 where rn>1

Answer 2

其他解决方案：

delete f1 from yourtable f1
where exists 
(
  select * from yourtable f2
  where f2.TIMESTAMP=f1.TIMESTAMP and f2.CAMERA=f1.CAMERA and f1.ANALYSIS_ID>f2.ANALYSIS_ID
)

Answer 3

试试这个：

delete f2 from (
select row_number() over(partition by TIMESTAMP, CAMERA order by ANALYSIS_ID) rang
from yourtable f1
) f2 where f2.rang>1