什么是在SQL中存储记录顺序的最佳方法

I have a table of users profiles. Every user can have many profiles and the user has the ability to arange the order of how they will be displayed in a grid.

我有一个用户配置文件表。每个用户都可以拥有许多配置文件,并且用户可以排列它们在网格中的显示顺序。

There are 2 tables Users and Profiles (1:M)

共有2个表用户和个人资料(1:M)

I've added a orderby column to the Users table where will be values like 1,2,3..

我已经在Users表中添加了一个orderby列,其中的值将是1,2,3 ..

So far it seems to be okay. But when a user will change the order of the last record to be the first I have to go throught the all records and increment their values +1. This seems to me pretty ugly.

到目前为止似乎还可以。但是当用户将最后一条记录的顺序更改为第一条记录时,我必须完成所有记录并增加其值+1。这在我看来非常难看。

Is there any more convenient solution for this kind of situation ?

对于这种情况,有没有更方便的解决方案?

7 个解决方案

#1

The best solution is one which mirrors functionality, and that's a simple list of integers. Keeping the list in order is only a few SQL statements, and easier to understand than the other solutions suggested (floats, gapped integers).

最好的解决方案是反映功能的解决方案,这是一个简单的整数列表。保持列表顺序只是几个SQL语句,比其他解决方案建议更容易理解(浮点数,有缺口的整数)。

If your lists were very large (in the tens of thousands) then performance considerations might come into play, but I assume these lists aren't that long.

如果您的列表非常大(数万个),那么性能考虑可能会发挥作用,但我认为这些列表并不长。

#2

Leave gaps in the sequence or use a decimal rather than an integer data type.

在序列中留下间隙或使用小数而不是整数数据类型。

#3

How about using floating points for the order by column? This way, you can always squeeze a profile between two others, without having to change those two values. Eg if I want to place profile A between profiles B (ordervalue 1) and C (ordervalue 2), I can assign ordervalue 1.5 to A. To place it on top, where before the top used to have ordervalue say 1, you can use ordervalue 0.5

如何按列使用浮点数?这样,您可以随时在两个其他人之间挤压配置文件,而无需更改这两个值。例如,如果我想在配置文件B(ordervalue 1)和C(ordervalue 2)之间放置配置文件A,我可以将ordervalue 1.5分配给A.要将它放在顶部,在顶部之前的位置有ordervalue说1,你可以使用订单价值0.5

There's no reason to have integers for orderby and no reason to have increments of 1 between the order of profiles.

没有理由为orderby设置整数,没有理由在配置文件的顺序之间增加1。

#4

As a user adds profiles, set each new profile's ordering number to the previous one +1000000. e.g. to start off with:

当用户添加配置文件时,将每个新配置文件的订购号设置为前一个+1000000。例如首先:

p1   1000000
p2   2000000
p3   3000000

When reordering, set the profile's order to the middle of the two it is going in between:

重新排序时,将配置文件的顺序设置为它们之间的两个中间位置:

p1   1000000
p2   2000000
p3   1500000

This gives the order p1,p3,p2

这给出了阶数p1,p3,p2

#5

If the data set is small (which seems to be the case), I'd prefer to use a normal list of integers and update them in batch when a profile gets a new position. This better reflects the application functionality.

如果数据集很小(似乎是这种情况),我宁愿使用正常的整数列表,并在配置文件获得新位置时批量更新它们。这更好地反映了应用程序功能。

In Sql Server, for the following table User_Profiles (user_id, profile_id, position), I'd have something like this:

在Sql Server中,对于下表User_Profiles(user_id,profile_id,position),我有这样的事情:

--# The variables are: 
--#   @user_id - id of the user
--#   @profile_id - id of the profile to change
--#   @new_position - new position that the profile will take
--#   @old_position - current position of the profile 

select @old_position = position 
from User_Profiles where 
user_id = @user_id and profile_id = @profile_id

update p set position = pp.new_position
from User_Profiles p join (
  select user_id, profile_id,
    case 
    when position = @old_position then @new_position 
    when @new_position > @old_position then --# move up
      case 
      when @old_position < position and 
           position <= @new_position 
      then position - 1
      else position
      end
    when @new_position < @old_position then --# move down
      case 
      when position < @old_position and 
           @new_position <= position 
      then position + 1
      else position
      end
    else position --# the same
    end as new_position
  from User_Profiles p where user_id = @user_id
) as pp on 
p.user_id = pp.user_id and p.profile_id = pp.profile_id

#6

I think the idea of leaving gaps between the orders is interesting but I don't know if it a "more convenient" solution for your problem.

我认为在订单之间留下空白的想法很有意思,但我不知道它是否是一个“更方便”的解决方案。

I think you would be better off just updating your order by column. Because you are still going to have to determine what rows the statuses have moved between, and what to do if two statuses are switched in position (Do you calculate the new order by value for the first one then the second one). What happens if the gap between isn't large enough?

我认为你最好只按列更新订单。因为您仍然需要确定状态在哪些行之间移动,以及如果两个状态切换到位,该怎么办(您是按第一个计算新订单的值,还是第二个计算新订单)。如果两者之间的差距不够大会怎么样?

It shouldn't be that data intensive to just enumerate down the order they put it in and update each record to the order.

数据密集不应只是枚举它们放入的顺序并将每条记录更新到订单。

#7

I think instead of keeping order in the orderby column you can introduce linklist concept to your design. Add column like nextId that will contain the next profile in the chain. When you query the profiles table you can sort out profiles in your code (java, C#, etc)

我认为不是在orderby列中保持顺序,而是可以在您的设计中引入链接列表概念。添加像nextId这样的列,它将包含链中的下一个配置文件。查询配置文件表时,您可以在代码中对配置文件进行排序(java,C#等)

#1