在SQL Server 2000中，如何删除没有主键的表中的指定行？

Question

假设我们有一个表，其中包含一些数据。

IF OBJECT_ID('dbo.table1') IS NOT NULL
BEGIN
    DROP TABLE dbo.table1;
END
CREATE TABLE table1 ( DATA INT );

---------------------------------------------------------------------
-- Generating testing data
---------------------------------------------------------------------
INSERT INTO dbo.table1(data)
SELECT 100
UNION ALL
SELECT 200
UNION ALL
SELECT NULL
UNION ALL
SELECT 400
UNION ALL
SELECT 400
UNION ALL
SELECT 500
UNION ALL
SELECT NULL;

如何删除表中的第2、5、6条记录？该顺序由以下查询定义。

SELECT  data
FROM    dbo.table1
ORDER BY data DESC;

请注意，这是在SQL Server 2000环境中。

谢谢。

Answer 1

简而言之，您需要表中的某些内容来指示顺序。当没有任何东西强制顺序时，“第二行”是不合逻辑的。然而，一个可能的解决方案可能是（玩具示例=>玩具解决方案）：

If object_id('tempdb..#NumberedData') Is Not Null
    Drop Table #NumberedData

Create Table #NumberedData
(
Id int not null identity(1,1) primary key clustered
, data int null
)

Insert #NumberedData( data )
SELECT 100
UNION ALL SELECT 200
UNION ALL SELECT NULL
UNION ALL SELECT 400
UNION ALL SELECT 400
UNION ALL SELECT 500
UNION ALL SELECT NULL

Begin Tran

Delete table1

Insert table1( data )
Select data
From #NumberedData
Where Id Not In(2,5,6)

If @@Error <> 0 
    Commit Tran
Else 
    Rollback Tran

显然，这种类型的解决方案不能保证完全按照您想要的方式工作，但这个概念是您所能得到的最好的。本质上，您将行填充到带有标识列的表中，并使用它来标识要删除的行。删除行需要清空原始表并仅使用所需的行重新填充。如果没有某种唯一的密钥，就没有干净的方法来处理这个问题。

Answer 2

您可能知道，您可以在更高版本中使用 row_number 非常直接地执行此操作。

delete t from 
(select ROW_NUMBER() over (order by data) r from table1) t   
where r in (2,5,6)

即使没有它，也可以使用未记录的 %%LOCKRES%% 函数来区分 2 个相同的行，

SELECT data,%%LOCKRES%%
FROM dbo.table1`

但我认为这在 SQL Server 2000 中不可用。

在 SQL 集中没有顺序，但游标有顺序，因此您可以使用如下所示的内容。注意：我期望能够使用 DELETE ... WHERE CURRENT OF 但这依赖于 PK，因此删除行的代码并不像我希望的那么简单。

如果要删除的数据是重复的，则不能保证它将删除与 CURRENT OF 相同的行。然而，在这种情况下，关联行的排序无论如何都是任意的，因此无论删除哪一行都可以同样在游标排序中给出该行号。

DECLARE @RowsToDelete TABLE
(
rowidx INT PRIMARY KEY
)
INSERT INTO @RowsToDelete SELECT 2 UNION SELECT 5 UNION SELECT 6

DECLARE @PrevRowIdx int
DECLARE @CurrentRowIdx int
DECLARE @Offset int
SET @CurrentRowIdx = 1
DECLARE @data int

DECLARE ordered_cursor  SCROLL CURSOR FOR
SELECT data
FROM dbo.table1
ORDER BY data

OPEN ordered_cursor

FETCH NEXT FROM ordered_cursor INTO @data

WHILE EXISTS(SELECT * FROM @RowsToDelete)
BEGIN
SET @PrevRowIdx = @CurrentRowIdx
SET @CurrentRowIdx = (SELECT TOP 1 rowidx FROM @RowsToDelete ORDER BY rowidx)
SET @Offset = @CurrentRowIdx - @PrevRowIdx
DELETE FROM @RowsToDelete WHERE rowidx = @CurrentRowIdx

FETCH RELATIVE @Offset FROM ordered_cursor INTO @data

/*Can't use DELETE ... WHERE CURRENT OF as here that requires a PK*/
SET ROWCOUNT 1
DELETE FROM dbo.table1 WHERE (data=@data OR data IS NULL OR @data IS NULL)
SET ROWCOUNT 0

END

CLOSE ordered_cursor
DEALLOCATE ordered_cursor

Answer 3

要对一组行执行任何操作（例如删除它们），您需要知道标识这些行的内容。

因此，您必须提出确定要删除的行的标准。

提供一个玩具示例（如上面的示例）并不是特别有用。

Answer 4

您提前计划，如果您预计这是可能的，您可以添加代理键列或类似的列。

一般来说，您要确保不会创建没有 PK 的表。

这就像问“假设我在过马路之前没有向两个方向看，然后我就走到了一辆公共汽车前面”。