任意排序表中的記錄

使用數據庫時的一個常見需求是按順序訪問記錄。例如，如果我有一個部落格，我希望能夠以任意順序重新排列我的部落格文章。這些條目通常有很多關係，因此關係數據庫似乎很有意義。

我見過的常見解決方案是添加一個整數列order：

CREATE TABLE AS your_table (id, title, sort_order)
AS VALUES
 (0, 'Lorem ipsum',   3),
 (1, 'Dolor sit',     2),
 (2, 'Amet, consect', 0),
 (3, 'Elit fusce',    1);

然後，我們可以對行進行排序，order以使它們按正確的順序排列。

但是，這似乎很笨拙：

• 如果我想將記錄 0 移到開頭，我必須重新排序每條記錄
• 如果我想在中間插入一條新記錄，我必須在它之後重新排序每條記錄
• 如果我想刪除一條記錄，我必須在它之後重新排序每條記錄

很容易想像這樣的情況：

• 兩條記錄相同order
• order記錄之間存在差距

由於多種原因，這些情況很容易發生。

這是 Joomla 等應用程序採用的方法：

你可能會爭辯說這裡的界面很糟糕，而不是人類直接編輯數字，他們應該使用箭頭或拖放 - 你可能是對的。但在幕後，同樣的事情正在發生。

有些人建議使用小數來儲存順序，這樣您就可以使用“2.5”在順序 2 和 3 的記錄之間插入一條記錄。雖然這有一點幫助，但可以說它甚至更混亂，因為你可以最終得到奇怪的小數（你停在哪裡？2.75？2.875？2.8125？）

有沒有更好的方法將訂單儲存在表格中？

如果我想將記錄 0 移到開頭，我必須重新排序每條記錄

不，有更簡單的方法。

update your_table
set order = -1 
where id = 0;

如果我想在中間插入一條新記錄，我必須在它之後重新排序每條記錄

這是真的，除非您使用支持“介於”值的數據類型。浮點數和數字類型允許您將值更新為 2.5。但是 varchar(n) 也可以。（想想“a”、“b”、“c”；然後想想“ba”、“bb”、“bc”。）

如果我想刪除一條記錄，我必須在它之後重新排序每條記錄

不，有更簡單的方法。只需刪除該行。其餘行仍將正確排序。

很容易想像這樣的情況：

兩條記錄具有相同的順序

唯一的約束可以防止這種情況。

記錄之間的順序有間隙

間隙對 dbms 如何對列中的值進行排序沒有影響。

有些人建議使用小數來儲存順序，這樣您就可以使用“2.5”在順序 2 和 3 的記錄之間插入一條記錄。雖然這有一點幫助，但可以說它甚至更混亂，因為你可以最終得到奇怪的小數（你停在哪裡？2.75？2.875？2.8125？）

除非你不得不停下來，否則你不會停下來。dbms對小數點後有 2、7 或 15 位的值進行排序沒有問題。

我認為您真正的問題是您希望將排序順序的值視為整數。你可以這樣做。

create table your_table (
 id int primary key, 
 title varchar(13), 
 sort_order float
);
   
insert into your_table values
(0, 'Lorem ipsum', 2.0),
(1, 'Dolor sit', 1.5),
(2, 'Amet, consect', 0.0),
(3, 'Elit fusce', 1.0);

-- This windowing function will "transform" the floats into sorted integers.
select id, title,
      row_number() over (order by sort_order)
from your_table

引用自：https://dba.stackexchange.com/questions/36875