T-SQL:如何使单元格值唯一?

编辑:我已将查询更改为使用您的域名,并添加了一个“仅选择”查询供您预览。

这其实很容易做到…只需将row_number()与partition子句一起使用:

UPDATE Persons SET UniqueName = temp.DeDupded FROM
    (SELECT ID,
        CASE WHEN ROW_NUMBER() OVER
            (PARTITION BY UniqueName ORDER BY UniqueName) = 1 THEN UniqueName
        ELSE UniqueName + CONVERT(VARCHAR, ROW_NUMBER()
            OVER (PARTITION BY UniqueName ORDER BY UniqueName)-1) END AS DeDupded
    FROM Persons) temp
WHERE Persons.ID = temp.ID

如果您想要一个“仅选择”,那么这里是:

SELECT ID,
    CASE WHEN ROW_NUMBER() OVER
        (PARTITION BY UniqueName ORDER BY UniqueName) = 1 THEN UniqueName
    ELSE UniqueName + CONVERT(VARCHAR, ROW_NUMBER()
        OVER (PARTITION BY UniqueName ORDER BY UniqueName)-1) END AS DeDupded
FROM Persons

再次编辑:如果要查找SQL Server 2000解决方案…

CREATE TABLE #Persons ( ID INT IDENTITY(1, 1), UniqueName VARCHAR(100) )

INSERT INTO #Persons VALUES ('Bob')
INSERT INTO #Persons VALUES ('Bob')
INSERT INTO #Persons VALUES ('Bob')
INSERT INTO #Persons VALUES ('John')
INSERT INTO #Persons VALUES ('John')

SELECT
    ID,
    CASE WHEN Position = 0 THEN UniqueName
        ELSE UniqueName + (CONVERT(VARCHAR, Position))
    END AS UniqueName
FROM
    (SELECT
        ID,
        UniqueName,
        (SELECT COUNT(*) FROM #Persons p2 WHERE
            p1.UniqueName = p2.UniqueName AND p1.ID > p2.ID) AS Position
    FROM
        #Persons p1) _temp

DROP TABLE #Persons

这就像是一个非常清晰的 trigger (插入,更新)。