代码之家 › 专栏 › 技术社区 › Mr. Developer

向返回重复记录的查询添加DELETE SQL Server

duplex sql-delete sql-server sql

0

Mr. Developer · 技术社区 · 6 年前

我需要你的帮助。

我有一个查询可以检测双面打印记录:

SELECT
    name, email, COUNT(*)
FROM
    users
GROUP BY
    name, email
HAVING 
    COUNT(*) > 1

因此,我需要删除结果,因此我尝试以下操作:

Delete FROM
    users
GROUP BY
    name, email
HAVING 
    COUNT(*) > 1

但不起作用。能告诉我一个帮助吗?谢谢。

3 回复 | 直到 6 年前

1

2

Ahmed Negm 6 年前

试试这个 query :

DELETE
FROM users
WHERE ID NOT IN
(SELECT MAX(ID)
FROM users
GROUP BY name, email)

2

1

Shakeer Mirza 6 年前

因为您的问题中没有ID列(主键),所以假设没有ID列。所以下面的CTE将删除重复的记录。

;WITH CTE AS (
SELECT name
, email
, ROW_NUMBER() Over(Partition by name, email Order by(Select 1)) as Sno
FROM users
)
DELETE FROM CTE WHERE SNO>1

3

0

Mazhar 6 年前

我假设你想只是删除重复行,而不是所有与重复行相关的行。

您可以在这里使用行号。您尚未提供 USERS 表,下面是一个示例

CREATE TABLE dbo.Users(
     Id INT IDENTITY(1,1)
    ,[Name] NVARCHAR(50)
    ,eMail NVARCHAR(50)
    ,DateCreated DATETIME
)

SELECT
     Id
    ,[Name]
    ,eMail
    ,DateCreated
    ,RN = ROW_NUMBER()OVER(PARTITION BY Name, eMail ORDER BY DateCreated ASC)  
FROM dbo.Users

可以将此查询更改为公用表表达式,然后从中删除

;WITH cteDups
AS(
    SELECT
         Id
        ,[Name]
        ,eMail
        ,DateCreated
        ,RN = ROW_NUMBER()OVER(PARTITION BY Name, eMail ORDER BY DateCreated ASC)  
    FROM dbo.Users
)
DELETE FROM cteDups WHERE RN > 1

这个 RN > 1 只删除重复记录