MYSQL - 查找并显示日期差异标准内的所有重复项

Question

下面的查询选择具有同一父亲的行的所有行，该行自先前注册以来已注册 335 天或更短的时间。有没有办法编辑此查询，使其不会消除输出中的重复行？我需要查看该父亲在 335 天内的所有注册实例。

SELECT * FROM ymca_reg a later
WHERE NOT EXISTS (
    SELECT 1 FROM ymca_reg a earlier 
    WHERE 
        earlier.Father_First_Name = later.Father_First_Name
        AND earlier.Father_Last_Name = later.Father_Last_Name
        AND (later.Date - earlier.Date < 335) AND (later.Date > earlier.Date)

我当前的查询是:

SELECT ymca_reg.* FROM ymca_reg WHERE (((ymca_reg.Year) In (SELECT Year FROM ymca_reg As Tmp 
    GROUP BY Year, Father_Last_Name, Father_First_Name 
    HAVING Count(*)>1  
        And Father_Last_Name = ymca_reg.Father_Last_Name 
        And Father_First_Name = ymca_reg.Father_First_Name))) 
    ORDER BY ymca_reg.Year, ymca_reg.Father_Last_Name, ymca_reg.Father_First_Name 

此查询确实返回所有重复项以供正确查看，但速度非常慢，因为它不使用联接，并且一旦我添加日期条件，它就只返回后面的行。谢谢。

Answer 1

我想你想要这样的东西:

SELECT *
FROM ymca_reg later
WHERE EXISTS (SELECT 1
              FROM ymca_reg earlier 
              WHERE earlier.Father_First_Name = later.Father_First_Name AND
                    earlier.Father_Last_Name = later.Father_Last_Name AND
                    abs(later.Date - earlier.Date) < 335 and
                    later.Date <> earlier.Date
             );

这应该返回具有此类重复项的所有记录。请注意，“稍后”和“较早”不再是真正恰当的描述，但我保留了名称，以便您可以看到与查询的相似性。