列表排序上移和下移,记录备忘
DECLARE @operType INT = 0;--0下移 1上移 DECLARE @id INT = 1; declare @preSort int;--上一条记录的排序值 declare @preid int; --上一条记录的id值 declare @curSort int;--当前记录的排序值 declare @nextSort int;--下一条记录的排序值 declare @nextid int; --下一条记录的id值 IF @operType = 0 BEGIN with TB As (select top 1000 *,row_number() over(order by Sort desc) as rowid from cn9c080.tb_QQ_CommodityTemplete WHERE status <> 2 order by Sort desc) select @nextSort=sort,@nextid=id from TB where rowid=(select rowid-1 from TB where id=@id); select @curSort=Sort From cn9c080.tb_QQ_CommodityTemplete Where id=@id; update cn9c080.tb_QQ_CommodityTemplete set Sort=@nextSort where id=@id; update cn9c080.tb_QQ_CommodityTemplete set Sort=@curSort where id=@nextid END; ELSE BEGIN with TB As (select top 1000 *,row_number() over(order by Sort desc) as rowid from cn9c080.tb_QQ_CommodityTemplete WHERE status <> 2 order by Sort desc) select @preSort=sort,@preid=id from TB where rowid=(select rowid+1 from TB where id=@id); select @curSort=Sort From cn9c080.tb_QQ_CommodityTemplete Where id=@id; update cn9c080.tb_QQ_CommodityTemplete set Sort=@preSort where id=@id; update cn9c080.tb_QQ_CommodityTemplete set SOrt=@curSort where id=@preid END;