基于row_number更新列
基于row_number更新列
提问于 2016-12-02 06:59:08
我为我的表创建了一个默认值为0的列。我想用正确的序列号更新那个表。我能用Row_Number做到这点吗？
我的表格是：
PersonID | Code | Sequence Number
---------+--------+------------
10 | D112 | 0
10 | D112 | 0
10 | D112 | 0
10 | E110 | 0
10 | E110 | 0
10 | E110 | 0
10 | D112 | 0
10 | D112 | 0
10 | D112 | 0
10 | E110 | 0
10 | E110 | 0
10 | E110 | 0我希望我的表是这样的：
PersonID | Code | Sequence Number
---------+--------+------------
10 | D112 | 1
10 | D112 | 1
10 | D112 | 1
10 | E110 | 2
10 | E110 | 2
10 | E110 | 2
11 | M490 | 1
11 | M490 | 1
11 | M490 | 1
11 | N550 | 2
11 | N550 | 2
11 | N550 | 2这是我拥有的代码，但不确定这是否正确。
WITH CTE AS (
SELECT
t.Sequence Number,
ROW_NUMBER() OVER (PARTITION BY t.PersonID, t.Code ORDER BY t.PersonID) AS RN
FROM Table AS t )
UPDATE CTE
SET Sequence Number = RN回答 1
Stack Overflow用户
发布于 2016-12-02 07:26:06
DECLARE @Table AS TABLE (PersonId INt, Code CHAR(4), SequenceNumber INT)
INSERT INTO @Table VALUES (10,'D112',0),(10,'D112',0),(10,'D112',0),(10,'E110',0)
,(10,'E110',0),(10,'E110',0),(10,'D112',0),(10,'D112',0),(10,'D112',0),(10,'E110',0)
,(10,'E110',0),(10,'E110',0)
;WITH CTE AS (
SELECT
PersonId
,Code
,SequenceNumber
,DENSE_RANK() OVER (PARTITION BY t.PersonID ORDER BY t.Code ) AS RN
FROM
@Table AS t)
UPDATE cte
SET SequenceNumber = RN
SELECT *
FROm
@Table您非常接近，只需要将PARTITION BY和ORDER BY稍作不同，然后使用DENSE_RANK()而不是ROW_NUMBER()来处理关系。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/40921957
复制相关文章
相似问题
腾讯云开发者
