在MySQL中查找在特定日期之间的重复行

Question

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

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 ...

感谢您的帮助。

- Don Djoe

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月5日，ID为8的门票
第二张日期为2015年12月3日，ID为11的门票

- cn007b

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

希望这可以帮助到您。

- mwpeng 彭明伟

---

负值也将小于二！ - Strawberry