根据其他三列的出现情况，将列添加为int计数.如何将ROW_NUMBER与插入一起使用

Question

我需要一个额外的列，它计算其他三列组合的唯一出现次数。我可以用ROW_NUMBER来做这件事，但我想更新表来存储值。

这是可以看到的：

SELECT
ROW_NUMBER() OVER (PARTITION BY EntryDate, DocNo, Item ORDER BY EntryUID) AS x, 
*
FROM
SourceTable

这不起更新作用：

WITH TableA AS 
(
SELECT
ROW_NUMBER() OVER (PARTITION BY EntryDate, DocNo, Item ORDER BY EntryUID) AS x,
*
FROM 
SourceTable
)
UPDATE SourceTable
SET SourceTable.NEWCount = TableA.x
WHERE SourceTable.EntryUID = TableA.EntryUID

我收到这个错误消息：

Msg4104，级别16，状态1，第17行
多部分标识符"表A。条目UID"；无法绑定

Answer 1

或者，如果您想使用原始方法，您需要在UPDATE语句中包含CTE，类似于以下内容：

WITH TableA AS 
(
SELECT
ROW_NUMBER() OVER (PARTITION BY EntryDate, DocNo, Item ORDER BY EntryUID) AS x,
*
FROM 
SourceTable
)
UPDATE src
SET NEWCount = TableA.x
FROM SourceTable src
INNER JOIN TableA ON src.EntryUID = TableA.EntryUID

Answer 2

假设您正在运行SQL Server，如错误消息所示，您可以简单地使用可更新的CTE:

with cte as (
select newcount, 
row_number () over(partition by entrydate, docno, item order by entryuid ) as rn
from sourcetable
)
update cte set newcount = rn