需要T-SQL更新语句来动态分配记录号

I have a data set that I got from XML and have it broken out with the following structure:

我有一个从XML获得的数据集,并使用以下结构进行了分析:

[Data Table]

[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}

I have added a column (called recNum) to indicate the distinct record number; however, I have not found a quick way to set the record number for each record. The number of rows that indicate a distinct record can vary, so I want the update statement to be able to handle this. Each "record" has a "column" name that ends with "_pk", so that's how I'm determining the start of each record in the dataset.

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

I have done this successfully with a while loop, but it's way too slow and tables can have millions of records. Example:

我已经成功完成了一个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

Does anybody have a suggestion to do this in a quick manner w/o using a cursor? My ultimate goal is to insert statements into an SQL table (already created) with the following format:

是否有人建议使用光标快速完成此操作?我的最终目标是使用以下格式将语句插入到SQL表(已创建)中:

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

... [updated 2015-06-24 00:26 EDT] This is how far I have gotten thus far... https://drive.google.com/file/d/0B82UP-AIFz_ITlNIb1ZwSFdyODg/view?usp=sharing

... [更新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

... I need to fill in the gaps. Any suggestions?

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

[updated 2015-06-25 09:33 EDT] This is for SQL Server 2008 R2

[更新2015-06-25 09:33 EDT]这是针对SQL Server 2008 R2的

3 个解决方案

#1

First, you want to assign a value for RecNum for all PK using ROW_NUMBER. After that, you want to update the remaining rows with the appropriate RecNum

首先,您要使用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

RESULT

| 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 |

#2

If I understand your question correctly, you can use a correlated subquery like this. If you are using SQL Server 2012 or above, you can use SUM() OVER() as well. Check Edit.

如果我正确理解了您的问题,您可以使用这样的相关子查询。如果您使用的是SQL Server 2012或更高版本,则也可以使用SUM()OVER()。检查了它。

Sample Data

CREATE TABLE Table1
    ([ID] int,rowid int, [Name] varchar(9), [Value] varchar(15));

INSERT INTO Table1
    ([ID], [Name], [Value])
VALUES
    (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');

Query

UPDATE Table1
SET rowid = 
 (SELECT COUNT(ID) FROM Table1 T2 WHERE T2.ID <= Table1.ID AND T2.Name Like '%[_]pk');

SELECT * FROM Table1;

SQL Fiddle

OUTPUT

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

EDIT

For SQL Server 2012 or above

对于SQL Server 2012或更高版本

Query

;WITH CTE AS 
(
SELECT SUM(CASE WHEN Name LIKE '%[_]pk' THEN 1 ELSE 0 END) OVER(ORDER BY ID) recnum,* 
FROM Table1
  )
  UPDATE CTE
  SET rowid = recnum;

  SELECT * FROM Table1;

SQL Fiddle

#3

You can use a cte for that:

您可以使用cte:

;With cte as (
    SELECT [id], Row_number() OVER(Order by [id] As rn
    FROM MyTable
)

UPDATE MyTable
SET recNum = rn
FROM MyTable t
INNER JOIN cte ON(t.[id] = cte.[id])

However, you since already have an id column that seems to have the values you are asking for, you can simply do this:

但是,您已经拥有一个似乎具有您要求的值的id列,您可以简单地执行此操作:

UPDATE MyTable
SET recNum = [id]