倒是不复杂
需求:
实现思路:
给定一个ID,比如272944
如果表中的ID大于这个值,则自定义变量自增加一,
如果表中的ID小于这个值,则用这个值减去表中ID,
如果表中的ID等于这个值,则为0
-
SELECT
-
a.ZpID,
-
CASE
-
WHEN a.zpid - 272944 > 0 THEN @a:=@a + 1
-
WHEN a.zpid - 272944 < 0 THEN a.zpid - 272944
-
ELSE 0
-
END AS rn
-
FROM
-
zp_audio_video a, (SELECT @a:=0) vars
-
WHERE
-
a.State = 1
-
AND a.Labels LIKE ‘三周年站庆歌曲类%‘
-
ORDER BY a.Zpid
结果:
最后过滤找到结果:
-
SELECT
-
*
-
FROM
-
(
-
SELECT
-
a.ZpID,
-
CASE
-
WHEN a.zpid - 272944 > 0 THEN @a:=@a + 1
-
WHEN a.zpid - 272944 < 0 THEN a.zpid - 272944
-
ELSE 0
-
END AS rn
-
FROM
-
zp_audio_video a, (SELECT @a:=0) vars
-
WHERE
-
a.State = 1
-
AND a.Labels LIKE ‘三周年站庆歌曲类%‘
-
ORDER BY a.Zpid
-
) a
-
WHERE
-
rn <= 5 AND rn != 0
-
ORDER BY rn DESC
-
LIMIT 10;
指定一个ID找到上下的范围
原文：http://blog.itpub.net/29254281/viewspace-1973422/
