有一个用户拥有的电影数据库,电影显示在称为“我的电影”的页面上,电影可以按照用户期望的顺序显示.例如位置#1的“战斗俱乐部”,位置#3等的“驱动”等等.
明显的解决方案是存储每个项目的位置,例如：
movieid,userid,position
1 | 1 | 1
2 | 1 | 2
3 | 1 | 3
然后当输出数据按位置排序时.该方法对于输出工作正常,但是在更新时有问题：项目的位置所有其他位置需要更新,因为位置是相对的.如果电影#3现在位置编号2,则电影#3现在需要更新到位置#2.如果数据库包含10,000部电影,并且电影从位置#1移动到位置#9999,即将要更新的行数将近达10,000行！
我唯一的解决方案是单独存储定位,而不是为每个项目位置设置一个单独的字段,它只是一个大的数据转储,在运行时间和每个项目(json,xml,任何)相关联,但感觉到的位置.由于数据库无法进行排序,因此无效.
我的总结问题：什么是最有效的方式将项目位置存储在一个友好的提取和更新的列表？
解决方法
create table usermovies (userid int,movieid int,position int,positionsetdatetime datetime) insert into usermovies (userid,movieid,position,positionsetdatetime) values (123,99,1,getutcdate()) insert into usermovies (userid,98,2,97,3,96,4,95,5,94,6,getutcdate()) insert into usermovies (userid,positionsetdatetime) values (987,getutcdate())
;with usermovieswithrank as ( select userid,dense_rank() over (partition by userid order by position asc,positionsetdatetime desc) as movierank from usermovies ) select * from usermovieswithrank where userid=123 order by userid,movierank asc
那么你会得到预期的结果：
USERID MOVIEID MOVIERANK 123 99 1 123 98 2 123 97 3 123 96 4 123 95 5 123 94 6
要移动电影的排名之一,我们需要更新位置和positionsetdatetime列.例如,如果用户标识123将影片95从第5级移动到第2级,则我们这样做：
update usermovies set position=2,positionsetdatetime=getutcdate() where userid=123 and movieid=95
这导致了这一点(使用上面的SELECT查询更新)：
USERID MOVIEID MOVIERANK 123 99 1 123 95 2 123 98 3 123 97 4 123 96 5 123 94 6
然后,如果用户ID 123将影片96移动到等级1：
update usermovies set position=1,positionsetdatetime=getutcdate() where userid=123 and movieid=96
我们得到：
USERID MOVIEID MOVIERANK 123 96 1 123 99 2 123 95 3 123 98 4 123 97 5 123 94 6
当然,您将在usermovies表中得到重复的位置列值,但是使用此方法,您将永远不会显示该列,您只需使用positionsetdatetime来确定每个用户的排序等级,您确定的排名是真正的立场.
如果在某些时候,您希望“位置”列可以正确地反映电影排名,而不参考positionetdatetime,您可以使用上面的选择查询中的movierank来更新usermovies位置列值,因为它实际上不会影响确定的电影排名.