SELECT a.ZpID, 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, 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;
