insert into sortdemo([name]) values(1)
/*触发器，让排序初始值为主键ID*/
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'trig_a' AND type = 'TR')
DROP TRIGGER trig_a
GO
create trigger trig_a on sortdemo for insert
as
declare @id int
select @id=[id] from inserted
update sortdemo set sort=@id where [id]=@id
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'setsort' AND type = 'P')
DROP PROCEDURE setsort
GO
create proc setsort
@id int,--移动对象id
--@distance int,移动距离
@direction int, --方向0向上1向下
@sort decimal(18,1)=0, --排序值
@id1 int=0 --临时id
as
if(@direction=0) --向上移动
begin
--获取传入主键所在记录上面的前N行，top后的数字便是N
set @id1=(
select top 1 [id] from sortdemo where sort<(select sort from sortdemo where [id]=@id) order by sort desc
)
--如果前面还有记录则继续
if(@id1 is not null)
begin
--获取移动目标目的行所在数据的排序值
select @sort=sort from sortdemo where [id]=@id1
--修改目标排序值使之排在目标行之前
update sortdemo set sort=@sort-0.1 where [id]=@id
end
end
else --向下移动
begin
--获取传入主键所在记录上面的后N行，top后的数字便是N
set @id1=(
select top 1 [id] from sortdemo where sort>(select sort from sortdemo where [id]=@id) order by sort
)
--如果后面还有记录则继续
if(@id1 is not null)
begin
--获取移动目标目的行所在数据的排序值
select @sort=sort from sortdemo where [id]=@id1
--修改目标排序值使之排在目标行之后
update sortdemo set sort=@sort+0.1 where [id]=@id
end
end
--执行语句
setsort 10,0
--获取排序后的结果，默认根据排序值排序，如果排序值相同按两者的名字培训
select * from sortdemo order by sort,[name]
/*前N行*/
select top 1 * from (
--获取传入主键所在记录的前N行，top后的数字便是N
select top 3 [id] from sortdemo where sort<(select sort from sortdemo where [id]=3) order by sort desc
) as tep order by [id] desc
/*后N行*/
select top 1 * from (
--获取传入主键所在记录的后N行，top后的数字便是N
select top 3 [id] from sortdemo where sort>(select sort from sortdemo where [id]=3) order by sort
) as tep order by [id] desc
