我有一个简单的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 ...
感谢您的帮助。