CREATE TABLE `sod_song_artist_relation` (

  `SongID` int(11) NOT NULL COMMENT '歌曲编号',

  `ArtistID` int(11) NOT NULL COMMENT '艺术家ID',

  `State` smallint(6) NOT NULL DEFAULT '1' COMMENT '状态 1有效 0 无效 ',

  `CreateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '发布时间',

  `Priority` smallint(6) DEFAULT '1000' COMMENT '推荐优先级',

  PRIMARY KEY (`SongID`,`ArtistID`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

INSERT INTO `sod_song_artist_relation` (`SongID`,`ArtistID`,`State`,`CreateTime`,`Priority`) VALUES (12050,2,1,'2017-03-13 18:06:46',2);
INSERT INTO `sod_song_artist_relation` (`SongID`,`ArtistID`,`State`,`CreateTime`,`Priority`) VALUES (113949,2,1,'2017-03-13 18:06:46',3);
INSERT INTO `sod_song_artist_relation` (`SongID`,`ArtistID`,`State`,`CreateTime`,`Priority`) VALUES (115773,2,1,'2017-03-13 18:06:46',4);
INSERT INTO `sod_song_artist_relation` (`SongID`,`ArtistID`,`State`,`CreateTime`,`Priority`) VALUES (115774,2,1,'2017-03-13 18:06:46',5);
INSERT INTO `sod_song_artist_relation` (`SongID`,`ArtistID`,`State`,`CreateTime`,`Priority`) VALUES (116236,2,1,'2017-03-13 18:06:46',7);
INSERT INTO `sod_song_artist_relation` (`SongID`,`ArtistID`,`State`,`CreateTime`,`Priority`) VALUES (116733,2,1,'2017-03-13 18:06:46',8);
INSERT INTO `sod_song_artist_relation` (`SongID`,`ArtistID`,`State`,`CreateTime`,`Priority`) VALUES (117664,2,1,'2017-03-13 18:06:46',9);
INSERT INTO `sod_song_artist_relation` (`SongID`,`ArtistID`,`State`,`CreateTime`,`Priority`) VALUES (70017258,2,1,'2017-03-13 18:23:55',10);
INSERT INTO `sod_song_artist_relation` (`SongID`,`ArtistID`,`State`,`CreateTime`,`Priority`) VALUES (70024956,2,1,'2017-03-13 18:23:55',10);

以上是实验数据准备

ArtistId是歌手的ID
Songid是歌曲ID
Priority是某个歌手的歌曲优先级,由编辑手工调整。同一个歌手的歌曲优先级不一定是连续的.优先级越小越靠前显示,最大为10。

现在的问题是,编辑指定一个优先级,但是很有可能该优先级已经存在.比如上述数据，假设编辑要指定编号 70024956 的歌曲优先级为 3,但是优先级已经存在.
那么会造成优先级重复,并且不能保证编号70024956 的歌曲优先于编号113949 的歌曲显示.

假设要将歌手为2的70024956 歌曲，由优先级10调整为5
我先将优先级大于等于5,并且小于10的歌曲,将优先级顺延,然后再将目标歌曲修改优先级.

-- 模拟编辑调整歌手ID为2的歌曲70024956,把优先级由10改为5
SET @priority:=5;
SET @songid=70024956 ;
set @artistid=2;
UPDATE sod_song_artist_relation target,
(SELECT
songid, artistid, @rank:=@rank + 1 AS rank
FROM
sod_song_artist_relation, (SELECT @rank:=@priority) vars
WHERE
artistid = @artistid AND state = 1
AND priority < 10
AND priority >= @priority
AND songid != @songid
ORDER BY priority) source
SET
target.priority = CASE
WHEN source.rank >= 10 THEN 10
ELSE source.rank
END
WHERE
source.songid = target.songid
AND source.artistid = target.ArtistID;
UPDATE sod_song_artist_relation
SET
priority = @priority
WHERE
songid = @songid and artistid = @artistid;

修改之后,查看结果,自给定优先级之后的数据,均有序且连续

而给定优先级之前的数据,可能并不连续.

如果需要设置的是名次
可以先将该歌手优先级小于10的数据重排序,让priprity连续有序,在执行上述SQL即可。

ITPub博客

指定分组内的等级位置

最新文章