如何将行从表的开头移动到表的末尾

Question

我有一个表格：

tblRows (RowIndex int identity,  Data int)

获取 X 行并将其移动到表末尾的最佳方法是什么？例如，假设表中有 1000 行，RowIndex 是 1-1000。我想将前 50 行移到末尾，所以现在 RowIndex 将是 51-1050。

我应该使用删除然后插入还是使用更新？

数据列必须是唯一的，所以可以先删除后插入。

行的顺序很重要。

Answer 1

使用insert，然后删除。

INSERT INTO tblRows(Data)
SELECT Data
FROM MyTable
WHERE RowIndex <= 50

然后，一旦您验证了数据已添加，

DELETE FROM tblRows
WHERE RowIndex <= 50

Answer 2

好的，

我想出了这个：添加了一列 RowOrder int，因此表格现在如下所示：

tblRows (RowIndex int identity,  Data int,RowOrder int)

将行顺序从 1 更新为 1000：

update tblRows 
set RowOrder = rn
from (
       select RowOrder,
              row_number() over(order by (select RowIndex)) as rn
       from tblRows 
     ) tblRows 

创建了一个临时表，该表将保存我要移动的行：

DECLARE @TempRows TABLE (RowOrder int,Data int)    
INSERT INTO @TempRows(RowOrder,Data )
SELECT TOP (50) RowOrder,Data 
FROM tblRows 
ORDER BY RowOrder
-- delete rows
DELETE 
FROM  tblRows 
WHERE RowOrder BETWEEN 1 AND 50
-- add rows to the end of the table
INSERT INTO tblRows (RowOrder,Data )
SELECT RowOrder, Data 
FROM @TempRows 
ORDER BY RowOrder
-- update row order
UPDATE RowOrder
SET RowOrder = rn
FROM(
    SELECT RowOrder, row_number() over(order by (select RowIndex)) as rn
    FROM RowOrder    
    ) RowOrder

我很想知道是否有其他更优雅的解决方案。

Answer 3

SET IDENTITY_INSERT dbo.tblRows On;  
with cte as (select top 50 RowIndex, 
                           (SELECT MAX(t.RowIndex) FROM tblRows t) + row_number() 
                           over (order by RowIndex) rn,DATA  from tblRows order by RowIndex)
    INSERT  tblRows (RowIndex, Data)  SELECT RN AS ROWINDEX, DATA  from cte;
;with cte as (select top 50 * FROM tblRows ORDER by RowIndex)
    DELETE cte;

为了避免唯一约束，您可以查询出要移动到临时表的内容，然后删除行，然后从临时表中插入。

;with cte as (select top 50 RowIndex, 
                           (SELECT MAX(t.RowIndex) FROM tblRows t) + row_number() 
                           over (order by RowIndex) rn,DATA  from tblRows order by RowIndex)
SELECT * INTO #tblRows_temp FROM cte;           
with cte as (select top 50 * FROM tblRows ORDER by RowIndex)
    DELETE cte;

SET IDENTITY_INSERT dbo.tblRows On;  
    INSERT  tblRows (RowIndex, Data)  SELECT RN AS ROWINDEX, DATA  from #tblRows_temp;
SET IDENTITY_INSERT dbo.tblRows Off;  
DROP TABLE #tblRows_temp;

Answer 4

SQL 不关心表中行的顺序，你也不应该关心。您的 RowIndex 字段是一个标识字段，它只是一个唯一标识符。排序是在通过查询检索/显示数据时完成的。它的存储顺序根本不重要。更改记录的唯一标识符不利于一致性，并且可能会破坏该数据项与保存在其他位置(在数据库或其他应用程序中(的其他记录之间的关系，这些记录包含对它的引用。

为了解决这个问题，例如，您可以添加一个名为 RowOrder 的整数列，并在编写查询时按该列而不是 RowIndex 对数据进行排序。最初，使用与每行的 RowIndex 相同的值填充此新列。

然后，您提到要将前 50 条记录移动到列表的末尾。因此，您只需找出 RowOrder 的当前最大值，然后(假设没有其他人同时向表中添加记录(，将前 50 条记录的 RowOrder 值递增该量：

DECLARE @MaxRowOrder int
SELECT @MaxRowOrder = MAX(RowOrder) FROM tblRows
UPDATE tblRows SET RowOrder = RowOrder + @MaxRowOrder WHERE RowOrder <= 50

如果你真的想整洁，你可以再写一点 SQL 从所有 RowOrder 值中减去 50，这样它们就会再次从 1 开始。

UPDATE tblRows SET RowOrder = (RowOrder - 50)

要按预期顺序检索行，原因很简单：

SELECT * FROM tblRows ORDER BY RowOrder