DECLARE @i INT; SET @i=0
UPDATE [WebSiteCluster].[dbo].[WebSite_System_Component]
SET SortID=@i,@i=@i+1
WHERE pkID IN(95,15,14,17,16)
一看您就懂的。
要将:95,15,14,17,16的SortID更新为:1,2,3,4,5。
而系统自动会将:95,15,14,17,16的SortID更新为:5,2,1,4,3。
即系统自动的将:95,15,14,17,16进行排序成14,15,16,17,95后,然后再赋与:1,2,3,4,5
UPDATE [WebSiteCluster].[dbo].[WebSite_System_Component]
SET SortID=@i,@i=@i+1
WHERE pkID IN(95,15,14,17,16)
一看您就懂的。
要将:95,15,14,17,16的SortID更新为:1,2,3,4,5。
而系统自动会将:95,15,14,17,16的SortID更新为:5,2,1,4,3。
即系统自动的将:95,15,14,17,16进行排序成14,15,16,17,95后,然后再赋与:1,2,3,4,5
SET SortID=charindex(rtrim(pkID)+',','95,15,14,17,16,')
WHERE pkID IN(95,15,14,17,16)
set SortID=b.cnt
from [WebSiteCluster].[dbo].[WebSite_System_Component] a,
(select cnt=row_number()over(order by (charindex(','+ltrim(ID)+',',',95,15,14,17,16,')),pkID)
from [WebSiteCluster].[dbo].[WebSite_System_Component]
WHERE pkID IN(95,15,14,17,16)
)b
where a.pkID=b.pkID
两位的值,结果都不是这个值。
insert into #b(a)select pkid from WebSite_System_Component b where b.pkid in(95,15,14,17,16)
order by b.pkid desc
update WebSite_System_Component set SortID=#b.SortID
from WebSite_System_Component join #b on WebSite_System_Component.a=#b.a
where #a.a in(95,15,14,17,16)
(
select rowno = ROW_NUMBER() over(order by SortID), *
from [WebSiteCluster].[dbo].[WebSite_System_Component]
where pkID IN(95,15,14,17,16)
)
update cte
set SortID = rowno
insert temp
select 95, 2 union all
select 15, 8 union all
select 14, 4 union all
select 17, 5 union all
select 16, 7 union all
select 99, 9
go
--SQL:
;with cte as
(
select rowno = ROW_NUMBER() over(order by pkID), *
from temp
where pkID IN(95,15,14,17,16)
)
update cte
set SortID = rowno--RESULT
select * from Temp
/*
pkID SortID
95 5
15 2
14 1
17 4
16 3
99 9
*/
/*
pkID SortID
95 1
15 2
14 3
17 4
16 5
99 6
*/
而不是:
/*
pkID SortID
95 5
15 2
14 1
17 4
16 3
99 9
*/