MySQL在特定天之间查找重复行

Question

我有一个简单的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，以便检索在彼此之间创建的票，其中也有相同的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的车票

Answer 2

select * from 
       tickets t1 left join tickets t2
       on t1.operator_id = t2.operator_id and t1.product_id = t2.product_id
       where t1.id != t2.id
        and datediff(r1.created, r2.created) <= 2

希望这能帮上忙。