create proc sp_ManualSort --1,6
@StartIndex int,
@EndIndex int
as
if @EndIndex > @StartIndex
begin
update TableName set Sort = @EndIndex where Sort = @StartIndex and ID = Sort
while(@StartIndex < @EndIndex)
begin
update TableName set Sort = @StartIndex where Sort = @StartIndex + 1 and ID = Sort
set @StartIndex = @StartIndex + 1
end
end
go
--TableName是表名,ID是表的自增ID,Sort是用于排序的字段--Sort一开始是跟ID的值相同的,经过排序后,Sort的值顺序被打乱,然后用order by重新排序查询,ID的值就被打乱,实现了排序功能。--但是由于某些客观原因,ID可能不连续,Sort也有可能不连续,这就导致排序失败。--请问如何在Sort的值不连续的情况下也实现排序?
@StartIndex int,
@EndIndex int
as
if @EndIndex > @StartIndex
begin
update TableName set Sort = @EndIndex where Sort = @StartIndex and ID = Sort
while(@StartIndex < @EndIndex)
begin
update TableName set Sort = @StartIndex where Sort = @StartIndex + 1 and ID = Sort
set @StartIndex = @StartIndex + 1
end
end
go
--TableName是表名,ID是表的自增ID,Sort是用于排序的字段--Sort一开始是跟ID的值相同的,经过排序后,Sort的值顺序被打乱,然后用order by重新排序查询,ID的值就被打乱,实现了排序功能。--但是由于某些客观原因,ID可能不连续,Sort也有可能不连续,这就导致排序失败。--请问如何在Sort的值不连续的情况下也实现排序?
@StartIndex int,
@EndIndex int
as
if @EndIndex > @StartIndex
begin
update TableName set Sort = @EndIndex where Sort = @StartIndex and ID = Sort
while(@StartIndex < @EndIndex)
begin
update TableName set Sort = @StartIndex where Sort = @StartIndex + 1 and ID = Sort
set @StartIndex = @StartIndex + 1
end
end
else if @EndIndex < @StartIndex
begin
update TableName set Sort = @EndIndex where Sort = @StartIndex and ID = Sort
while(@StartIndex > @EndIndex)
begin
update TableName set Sort = @StartIndex where Sort = @StartIndex - 1 and ID = Sort
set @StartIndex = @StartIndex - 1
end
end
GO发错了,重新发一遍。