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

最好的解决办法是 镜像功能 ,这是一个简单的整数列表。保持列表的顺序仅仅是一些SQL语句,比其他建议的解决方案(浮动、间隙整数)更容易理解。

如果您的列表非常大(在数万个列表中),那么性能考虑因素可能会发挥作用,但我假设这些列表不会太长。

p1   1000000
p2   2000000
p3   3000000

p1   1000000
p2   2000000
p3   1500000

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

order by

orderby profiles