使用子查询删除重复的行 [英] Delete duplicate rows using Sub-query

问题描述

我正在使用SQL Server 2014，并利用Microsoft提供的 AdventureWorks2012 示例数据库.

I'm using SQL Server 2014 and utilizing the AdventureWorks2012 sample database provided by Microsoft.

我正在尝试使用下面的子查询(选项#2)删除重复的行:

I'm trying to delete duplicate rows using sub-query below (option #2):

/ *选项#2:SUBQUERY * /

--SELECT * FROM
DELETE SQLPractice.[dbo].[CURRENCY]
WHERE EXISTS (SELECT * 
              FROM
                  (SELECT 
                       NAME,
                       ROW_NUMBER () OVER (PARTITION BY NAME ORDER BY NAME) AS Flag
                   FROM  
                       SQLPractice.[dbo].[CURRENCY]) AS T
              WHERE Flag > 1) 
GO

但是它将删除表中的所有行.

But it deletes all rows from the table.

但是另一个选项(CTE)确实仅删除重复的行.

But the other option (CTE) does delete only the duplicate rows.

/*** Option #3: CTE ***/ 
;WITH RepFlag AS
(
    SELECT 
        NAME,
        ROW_NUMBER () OVER (PARTITION BY NAME ORDER BY NAME) AS Flag
    FROM 
        SQLPractice.[dbo].[CURRENCY]
)
--SELECT * FROM RepFlag
DELETE RepFlag
WHERE Flag > 1

SELECT * 
FROM SQLPractice.[dbo].[CURRENCY]

请使用下面的代码创建您自己的测试表.

Please use the code below to create your own test table.

/*** REMOVING DUPLICATE ROWS OPTION ***/
-- Creating a table 
SELECT TOP 0 *
INTO [dbo].[CURRENCY]
FROM AdventureWorks2012.Sales.Currency
WHERE NAME LIKE  '%A';

-- inserting duplicate rows 
INSERT [dbo].[CURRENCY]
SELECT * FROM AdventureWorks2012.Sales.Currency
WHERE NAME LIKE  '%A';

/***** SELECTING COUNT OF DUPLICATED ROWS *****/ 

/*** Option #1: "GROUP BY" with "HAVING" ***/ 
SELECT 
    NAME, COUNT(*) AS Qty   
FROM 
    SQLPractice.[dbo].[CURRENCY]
GROUP BY 
    NAME
HAVING 
    COUNT(*) >1
GO

推荐答案

如果要使用子查询删除重复的名称，请使用以下方法.

If you wanted to delete the duplicate name using a subquery,use the following method.

DELETE t
FROM  (SELECT  NAME,ROW_NUMBER () OVER (PARTITION BY NAME ORDER BY NAME) AS Flag
              FROM  SQLPractice.[dbo].[CURRENCY]
            ) t
WHERE t.Flag > 1
GO

您还可以使用c ommon表表达式(CTE)来实现此目的.

You can also achieve this using common table expression (CTE).

;WITH cte_1
AS (SELECT  NAME,ROW_NUMBER () OVER (PARTITION BY NAME ORDER BY NAME) AS Flag
              FROM  SQLPractice.[dbo].[CURRENCY]
            ) 
DELETE FROM cte_1
WHERE Flag > 1

这篇关于使用子查询删除重复的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！