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.CAMERA < y.CAMERA);

Answer 1

通用SQL命令如下所示。您可以输入列名/条件和表名。

从（SELECTROW_NUMBER（）over（分区按列1顺序按列2）a中删除T，*from TABLENAME）T

Answer 2

你有两个问题：

1. 我的代码有什么问题
2. 是否有更好的方法删除重复列条目

对于第二个问题，这是一个重复。

第一个问题，请参阅https://docs.microsoft.com/en-us/sql/t-sql/statements/delete-transact-sql?view=sql-服务器-2017。（删除时按F1）。正确的语法是

delete y
from Table12 y 
where exists (

Answer 3

CREATE TABLE Table12
    ([ANALYSIS_ID] int, [TIMESTAMP] int, [EMOTION] varchar(5))
;

INSERT INTO Table12
    ([ANALYSIS_ID], [TIMESTAMP], [EMOTION])
VALUES
    (1, 5, 'HAPPY'),
    (2, 10, 'SAD'),
    (3, 10, 'SAD'),
    (4, 15, 'HAPPY'),
    (5, 15, 'ANGRY')
;

with cte as (select *, row_number() over (partition by emotion order by [ANALYSIS_ID] ) as rn   from Table12) 
delete from cte
where rn>1
select * from Table12

输出

ANALYSIS_ID TIMESTAMP   EMOTION
1              5         HAPPY
2              10         SAD
5              15        ANGRY