sql - SQL - 删除重复列错误

Question

我有下表（TBL_VIDEO），在“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);

标签： sqlsql-server

Answer 1

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