問題描述
我在 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的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！