select * from tb where id in(95,15,14,21,6) order by charindex(','+ltrim(Id)+',',',95,15,14,21,6,')
在select中的结果是对的,但是在update中就不对了,怎么办? UPDATE [WebSiteCluster].[dbo].[WebSite_System_Component] SET SortID=@i,@i=@i+1 WHERE pkID IN(95,15,14,17,16)
DECLARE @i INT; SET @i=0 DECLARE @Str varchar(100); SET @Str='95,15,14,17,16'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,这样按顺序,将它的顺序ID存放。 如: pkID SortID 95 1 15 2 14 3 17 4 16 5
如果就这几个数字,那么可以 order by case when pkid=95 then 1 when pkid=15 then 2 when pkid=14 then 3 when pkid=17 then 4 else 5 end 这个试试
UPDATE [WebSiteCluster].[dbo].[WebSite_System_Component] SET SortID=charindex(','+ltrim(Id)+',',',95,15,14,21,6,') WHERE pkID IN(95,15,14,17,16)UPDATE [WebSiteCluster].[dbo].[WebSite_System_Component] SET SortID=(select Count(*) from [WebSiteCluster].[dbo].[WebSite_System_Component] a where a.SortID<=SortID and a.pkID IN(95,15,14,17,16) ) WHERE pkID IN(95,15,14,17,16)
order by charindex(','+ltrim(Id)+',',',95,15,14,21,6,')
UPDATE [WebSiteCluster].[dbo].[WebSite_System_Component]
SET SortID=@i,@i=@i+1
WHERE pkID IN(95,15,14,17,16)
DECLARE @Str varchar(100); SET @Str='95,15,14,17,16'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,这样按顺序,将它的顺序ID存放。
如:
pkID SortID
95 1
15 2
14 3
17 4
16 5
order by case when pkid=95 then 1
when pkid=15 then 2
when pkid=14 then 3
when pkid=17 then 4
else 5
end
这个试试
UPDATE [WebSiteCluster].[dbo].[WebSite_System_Component]
SET SortID=charindex(','+ltrim(Id)+',',',95,15,14,21,6,')
WHERE pkID IN(95,15,14,17,16)UPDATE [WebSiteCluster].[dbo].[WebSite_System_Component]
SET SortID=(select Count(*) from [WebSiteCluster].[dbo].[WebSite_System_Component] a where a.SortID<=SortID and a.pkID IN(95,15,14,17,16) )
WHERE pkID IN(95,15,14,17,16)
后面的SortID的值,应该是:1,2,3,4,5,6……这样的顺序排列。