同一身份证售票系统之间的时间

Question

我使用的是票务系统，需要写一个查询来检查代理是否迟到了。

这就是我到现在为止所需要的：

• 我有：ticket_id time_created
• 我需要选择所有相同的ticket_id，其中两者之间的time_created大于24小时。

例如：

ticket_id time_created 
1         2013-08-19 12:11
1         2013-08-17 12:07
1         2013-08-17 12:00
2         2013-08-19 12:11
2         2013-08-19 12:10

结果

ticket_id time_difference
1             48:04

我只需要检查相同ticket_id号码的票就行了。

编辑让我们假设也有和id列，我需要检查值在1,2，然后2,3 (在本例中- all ticket_id=1)和4,5 (ticket_id=2)之间。

Answer 1

此查询将检查最后一个日期与最低日期之间的差异，您希望此日期还是最后一个日期与第二个最后日期之间的差异。

SELECT ticket_id, 
DATEDIFF(MAX(TIME_CREATED),MIN(TIME_CREATED)) AS DiffDate 
FROM TABLE1 
    group by ticket_id

这个查询应该能找出两张最新的票的区别。

SELECT ticket_id, TIMEDIFF(MAX(A.TIME_CREATED),

             (SELECT MAX(TIME_CREATED) FROM TICKETS B 
              where B.TIME_CREATED <MAX(A.TIME_CREATED)))  AS DiffDate 
    FROM TICKETS A
        group by ticket_id

想想看，我现在认为这可能是

Difference between dates in two consecutive rows

你能试试那里的查询吗..。

选择ticketid，dense_rank(从ticketid)提取，选择ticketid，ticketdate滞后( ticketdate) OVER (按ticketid顺序划分)作为tdiff，选择dense_rank() OVER (按ticketid顺序进行划分)从Table1顺序选择ticketid(Ticketid)x，其中秩= 2；

和小提琴在

http://sqlfiddle.com/#!1/0e213/1

Answer 2

像这样的东西可能会起作用：

select ticket_id, min(a.time_created), max(b.time_created),
       TIMESTAMPDIFF(HOUR,min(a.time_created), max(b.time_created))
  from tbl AS a JOIN tbl AS b USING(ticket_id)
  where a.time_created < (b.time_created - INTERVAL 1 DAY)
  group by(a.ticket_id);

制作：

+------------+--------------------------------+--------------------------------+--------------------------------------------------------------+
| TICKET_ID  |      MIN(A.TIME_CREATED)       |      MAX(B.TIME_CREATED)       | TIMESTAMPDIFF(HOUR,MIN(A.TIME_CREATED), MAX(B.TIME_CREATED)) |
+------------+--------------------------------+--------------------------------+--------------------------------------------------------------+
|         1  | August, 17 2013 12:00:00+0000  | August, 19 2013 12:11:00+0000  |                                                           48 |
+------------+--------------------------------+--------------------------------+--------------------------------------------------------------+

请参阅http://sqlfiddle.com/#!2/4af76e/7

请注意，我在这里使用了TIMESTAMPDIFF，请求HOUR中的结果。由于不知道您的域规范，我这里不使用TIMEDIFF，因为TIME值的范围有限(≈34天)。

Answer 3

要获得差异，您需要等效于lag()函数，而MySQL不支持该函数。您可以通过关联子查询获得相同的效果：

select ticket_id, timediff(nextTimeCreated, time_created)
from (select t.*,
             (select time_created
              from t t2
              where t2.ticket_id = t.ticket_id and
                    t2.time_created > t.time_created
             ) as nextTimeCreated
      from t
     ) t
where nextTimeCreated > time_created + interval 1 day;