MYSQL - 在日期差异critria中查找并显示所有重复项 [英] MYSQL - find and show all duplicates within date difference critria

问题描述

下面的查询选择所有具有相同父亲的行注册时间早于335天或更短的行。有没有办法编辑这个查询，使它不能消除输出中的重复行？我需要在335天内看到所有父亲的登记实例。

This query below selects all rows that have a row with the same father registering 335 days or less since earlier registration. Is there a way to edit this query so that it does not eliminate the duplicate row in the output? I need to see all instances of the registration for that father within 335 days of each other.

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 

此查询确实返回正确的所有重复项，但它非常慢，因为它不使用加入，只要我添加日期标准，它只返回后一行。谢谢。

This query does return all the duplicates for review correctly, but it's terribly slow because it doesn't use a join and as soon as I add the date criteria it only returns the later row. Thanks.

推荐答案

我想你想要这样的东西：

I think you want something like this:

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
             );

这应该返回所有具有这样重复的记录。请注意，稍后和较早期不再是真正适合的描述，但我留下了名称，以便您可以看到与您的查询相似。

This should return all records that have such duplicates. Note that "later" and "earlier" are no longer really apt descriptions, but I left the names so you can see the similarity to your query.

这篇关于MYSQL - 在日期差异critria中查找并显示所有重复项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！