如何在SQL Server中删除重复行？

尝试使用：

SELECT linkorder
    ,Row_Number() OVER (
        PARTITION BY linkorder ORDER BY linkorder DESC
        ) AS RowNum
FROM u_links

SET rowcount 1;
DELETE FROM t1 WHERE myprimarykey=1;

myprimarykey是该行��标识符。

我将rowcount设置为1，因为我只有两行重复。如果我有3行重复，则会将rowcount设置为2，以便删除它看到的前两行，并仅在表t1中留下一行。

1. 删除所有与LargeSourceTable相关的依赖视图
2. 您可以使用SQL管理工作室找到依赖项，右键单击表格，然后单击“查看依赖项”
3. 重命名表格：
4. sp_rename 'LargeSourceTable'，'LargeSourceTable_Temp'; GO
5. 重新创建LargeSourceTable，但现在要添加一个主键，将定义重复值的所有列添加WITH（IGNORE_DUP_KEY = ON）

6. 例如：

   CREATE TABLE [dbo]。[LargeSourceTable] ( ID int IDENTITY(1,1), [CreateDate] DATETIME CONSTRAINT [DF_LargeSourceTable_CreateDate] DEFAULT (getdate()) NOT NULL, [Column1] CHAR (36) NOT NULL, [Column2] NVARCHAR (100) NOT NULL, [Column3] CHAR (36) NOT NULL, PRIMARY KEY (Column1, Column2) WITH (IGNORE_DUP_KEY = ON) ); GO

7. 再次创建您在第一步删除的视图以针对新创建的表格

8. 现在，运行以下SQL脚本，您将在每页1,000,000行中看到结果，您可以更改每页的行数以更频繁地查看结果。

9. 请注意，我设置了IDENTITY_INSERT，因为其中一个列包含自动增量ID，我也正在复制它

SET IDENTITY_INSERT LargeSourceTable ON DECLARE @PageNumber AS INT, @RowspPage AS INT DECLARE @TotalRows AS INT declare @dt varchar(19) SET @PageNumber = 0 SET @RowspPage = 1000000 select @TotalRows = count (*) from LargeSourceTable_TEMP

While ((@PageNumber - 1) * @RowspPage < @TotalRows )
Begin
    begin transaction tran_inner
        ; with cte as
        (
            SELECT * FROM LargeSourceTable_TEMP ORDER BY ID
            OFFSET ((@PageNumber) * @RowspPage) ROWS
            FETCH NEXT @RowspPage ROWS ONLY
        )

        INSERT INTO LargeSourceTable 
        (
             ID                     
            ,[CreateDate]       
            ,[Column1]   
            ,[Column2] 
            ,[Column3]       
        )       
        select 
             ID                     
            ,[CreateDate]       
            ,[Column1]   
            ,[Column2] 
            ,[Column3]       
        from cte

    commit transaction tran_inner

    PRINT 'Page: ' + convert(varchar(10), @PageNumber)
    PRINT 'Transfered: ' + convert(varchar(20), @PageNumber * @RowspPage)
    PRINT 'Of: ' + convert(varchar(20), @TotalRows)

    SELECT @dt = convert(varchar(19), getdate(), 121)
    RAISERROR('Inserted on: %s', 0, 1, @dt) WITH NOWAIT
    SET @PageNumber = @PageNumber + 1
End

SET IDENTITY_INSERT LargeSourceTable OFF

with myCTE
as

(
select productName,ROW_NUMBER() over(PARTITION BY productName order by slno) as Duplicate from productDetails
)
Delete from myCTE where Duplicate>1

-- this query will keep only one instance of a duplicate record.
;WITH cte
     AS (SELECT ROW_NUMBER() OVER (PARTITION BY col1, col2, col3-- based on what? --can be multiple columns
                                       ORDER BY ( SELECT 0)) RN
         FROM   Mytable)



delete  FROM cte
WHERE  RN > 1

DELETE prg.Person WHERE Id IN (
SELECT dublicateRow.Id FROM
(
select MIN(Id) MinId, NationalCode
 from  prg.Person group by NationalCode  having count(NationalCode ) > 1
 ) GroupSelect
 JOIN  prg.Person dublicateRow ON dublicateRow.NationalCode = GroupSelect.NationalCode 
 WHERE dublicateRow.Id <> GroupSelect.MinId)

这可能对你的情况有所帮助

DELETE t1 FROM table t1 INNER JOIN table t2 WHERE t1.id > t2.id AND t1.col1 = t2.col1 

从一个包含数百万条记录的大表中删除重复数据可能需要很长时间。我建议您将所选行批量插入到临时表中，而不是直接删除。

修改后的答案：批量插入选定行到临时表中，而非直接删除，可以更快地删除包含数百万条记录的大表中的重复数据。

--REWRITING YOUR CODE(TAKE NOTE OF THE 3RD LINE) WITH CTE AS(SELECT NAME,ROW_NUMBER() 
OVER (PARTITION BY NAME ORDER BY NAME) ID FROM @TB) SELECT * INTO #unique_records FROM 
CTE WHERE ID =1;

WITH CTE AS (
    SELECT col1, col2, col3, col4, col5, col6, col7,
           ROW_NUMBER() OVER (PARTITION BY col1, col2, col3, col4, col5, col6, col7 ORDER BY (SELECT 0)) AS RowNumber
    FROM YourTable
)
DELETE FROM CTE WHERE RowNumber > 1;

将YourTable替换为您表的实际名称。该查询根据col1、col2、col3、col4、col5、col6和col7中值的组合，为每个集合内的重复行分配一个行号。然后删除行号大于1的行。

您可以利用dbForge Studio的 SQL编辑器来执行查询并高效处理各种其他数据库管理任务。

请在SELECT命令后简单地加上关键字DISTINCT，例如：

SELECT DISTICNT ColumnOne, ColumnTwo, ColumnThree
 FROM YourTable