SQL更新语句自连接

Question

Gid Rid Cid PriorityId

1   7   11  NULL
1   7   12  NULL
1   8   13  NULL
1   9   25  NULL
1   13  18  NULL
1   13  19  NULL
1   14  23  NULL
1   14  24  NULL

我需要将priorityId设置为以下。有人能建议一个sql更新语句吗？我想我需要一个由Rid和Gid组成的组或自加入

Gid Rid Cid PriorityId

1   7   11  1
1   7   12  2
1   8   13  1
1   9   25  1
1   13  18  1
1   13  19  2
1   14  23  1
1   14  24  2

非常感谢。

Answer 1

我会这样写更新：

with toupdate as (
    Select t.*
           Row_Number() Over (Partition By Gid, Rid Order By Gid, Rid, Cid) As new_PriorityId
    From Table t
   )
update toupdate
    set PriorityId = new_PriorityID;

Answer 2

对于SQL Server 2012，您可以使用以下语句，将Gid和Rid作为分区使用：

Update  T
Set     PriorityId = D.PriorityId
From    
(   
    Select  Gid, Rid, Cid,
            Row_Number() Over (Partition By Gid, Rid Order By Gid, Rid, Cid) As PriorityId
    From    Table
) D
Join    Table   T   On  T.Gid = D.Gid 
                    And T.Rid = D.Rid 
                    And T.Cid = D.Cid 

下面的内部语句应该在不更新表的情况下向您显示结果：

Select  Gid, Rid, Cid,
        Row_Number() Over (Partition By Gid, Rid Order By Gid, Rid, Cid) As PriorityId
From    Table