sql - 删除SQL Server中除最高记录外的所有记录

Question

我正在尝试编写一个脚本来删除除头一个记录以外的所有记录(如果也可以的话，只剩下一个记录)。这是我到目前为止的格式:

DELETE FROM table
WHERE id NOT IN (
  SELECT id
  FROM (
    SELECT id
    FROM table
    ORDER BY id DESC
    LIMIT 1 ))

但是我意识到LIMIT在SQL Server 2005中不是公认的功能，因此我对执行该操作感到有些困惑。有人对此主题有任何专业知识吗？这些记录不是重复的，我只想取出一个记录。

更新:

我意识到我要在这里完成的工作还不是很清楚。我需要做的就是删除该表中除最上面的记录以外的所有记录，因为它在另一列中具有相同的值(我们称其为“anotherid”)。

所以它是这样的:

id        value       anotherid       
1          3           1
2          4           1
3          5           2
4          6           2
5          7           2

对此:

id        value       anotherid       
1          3           1
3          5           2

Answer 1

根据我的理解，给出更新:

DECLARE @x TABLE(id INT, value INT, anotherid INT);

INSERT @x SELECT 1, 3, 1
UNION ALL SELECT 2, 4, 1
UNION ALL SELECT 3, 5, 2
UNION ALL SELECT 4, 6, 2
UNION ALL SELECT 5, 7, 2;

;WITH y AS 
(
  SELECT id, rn = ROW_NUMBER() OVER 
   (PARTITION BY anotherid ORDER BY id) -- or order by value?
   FROM @x -- replace with your dbo.tablename
)
DELETE y WHERE rn > 1;

SELECT id, value, anotherid FROM @x;

结果:

id  value  anotherid
--- ------ ---------
1   3      1
3   5      2

回答原始问题

其他几个选择:

-- use a CTE to identify the highest id,
-- then delete all rows except that one
;WITH x AS 
(
  SELECT id, rn = ROW_NUMBER() OVER (ORDER BY id DESC) 
  FROM dbo.table
)
DELETE x WHERE rn > 1;

-- use a subquery to get the max, and delete
-- all id values lower than that result
DELETE dbo.table WHERE id < (SELECT MAX(id) FROM dbo.table);

请注意，如果 id不是唯一的，则它们的行为会有所不同。假设最高 id是 10，并且 id = 10有三行。最上面的答案将在表格中保留一行；底部的答案(以及Adrian的答案)将保留三行。如果这是您的情况，并且即使在出现平局的情况下仍打算在表中恰好保留一行，则可以在顶部查询中的 ORDER BY中添加其他打破平局条件。