sorry! ;with cte as ( select ProjectID,sort,rid=row_number() over (order by ProjectID) )update ProjectInfo a set a.sort = b.rid from cte b where a.ProjectID = b.ProjectID
UPDATE ProjectInfo SET SORT=T.ID FROM ( SELECT projectID,ROW_NUMBER() OVER(ORDER BY projectID) ID FROM ProjectInfo )T WHERE ProjectInfo.projectID=T.projectID
update tableName, (select (@rank:=@rank+1) rank, projectId from( select @rank:=0, projectId from tableName order by projectId) tmp) tmp2 set tableName.sort = tmp2.rank where tableName.projectId = tmp2.projectId
不行的报错:You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(select (@rank:=@rank+1) rank,ProjectId FROM(select @rank:=0,ProjectId from Proj' at line 1
update ProjectInfo v set Sort=(select count(*) from ProjectInfo where projectID<=v.projectID) where ProjectID in(1,2,3,4,5,6)
;with cte as
(
select ProjectID,sort,rid=row_number() over (order by ProjectID)
)update ProjectInfo a
set a.sort = b.rid
from cte b
where a.ProjectID = b.ProjectID
SELECT projectID,ROW_NUMBER() OVER(ORDER BY projectID) ID FROM ProjectInfo
)T
WHERE ProjectInfo.projectID=T.projectID
(select (@rank:=@rank+1) rank, projectId from(
select @rank:=0, projectId from tableName order by projectId) tmp) tmp2
set tableName.sort = tmp2.rank where tableName.projectId = tmp2.projectId
不行的报错:You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(select (@rank:=@rank+1) rank,ProjectId FROM(select @rank:=0,ProjectId from Proj' at line 1
set Sort=(select count(*) from ProjectInfo where projectID<=v.projectID)
where ProjectID in(1,2,3,4,5,6)