Delete Duplicated Row in SQL Server

最新推荐文章于 2025-04-15 13:52:25 发布

iteye_21322

最新推荐文章于 2025-04-15 13:52:25 发布

阅读量134

点赞数

CC 4.0 BY-SA版权

分类专栏： SQL Server

本文链接：https://blog.youkuaiyun.com/iteye_21322/article/details/82577530

SQL Server 专栏收录该内容

7 篇文章

订阅专栏

本文介绍了一种使用SQL脚本的方法来消除表中的重复记录，并通过创建临时表来保留每组记录中MyTime字段的最大值。具体步骤包括截断临时表、创建包含最大MyTime值的公共表表达式(CTE)、从源表选择目标列、将选定记录插入到临时表、清空源表以及最后将清理后的记录重新插入源表。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1. Truncate NoDup table

2. Create CTE1 to get the max MyTime group by FileName and Month

TRUNCATE TABLE NoDup;

;WITH CTE1 AS
(SELECT FileName, Max(MyTime) AS MaxMyTime
FROM SourceTableDup
GROUP BY FileName, CONVERT(VARCHAR(10),MyTime,10))

3. Select the target columns from SourceTable

4. Join the CTE1 on FileName and MyTime

5. Insert into NoDup table

INSERT INTO NoDup
SELECT Col1, Col2, Col3
FROM SourceTableDup INNER JOIN CTE1 
ON SourceTableDup.FileName = CTE1.TargetFileName
AND SourceTableDup.MyTime = CTE1.MaxMyTime

6. Truncate source table

7. Insert into source table from NoDup table

TRUNCATE TABLE SourceTableDup;
INSERT INTO SourceTableDup
SELECT * FROM NoDup;

Reference:

http://support.microsoft.com/kb/139444

http://www.orafaq.com/faq/how_does_one_eliminate_duplicates_rows_from_a_table