SQL:重新排序列表顺序

问题描述

我想遍历表格行...通过将 OrderID 设置为 @NUMCOUNT 来重新排序我的表单选项"...

I want to loop through a table row.. reorder my "Form Options" by setting the OrderID to @NUMCOUNT...

我希望能够重新排列我的列表的顺序.我需要这样做的原因是因为如果我删除一个表单选项，那么它就会出现问题.. 即 (1, 2, 3, 5).. 我想要 (1, 2, 3, 4)..等等.

I want to be able to rearrange the order of my List. The reason I need to do this is because if I delete a Form Option then it will be out of order.. i.e (1, 2, 3, 5).. I will want (1, 2, 3, 4).. etc..

示例表:

ID 订单 ID 表单选项

ID OrderID FormOption

1 1 名称

2 3 地址 2

3 2 地址 1

DECLARE @NUMCOUNT int
SET @NUMCOUNT = 0
WHILE (SELECT Count(OrderID) FROM FormOptions WHERE ProductID=1) > @NUMCOUNT
BEGIN
   SET @NUMCOUNT = @NUMCOUNT + 1
   PRINT 'The count is ' + CAST(@NUMCOUNT as char)
   UPDATE FormOptions SET OrderID = @NUMCOUNT WHERE ID=????
END

推荐答案

我猜你正在做的事情需要允许从有序列表中删除并在特定位置插入到有序列表中.删除不需要使订单密集，但可以做到:

I'm guessing that you are working on something that needs to allow deleting from an ordered list and inserting into an ordered list at a specific position. Deleting doesn't require making the order dense, but it can be done:

delete from FormOptions
  where OrderId = @SelectedFormOption
update FormOptions
  set OrderId = OrderId - 1
  where OrderId > @SelectedFormOption

您可能希望将其包装在事务中.(确保您了解交易，因为这非常重要.)

You may want to wrap that in a transaction. (Make sure that you understand transactions as this is rather important.)

插入类似:

update FormOptions
  set OrderId = OrderId + 1
  where OrderId >= @TargetOrderId
insert into FormOptions
  ( OrderId, ... ) values ( @TargetOrderId, ... )

交换订单位置可以在一次更新中自动完成:

Swapping order positions may be done atomically in a single update:

 update FormOptions
   set OrderId = case when OrderId = @TargetA then @TargetB else @TargetB end
   where OrderId in ( @TargetA, @TargetB )

可以编写类似的 update 来将单个表单选项向上或向下移动一个位置.

A similar update can be written to move a single form option up or down one position in the order.

这篇关于SQL:重新排序列表顺序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！