php - 如何查询在5分钟内添加了2次以上且具有相同电子邮件地址的重复数据？

我在数据库表中有我的示例数据，如下所示。

id   email            created_at
1    [email protected]       2016-01-01 01:01:30
2    [email protected]       2016-01-01 01:02:20
3    [email protected]       2016-01-01 01:03:30
4    [email protected]     2016-01-01 02:56:00
5    [email protected]       2016-01-01 01:04:30
6    [email protected]     2016-01-01 02:59:08
7    [email protected]     2016-01-01 03:01:24
8    [email protected]       2016-01-01 04:20:30
9    [email protected]       2016-01-01 04:23:29
10   [email protected]     2016-01-01 04:30:08
11   [email protected]       2016-01-01 04:25:29
12   [email protected]     2016-01-01 04:32:08
13   [email protected]       2016-01-01 05:16:30
14   [email protected]       2016-01-01 06:00:00
15   [email protected]     2017-07-17 15:03:00
16   [email protected]     2017-07-17 15:04:00
17   [email protected]     2017-07-17 15:08:01

我运行以下由草莓提供的SQL查询。

SELECT x.*
FROM my_table x
JOIN my_table y
ON y.id <> x.id
AND y.email = x.email
AND y.created_at
BETWEEN x.created_at - INTERVAL 5 MINUTE
AND x.created_at + INTERVAL 5 MINUTE
GROUP
BY x.id HAVING COUNT(*) >= 2

我有以下记录。

id   email            created_at
1    [email protected]       2016-01-01 01:01:30
2    [email protected]       2016-01-01 01:02:20
3    [email protected]       2016-01-01 01:03:30
5    [email protected]       2016-01-01 01:04:30
6    [email protected]     2016-01-01 02:59:08
8    [email protected]       2016-01-01 04:20:30
9    [email protected]       2016-01-01 04:23:29
11   [email protected]       2016-01-01 04:25:29
16   [email protected]     2017-07-17 15:04:00

我要检索的是以下记录，因为它们具有相同的电子邮件地址。这对我来说意味着同样的记录，它们在5分钟内被插入了2次以上。

id   email            created_at
1    [email protected]       2016-01-01 01:01:30
2    [email protected]       2016-01-01 01:02:20
3    [email protected]       2016-01-01 01:03:30
5    [email protected]       2016-01-01 01:04:30
8    [email protected]       2016-01-01 04:20:30
9    [email protected]       2016-01-01 04:23:29
11   [email protected]       2016-01-01 04:25:29

我如何编写一个sql查询来只获取在5分钟内添加了2次以上的记录？

最佳答案

可能有一个更简单的方法，但这似乎会返回您正在寻找的结果。。。

SELECT DISTINCT a.*
           FROM service_request a
           JOIN
              ( SELECT x.*
                     , MAX(y.created_at) AS range_end
                  FROM service_request x
                  JOIN service_request y
                    ON y.email = x.email
                   AND y.id >= x.id
                   AND y.created_at <= x.created_at + INTERVAL 5 MINUTE
                 GROUP
                    BY x.id HAVING COUNT(*) >= 3
              ) b
             ON b.email = a.email
            AND a.created_at BETWEEN b.created_at AND b.range_end;

关于php - 如何查询在5分钟内添加了2次以上且具有相同电子邮件地址的重复数据？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/35088108/