您可以使用row_number()找出哪个用户具有最高优先级. SQL Server让您以可更新的CTE进行此操作，因此查询如下所示:

You can figure out which user has the highest priority by using row_number(). SQL Server let's you do this in an updatable CTE, so the query looks like this:

with toupdate as (
      select t.*,
             row_number() over (partition by projectid
                                order by (case when userid = 1 then 1
                                               when userid = 2 then 2
                                               when userid = 3 then 3
                                               else 4
                                          end
                                         )
                               ) as PriorityForLead
      from table t
     )
update toupdate
    set RoleId = 11
    where PriorityForLead = 1;