如何在SQL服务器中修改重复的字符串?

Question

我正在处理将表中的字符串值列从 15 个字符截断为 10 个字符(这是我想要允许该列的新最大长度(。

表中的一对列上有一个唯一键，这个列就是其中之一。

由于截断，可能会违反这一点。

例如：

| ID |        C1       | C2 |
| -- | --------------- | -- |
| 1  | 123456789012345 |  1 |
| 2  | 123456789012346 |  1 |
| 3  | 123456789012345 |  2 |
| 4  | 123456789012346 |  2 |

假设我在 C1 和 C2 上有一个唯一的密钥。C1 目前是 varchar(15(，但由于我无法控制的原因，它被更改为 varchar(10(。

我必须将 C1 中的值截断为长度为 10 的字符串。但是，如果我只是盲目地这样做，我显然最终会(在上面的例子中(违反唯一键约束。

所以，我知道如何使用以下方法查找所有重复项：

select
t1.ID,
LEFT(t1.C1, 10) as C1,
t1.C2
INTO
#ColumnDuplicates
FROM
t t1
join t t2 on
t1.ID <> t2.ID
AND LEFT(t1.C1, 10) = LEFT(t2.C1, 10)
WHERE
t1.C2 = t2.C2
SELECT * FROM #ColumnDuplicates

参考上表，这个查询会得到我：

| ID |     C1     | C2 |
| -- | ---------- | -- |
| 1  | 1234567890 |  1 |
| 2  | 1234567890 |  1 |
| 3  | 1234567890 |  2 |
| 4  | 1234567890 |  2 |

现在，我不确定如何进行下一步。我需要做的是以某种方式达到这个：

| ID |     C1     | C2 |
| -- | ---------- | -- |
| 1  | 123456_001 |  1 |
| 2  | 123456_002 |  1 |
| 3  | 123456_001 |  2 |
| 4  | 123456_002 |  2 |

实际上，我想为每个 C1 值找到所有重复的 C2 值，然后将最后 4 个字符更改为_[0-9][0-9][0-9]模式，并逐渐将这些重复项从 000(或 001，我真的不在乎哪个用作起点(到最大 999。这将使我有空间处理每个 C2 值大约 999 个重复项，根据我对我正在使用的数据的熟悉程度，我很确定这不会成为问题。

然后我可以轻松地使用此临时表来更新我正在修改的主表中的 C1 值。

我目前对SQL的了解非常基础，所以我真的不知道如何做到这一点。

Answer 1

如果幸运的话，您可以查看前六个字符中的重复项。 我说幸运，因为这假设你永远不会有超过 1000 个这样的重复项：

with toupdate as (
select t.*,
row_number() over (partition by left(c1, 6), c2 order by c2) as seqnum,
count(*) over (partition by left(c1, 6), c2) as cnt
from t
)
update toupdate
set c1 = (case when cnt > 1
then concat(left(c1, 6), '_', format(seqnum, '000'))
else left(c1, 10)
end);

以上对于重复项来说有点悲观。 在使用row_number()之前过滤掉已知的单例可能是有意义的：

with toupdate as (
select t.*,
row_number() over (partition by left(c1, 6), c2,
(case when cnt10 > 1 then 1 else 2 end)
order by c2
) as seqnum,
count(*) over (partition by left(c1, 6), c2,
(case when cnt10 > 1 then 1 else 2 end)
) as cnt6
from (select t.*,
count(*) over (partition by left(c1, 10), c2) as cnt10
from t
) t
)
update toupdate
set c1 = (case when cnt10 > 1
then concat(left(c1, 6), '_', format(seqnum, '000'))
else left(c1, 10)
end);

Answer 2

您可以使用可更新的 CTE 来实现此目的：

CREATE TABLE dbo.YourTable (ID int NOT NULL,
C1 varchar(15) NOT NULL,
C2 int NOT NULL);
CREATE UNIQUE INDEX YourIndex ON dbo.YourTable (C1,C2);
GO
INSERT INTO dbo.YourTable (ID, C1, C2)
VALUES (1,'123456789012345',1),
(2,'123456789012346',1),
(3,'123456789012345',2),
(4,'123456789012346',2);
GO
WITH CTE AS(
SELECT C1,
LEFT(YT.C1,6) + '_' + RIGHT(CONCAT('000',ROW_NUMBER() OVER (ORDER BY YT.C1, YT.C2 ASC)),3) AS NewC1
FROM dbo.YourTable YT
WHERE LEN(YT.C1) > 10) --Unsure if that WHERE is needed
UPDATE CTE
SET C1 = NewC1;
GO
DROP INDEX YourIndex ON dbo.YourTable; --Has to be dropped to alter
ALTER TABLE dbo.YourTable ALTER COLUMN C1 varchar(10) NOT NULL;
GO
CREATE UNIQUE INDEX YourIndex ON dbo.YourTable (C1,C2); --Recreate
GO
SELECT *
FROM dbo.YourTable;
GO
DROP TABLE dbo.YourTable;