更新时间:2023-02-03 07:45:16
这样的事情:
Something like this:
WHILE ((SELECT TOP (1) [Stuid] FROM [MyTable] GROUP BY [Stuid] HAVING Count(*)>1) > 0)
BEGIN
DELETE TOP (1) FROM MyTable
WHERE ((Stuid) In (SELECT [Stuid] FROM [MyTable] As Tmp GROUP BY [Stuid] HAVING Count(*)>1 ))
END
您将需要添加一个唯一的列到你的桌子,这样你就可以区分同一行的不同副本:
You're going to need to add a unique column to your table, so that you can distinguish between different copies of the same row:
ALTER TABLE YourTable
ADD TempRowNumber int IDENTITY(1, 1) NOT NULL;
然后你需要找到并删除重复的行。假设您只关心 Stuid
列:
Then you need to find and delete the duplicate rows. Assuming you only care about the Stuid
column:
DELETE
FROM
T1
FROM
YourTable As T1
WHERE
Exists
(
SELECT 1
FROM YourTable As T2
WHERE T2.Stuid = T1.Stuid
And T2.TempRowNumber < T1.TempRowNumber
)
;
如有必要,您可以删除临时唯一列,并添加适当的唯一约束:
If necessary, you can then drop the temporary unique column, and add a proper unique constraint:
ALTER TABLE YourTable
DROP COLUMN TempRowNumber;
GO
ALTER TABLE YourTable
WITH CHECK
ADD CONSTRAINT UX_YourTable_Stuid UNIQUE (Stuid);
GO
检查出来:https://support.microsoft.com/en-us/kb/139444 [ ^ ]