sql - 删除除 TOP 1 之外的其他内容

Question

有没有办法删除表中除一行(随机)之外的所有行，而不在 DELETE 中指定任何列名声明？

我正在尝试做这样的事情:

CREATE TABLE [dbo].[DeleteExceptTop1]([Id] INT)
INSERT [dbo].[DeleteExceptTop1] SELECT 1
INSERT [dbo].[DeleteExceptTop1] SELECT 2
INSERT [dbo].[DeleteExceptTop1] SELECT 3

SELECT * FROM [dbo].[DeleteExceptTop1]

DELETE
FROM [dbo].[DeleteExceptTop1]
EXCEPT
SELECT TOP 1 * FROM [dbo].[DeleteExceptTop1]

SELECT * FROM [dbo].[DeleteExceptTop1]

决赛SELECT应该产生一行(可以是三行中的任何一行)。

Answer 1

;WITH CTE AS
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT newid())) AS RN
FROM [dbo].[DeleteExceptTop1]
)
DELETE FROM CTE
WHERE RN > 1

或者类似于@abatishchev的答案，但在排序上有更多的变化并避免废弃的结构。

DECLARE @C INT
SELECT @C = COUNT(*) - 1 
FROM [dbo].[DeleteExceptTop1]

IF @c > 0
BEGIN
WITH CTE AS
(
SELECT TOP(@C) *
FROM [dbo].[DeleteExceptTop1]
ORDER BY NEWID()
)
DELETE FROM CTE;
END

或者使用EXCEPT并假设没有重复行并且所有列的数据类型都与EXCEPT运算符兼容的 final方法

/*Materialise TOP 1 to ensure only evaluated once*/
SELECT TOP(1) * 
INTO #T
FROM [dbo].[DeleteExceptTop1]
ORDER BY NEWID()

;WITH CTE AS
(
SELECT *
FROM [dbo].[DeleteExceptTop1] T1
WHERE EXISTS(
             SELECT *
             FROM #T
             EXCEPT
             SELECT T1.*)
)
DELETE FROM CTE;

DROP TABLE #T