sql - 考虑用户偏好来处理排序结果-数据库

Question

假设我们有一个包含以下数据的简单数据库：

        name
        apple
        pear
        banana
        grape

用户想要按名称对这些水果进行排序，我们将毫不奇怪

         apple
         banana
         grape
         pear

但是，由于某些原因，用户希望将 pear作为第三个水果，这意味着他希望：

         apple
         banana
         pear
         grape

而且，重要的是，当用户以后要按名称对水果进行排序时，用户希望保留此顺序。

我们应该如何解决这个问题？首先，我们可以添加一个字段 user_sort_id，当用户对排序结果进行排序和处理时，该字段将更新，并将其用作排序键。

                       init value       -> sort by name         ->place pear as the seconds
        name           user_sort_id 
        apple            0                     0                          0
        pear             1                     3                          2  
        banana           2                     1                          1  
        grape            3                     2                          3 

这种方法应在理论上起作用。但是，在实践中，我无法想到可以完成此操作的优雅而快速的SQL语句。有什么想法或选择吗？

Answer 1

如果希望每个用户都有独立的排序顺序，则需要另一个表。

CREATE TABLE user_sort_order (
    name VARCHAR(?) NOT NULL REFERENCES your-other-table (name),
    user_id INTEGER NOT NULL REFERENCES users (user_id),
    sort_order INTEGER NOT NULL                -- Could be float or decimal
);

然后订购很容易。

SELECT name 
FROM user_sort_order
WHERE user_id = ?
ORDER BY sort_order

没有更新的魔术子弹。


删除所有用户的行，并以新顺序插入行。 （强力总是有效的。）
用新订单更新每一行。 （可能是很多UPDATE语句。）
跟踪应用程序中的更改，并仅更新更改的行和必须由更改“碰撞”的行。 （简朴，但容易出错。）
不要让用户强加自己的排序顺序。 （通常，这个主意听起来并不差。）