如何在SQL Server中保留最新记录并删除旧记录？

我的表按照这样的desc值排序

aaa  12am
aaa  11am
bbb  12am
bbb  11am

我需要删除第2行和第4行并保留最新的第2行

你可以使用它做CTE用ROW_NUMBER:

;WITH ToDelete AS (
   SELECT ROW_NUMBER() OVER (PARTITION BY name ORDER BY timefield DESC) AS rn
   FROM mytable
)
DELETE FROM ToDelete
WHERE rn > 1

rn=1识别每个切片的最新记录.删除所执行的操作CTE被传播到真正的表,并删除一切,但最新的记录.

编辑:

要为每张幻灯片选择最新的记录,请使用:

;WITH CTE AS (
   SELECT *,
          ROW_NUMBER() OVER (PARTITION BY name 
                             ORDER BY timefield DESC) AS rn
   FROM mytable
)
SELECT * 
FROM CTE
WHERE rn = 1