基于位置重新排序行 SQL Server

问题描述

我在 SO 上看到了很多关于这个问题的问题，但没有一个与我的场景相关.除了做基本的 CRUD 操作之外，我与 SQL 人员相去甚远.因此我对此非常坚持.

I have seen many questions on SO regarding this question but none is relevant to my scenario. I am nowhere near an SQL guy apart from doing basic CRUD operations. Hence I am quite stuck with this.

我有一张桌子.

myTable [rID, newsID, OrderPosition] where;

rID is primaryKey int column, 
newsID int is the ID of an item from another table and,
OrderPosition int to hold the position of the rows. 

myTable 总是总共有 10 行.

myTable will always have a total of 10 rows.

所以，最初，让我们假设 myTable 有以下数据:

So, initially, lets assume myTable has the following data:

rID    newsID    OrderPosition
100    4000      1
101    4100      2
102    4200      3
103    4300      4
104    4400      5
105    4500      6
106    4600      7
107    4700      8
108    4800      9
109    4900      10

预期的功能应该如下；

插入新内容

插入新项目时，用户应该能够将其插入到他/她想要的任何位置.现在，我只能通过删除 OrderPosition = 10 行、将新记录 OrderPosition 分配为 0 并重新排序表来设法将新记录插入到第一个位置.但是客户想要选择项目应该放在哪个位置.在这种情况下，我假设 OrderPosition = 10 将再次被删除?

when inserting a new item, user should be able to insert it into any position he/she desires. Right now, I only managed to insert the new record to the first position by deleting the OrderPosition = 10 row, assigning the new record OrderPosition of 0, and reorder the table. But client wants to select which position the item should go. In which case I assume, the OrderPosition = 10 will be deleted again?

删除

当从这张表中删除一条记录时，由于总有10条记录，所以我需要从另一个表[tblNews]中获取最新输入的记录并将其插入到第10个位置(我可以得到最后一个tblNews 中的记录按输入日期降序排列.)由于我不知道他们将删除哪条记录，因此我不知道如何在删除记录后对表重新排序.

When a record from this table is deleted, since there will always be a total of 10 records, I need to get the latest entered record from another table [tblNews] and insert it to the 10th position (I can get the last record from tblNews by ordering descending by the date it was entered.) Since I don't know which record they will delete, I don't know how to re-order the table after a record has been deleted.

非常感谢任何帮助、代码和文章指导.

Any help, code, direction to an article would be very much appreciated.

============编辑====================

=========== EDIT ====================

答案中提到的 UPDATE 方法对我不起作用，因为；例如用户想要在第 5 个订单位置插入一条新记录.这意味着，订单位置 10 将被删除，订单位置 5、6、7、8 和 9 的当前记录将增加 1

The UPDATE method mentioned in the answers will not work for me since; e.g. user wants to insert a new record into the 5th order position. This would mean, the order position 10 would be deleted and the current records with order postions 5,6,7,8 and 9 is to be incremented by one

推荐答案

插入行时，需要移动"下面的行以腾出空间.例如，可以像这样在位置 4 上插入:

When inserting the row, you need to "move" the rows below to make the room for it. For example, inserting on a position 4 could be done like this:

DELETE FROM myTable WHERE OrderPosition = 10; -- If you want the keep the table from growing beyond 10 rows.
UPDATE myTable SET OrderPosition = OrderPosition + 1 WHERE OrderPosition >= 4;
INSERT INTO myTable VALUES (..., 4);

相反，在删除该行后，您可以将其下方"的行移回:

Conversely, after you delete the row, you can move the rows "below" it back up:

DELETE FROM myTable WHERE OrderPosition = 4;
UPDATE myTable SET OrderPosition = OrderPosition - 1 WHERE OrderPosition > 4;
-- Now you can insert the row on position 10 if desired.

注意，即使 OrderPosition 有一个 UNIQUE 约束，UPDATE 也不会违反它.

Note that even if OrderPosition has a UNIQUE constraint on it, UPDATE won't violate it.

[SQL 小提琴]

这篇关于基于位置重新排序行 SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！