mysql排序

 

advert表2个字段id和orders，id是惟一的，orders是非0整数，orders不允许重复

例如以下测试数据：

id orders

3061

2752

2763

2864

2945

2926

2847

2858

2739

31410

 

现在要对orders排序，比如：

 

一、id=276,orders=3升序

update advert set orders = orders + 1 where orders < 3 and orders >= (3 - 1);

update advert set orders = orders - 1 where id = 276;

 

二、id=276,orders=3降序

update advert set orders = orders - 1 where orders > 3 and orders <= (3 + 1);

update advert set orders = orders + 1 where id = 276;

 

三、id=276,orders=3放首位

update advert set orders = orders + 1 where orders < 3;

update advert set orders = 1 where id = 276;

 

四、id=276,orders=3放末尾

select max(orders) as max_orders from advert;

update advert set orders = orders - 1 where orders > 3;

update advert set orders = max_orders where id = 276;

 

每一个需求都要多条sql语句才能执行，目前正在通过存储过程实现。