将相同的ID分配给SQL Server中的重复项 [英] Assign identical ID to duplicates in SQL server

问题描述

我想创建一个更新查询，该查询将为表中的值分配增量ID.但是，重复的值应具有相同的ID.

I would like to create an update query that would assign incremental IDs to values in my table. However, duplicate values should receive the same ID.

MyTable:

pk  Word   ID  
1   Dummy   null  
2   Dummy   null
3   dummy   null  
4   dummy   null  
5   Hello   null  
6   Hello   null  
7   Test7   null  

预期结果:

pk  Word   ID  
1   Dummy   1  
2   Dummy   1  
3   dummy   2  
4   dummy   2  
5   Hello   3  
6   Hello   3  
7   Test7   4  

提前谢谢！

推荐答案

您可以创建一个带有自动递增ID字段和单词的表.

You can create a table with an auto increment id field an the word.

MySQL:

CREATE TABLE secondTable (
id  int NOT NULL AUTO_INCREMENT,
word  varchar(255) NULL,
PRIMARY KEY (id)
);

MSSQL:

CREATE TABLE [secondTable] (
[id] int NOT NULL IDENTITY(1,1) ,
[word] varchar NULL ,
PRIMARY KEY ([id])
)

然后，将您的word的不同值插入您的secondTable中.

After that you insert the distinct values of word in your secondTable.

INSERT INTO secondTable (word) SELECT distinct word FROM MyTable;

最后，您可以使用分组ID更新表:

At last you can update your table with the grouping IDs:

UPDATE MyTable 
SET ID = (SELECT id from secondTable where MyTable.word = secondTable.word)

这篇关于将相同的ID分配给SQL Server中的重复项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！