MySQL在某些日期之间找到重复的行

Question

我有一个名为 tickets 的简单 MySQL 表

id  | operator_id | product_id |   created_timestamp
 1  |    STAFF001 |     acc001 | 2015-01-01 22:00:00
 2  |    STAFF003 |     acc004 | 2015-11-01 22:00:00
 3  |    STAFF002 |     acc002 | 2015-01-01 22:00:00
 4  |    STAFF002 |     acc003 | 2015-11-01 22:00:00
 5  |    STAFF001 |     acc005 | 2015-01-01 22:00:00
 6  |    STAFF005 |     acc002 | 2015-11-01 22:00:00
 7  |    STAFF004 |     acc001 | 2015-01-01 22:00:00
 8  |    STAFF001 |     acc001 | 2015-12-05 22:00:00
 9  |    STAFF003 |     acc001 | 2015-01-01 22:00:00
10  |    STAFF002 |     acc007 | 2015-11-01 22:00:00
11  |    STAFF001 |     acc001 | 2015-12-03 22:00:00
12  |    STAFF001 |     acc001 | 2015-12-01 22:00:00
13  |    STAFF005 |     acc001 | 2015-01-01 22:00:00
14  |    STAFF006 |     acc001 | 2015-12-01 22:00:00

我应该如何编写 SQL 才能检索在彼此之间的 2 天之间创建的票证，这些票证也具有相同的 operator_id 和 product_id？

所以预期的结果是(忽略原因栏，它只是为了帮助解释)

id  | operator_id | product_id |   created_timestamp | reason
 8  |    STAFF001 |     acc001 | 2015-12-05 22:00:00 | because it is within 2 days from id 11
11  |    STAFF001 |     acc001 | 2015-12-03 22:00:00 | because it is within 2 days from id 8 or 12
12  |    STAFF001 |     acc001 | 2015-12-01 22:00:00 | because it is within 2 days from id 11

这些不包括在内

id  | operator_id | product_id |   created_timestamp | reason
 1  |    STAFF001 |     acc001 | 2015-01-01 22:00:00 | the date diff is too big
14  |    STAFF006 |     acc001 | 2015-12-01 22:00:00 | the date diff is ok for the same product_id (e.g. compared to id 11 and 8 )but the operator_id is different
many other ...

感谢您的帮助

Answer 1

select *
from tickets t1
join tickets t2 on
    t1.id <> t2.id
    and t1.operator_id = t2.operator_id
    and t1.product_id = t2.product_id
    and ABS(DATEDIFF(t1.created_timestamp, t2.created_timestamp)) <= 2
;

我添加了 ABS() 函数，因为您有以下记录:
第一张日期为 2015-12-05 且 ID 为 8 的票
日期为 2015-12-03 且 ID 为 11 的第二张票