将DELETE添加到返回重复记录的查询中SQL Server

i need your help.

我需要你的帮助。

I've this query that detect a duplex records:

我有这个查询检测双工记录：

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

So i need to delete the results, so i've try this:

所以我需要删除结果，所以我试试这个：

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

but doesn't work. Could tell me an help ? thank.

但不起作用。可以告诉我一个帮助吗？谢谢。

3 个解决方案

#1

Try this query:

试试这个查询：

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

#2

Since there is no ID Column (Primary Key) in your question, assuming there is no ID column. So below CTE will delete duplicate records.

由于您的问题中没有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

I'm assuming you want to JUST remove the duplicate rows not all the rows relating to the duplicate rows.

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

You can use ROW_NUMBER here. You haven't provided the whole schema for the USERS table so here's an example

你可以在这里使用ROW_NUMBER。您还没有为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

you can change this query to a Common Table Expression and then you can DELETE from it

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

;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

The RN > 1 will remove the duplicate records only

RN> 1将仅删除重复记录

#1