sql-server - 需要T-SQL更新语句动态分配一个记录号

Question

我有一个从 XML 中获取的数据集，并使用以下结构对其进行了分解:

[数据表]

[ID] [Name]     [Value]
1    ad1_pk     1
2    ad1_addr1  123 Easy Street
3    ad1_pk     2
4    ad1_addr1  99 US31
5    ad1_atfk   6
6    ad1_pk     3
... {and so on}

我添加了一列(称为 recNum)来指示不同的记录编号；但是，我还没有找到一种快速的方法来为每条记录设置记录号。表示不同记录的行数可能会有所不同，因此我希望更新语句能够处理这个问题。每条“记录”都有一个以“_pk”结尾的“列”名称，因此这就是我确定数据集中每条记录开始的方式。

我已经用 while 循环成功地完成了这项工作，但是它太慢了而且表格可能有数百万条记录。示例:

DECLARE @maxRowID INT = (SELECT MAX(ID) FROM myTable)
DECLARE @i INT = 1
DECLARE @currentRecordID INT = 1

WHILE @i<@maxRowID AND @i<100 BEGIN
    IF (SELECT RIGHT(name,3) [name] FROM myTable WHERE ID=@i)='_pk' AND @i>1 BEGIN
        SET @currentRecordID = (SELECT DISTINCT value FROM myTable WHERE id=@i)
        RAISERROR('Record=%i',0,1,@currentRecordID) WITH NOWAIT
        UPDATE z2
        SET recNum=@currentRecordID
        FROM myTable z2
        WHERE id=@i
    END ELSE BEGIN
        UPDATE z2
        SET recNum=@currentRecordID
        FROM myTable z2
        WHERE id=@i
    END
    SET @i = @i+1
END

有没有人建议在不使用光标的情况下快速执行此操作？我的最终目标是将语句插入到具有以下格式的 SQL 表(已创建)中:

insert into myNewTable ({column name list}) VALUES ({value list})

...[更新于美国东部时间 2015-06-24 00:26]这就是我到目前为止的进展...... https://drive.google.com/file/d/0B82UP-AIFz_ITlNIb1ZwSFdyODg/view?usp=sharing

SELECT TOP 100
z2.ID,z2.Name,z2.Value,CASE WHEN z2.ID=RecIDs.ID THEN z2.Value ELSE NULL END RecNum
FROM MyTable 2
LEFT JOIN (
    SELECT DENSE_RANK() OVER (ORDER BY ID) drn,ID FROM MyTable
    WHERE RIGHT(name,3)='_pk'
) RecIDs ON RecIDs.ID = z2.ID
ORDER BY ID

...我需要填补空白。有什么建议吗？

[更新于 2015-06-25 09:33 EDT]这是用于 SQL Server 2008 R2

Answer 1

首先，您要为所有使用ROW_NUMBER 的PK 分配RecNum 值。之后，您想用适当的 RecNum

更新剩余的行

SQL Fiddle

WITH CtePKs AS(
    SELECT *,
        RN = ROW_NUMBER() OVER(ORDER BY ID)
    FROM z2
    WHERE RIGHT(Name, 3) = '_pk'
)
UPDATE CtePKs SET RecNum = RN 

UPDATE z
    SET RecNum = x.RecNum
FROM z2 z
OUTER APPLY(
    SELECT TOP 1 Id, RecNum
    FROM z2
    WHERE
        ID < z.ID 
        AND RecNum IS NOT NULL
    ORDER BY ID DESC
)x
WHERE z.RecNum IS NULL

结果

| ID |      Name |           Value | RecNum |
|----|-----------|-----------------|--------|
|  1 |    ad1_pk |               1 |      1 |
|  2 | ad1_addr1 | 123 Easy Street |      1 |
|  3 |    ad1_pk |               2 |      2 |
|  4 | ad1_addr1 |         99 US31 |      2 |
|  5 |  ad1_atfk |               6 |      2 |
|  6 |    ad1_pk |               3 |      3 |