TSQL-为重复记录生成序列号

Question

使用 SQL Server 2000，考虑一个包含超过 400,000 条记录的源表。

任务是选择每个 regno 条目，并带有递增的动态 rowid 或序列号（对于那些具有重复项或多个条目的条目）。对于源表中没有重复条目的情况，rowid 应该简单地为 null。

以下是所需输出的示例：

    regno   rowid
    100      1
    100      2
    100      3
    200      null
    300      4
    300      5
    400      null
    500      null
    600      6
    600      7

问题： 在 SQL Server 2000 中，什么查询可以使用 TSQL 执行所需的序列递增？

Answer 1

如果我的评论是正确的（600 应该是 6,7），那么看看这个

DECLARE @Table TABLE(
        regno INT,
        rowid INT
)

INSERT INTO @Table (regno,rowid) SELECT 100, NULL
INSERT INTO @Table (regno,rowid) SELECT 100, NULL
INSERT INTO @Table (regno,rowid) SELECT 100, NULL
INSERT INTO @Table (regno,rowid) SELECT 200, NULL
INSERT INTO @Table (regno,rowid) SELECT 300, NULL
INSERT INTO @Table (regno,rowid) SELECT 300, NULL
INSERT INTO @Table (regno,rowid) SELECT 400, NULL
INSERT INTO @Table (regno,rowid) SELECT 500, NULL
INSERT INTO @Table (regno,rowid) SELECT 600, NULL
INSERT INTO @Table (regno,rowid) SELECT 600, NULL

DECLARE @TempTable TABLE(
        ID INT IDENTITY(1,1),
        regno INT
)

INSERT INTO @TempTable (regno)
SELECT  regno
FROM    @Table

SELECT  regno,
        CASE 
            WHEN (SELECT COUNT(1) FROM @TempTable WHERE regno = t.regno) = 1 
                THEN NULL 
            ELSE (SELECT COUNT(1) FROM @TempTable WHERE regno = t.regno) - (SELECT COUNT(1) FROM @TempTable WHERE regno = t.regno AND ID > t.ID) +
            (SELECT COUNT(1) FROM @TempTable WHERE regno < t.regno AND regno IN (SELECT regno FROM @TempTable GROUP BY regno having COUNT(1) > 1))
        END Val     
FROM    @TempTable t

Answer 2

提取非唯一记录的查询将是

select regno,count(*) from table group by regno having count(*) > 1

我对 MSSQL 的了解不够，无法告诉您如何生成递增序列号来更新与查询匹配的记录。

Answer 3

没有临时表：

DECLARE @Table TABLE(
        regno INT
)

INSERT INTO @Table (regno) SELECT 100
INSERT INTO @Table (regno) SELECT 100
INSERT INTO @Table (regno) SELECT 100
INSERT INTO @Table (regno) SELECT 200
INSERT INTO @Table (regno) SELECT 300
INSERT INTO @Table (regno) SELECT 300
INSERT INTO @Table (regno) SELECT 400
INSERT INTO @Table (regno) SELECT 500
INSERT INTO @Table (regno) SELECT 600
INSERT INTO @Table (regno) SELECT 600

select regno, null as rowid from @Table group by regno having count(*) = 1
union 
select regno, row_number() OVER (ORDER BY a.regno) as rowid
   from @table a
   where regno in (select regno from @table group by regno having count(*) > 1)

regno       rowid
----------- --------------------
100         1
100         2
100         3
200         NULL
300         4
300         5
400         NULL
500         NULL
600         6
600         7

哎呀 - 直到发布此内容后才发现您想在 SQL 2000 中执行此操作...请忽略我的查询。在 SQL 2000 中，您需要一个临时表来生成序列。