排序

gistfile1.txt

-- 置顶
UPDATE t_article SET display_order=display_order-1 WHERE display_order>1;
UPDATE t_article SET display_order=3 WHERE id=3;

-- 置底
UPDATE t_article SET display_order=display_order+1 WHERE display_order<3;
UPDATE t_article SET display_order=1 WHERE id=3;


-- 上移
select * from t_article where id=3;
SELECT * from t_article WHERE ( display_order > 1  OR display_order=(SELECT MAX(display_order) FROM t_article ) )  ORDER BY display_order ASC limit 1;
update t_article set display_order=1 where id=1;
update t_article set display_order=2 where id=3;

-- 下移
select * from t_article where id=1;
SELECT * from t_article WHERE ( display_order < 2  OR display_order=(SELECT MIN(display_order) FROM t_article ) )  ORDER BY display_order DESC limit 1;
update t_article set display_order=1 where id=1;
update t_article set display_order=2 where id=3;