mysql - 在MySql中查找重复邮件并根据条件删除某些邮件

Question

我看到了很多查找重复记录并删除它们的方法，但我找不到我正在寻找的方法，即根据特定条件删除它们。

下面的第一个 SQL 代码取自另一篇文章，在查找重复项方面效果很好。但是，我对第二部分有疑问。

<cfquery datasource="mydatabase" name="duplist">
SELECT  a.*, b.totalCount AS Duplicate
FROM    mytable a
        INNER JOIN
        (
            SELECT  email, COUNT(*) totalCount
            FROM    mytable
            GROUP   BY email
            HAVING  COUNT(*) > 1
        ) b ON a.email = b.email
</cfquery>

<cfoutput query="duplist">
    <CFQUERY DATASOURCE="mydatabase" name="dlist">
    SELECT * FROM mytable
    WHERE userid = '#userid#'
    AND activedate is null
    </CFQUERY>
</cfoutput> 

<cfoutput>
Total To Be Deleted: #dlist.recordcount#
</cfoutput>

我正在尝试使用 SELECT(在将其替换为 DELETE 之前)基于具有重复电子邮件记录的输出查询“duplist”，仅那些没有“activedate”的重复用户电子邮件。我只得到一条记录(在#dlist.recordcount# 中)，这是最后一条记录!查询不应该遍历它们吗？

已编辑:

经过一些反馈并检查记录计数没有正确计数，因为它在循环之外。它将在没有开始日期的情况下检索这些记录。但是，现在真正的问题是在以下场景中:

ID   USERID   EMAIL          STARTDATE
1    user1    test@test.com 
2    user2    test@test.com  11/01/2014
3    user3    test@test.com 
4    user4    test@test.com  11/02/2014 
5    user5    me@mydomain.com
6    user6    me@mydomain.com

运行上面的代码会列出这6条重复邮件的记录。第二个查询将选择(删除)那些没有开始日期的记录，即记录 1、3、5 和 6。两个问题:

1. 记录 1 和 3 将被删除，但 2 和 4 仍然是重复的
2. 记录 5 和 6 将被删除，我的电子邮件将没有用户。我需要保留其中一个。

如何修改代码来应对场景？我想保留记录 #4 和 #6，因为 4 比 2 新，6 比 6 新。

4    user4    test@test.com  11/02/2014 
6    user6    me@mydomain.com

Answer 1

可以使用Rank()函数对数据进行排序，删除排名不在第一位的行。

Oracle 相当于 Rank() 查询以选择 #4 和 #6 数据:

SELECT * FROM
(
SELECT USERNAME, EMAIL, START_DATE, 
        RANK() OVER (PARTITION BY EMAIL ORDER BY START_DATE DESC NULLS LAST, ROWNUM DESC) AS RANK
FROM TEMP_SOL
)
WHERE RANK = 1

可以使用此链接导出 Mysql 等效项 Rank function in MySQL with Order By clause

编辑:非排名解决方案可以是这样的:

首先获取所有重复的邮件

<cfquery name="dupEmail" datasource="XXX">
SELECT EMAIL
FROM TEMP_SOL
GROUP BY EMAIL
HAVING COUNT(*) > 1
</cfquery>

遍历 dupEmail 和

1. 选择不需要删除的用户名。

2. 选择/删除不等于上述具有相同 emailid 的用户名。

   <cfloop query="dupEmail">
      <cfquery name="UserToRetain" datasource="XXX">
          SELECT USERNAME FROM TEMP_SOL
          WHERE EMAIL = '#dupEmail.Email#'
          ORDER BY START_DATE DESC NULLS LAST, ROWNUM DESC
          LIMIT 1 
     </cfquery>
   
     <cfquery name="DeleteUsers" datasource="XXX">
            SELECT * FROM TEMP_SOL
            WHERE USERNAME <> '#UserToRetain.USERNAME#' AND EMAIL='#dupEmail.Email#'
     </cfquery>
   
   </cfloop>
   

注意:以上代码尚未经过测试，查询可能无法在 mysql 上运行。