本文介绍了具有多个条件的更新. SQL 2008的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一张桌子...
ProjectID UserID RoleID
101 1 10
101 2 10
102 2 10
102 3 10
103 1 10
当前只有一种类型的角色,即角色"10",但我想添加一个新角色,即角色"11",它将作为主角.因此,凡是具有"10"角色的用户的项目,都应具有领导作用.被选择为潜在客户的用户将基于优先级列表,在本示例中,我们将顺序定为1、2、3.
Currently there is only one type of Role, role '10', but I'm wanting to add a new role, role '11', which will act as a lead. So any project that has a user with the role of '10', should have a lead. The user chosen to be lead will be based on a priorty list, in this example we'll say the order is 1, 2, 3.
预期结果...
ProjectID UserID RoleID
101 1 11
101 2 10
102 2 11
102 3 10
103 1 11
推荐答案
您可以使用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;
这篇关于具有多个条件的更新. SQL 2008的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!