仅当它们之间的差异超过15分钟时，才如何获取2条记录 [英] How to get 2 records only if difference between them is more then 15 min

问题描述

仅当这两个之间的时间为15分钟或更长时间时，我才需要能够使用sql查找第一条记录和第二条记录.

I need to be able to find first and the second record with sql only if time between those 2 is 15 or more minutes.

让我举个例子，以便您了解我的需求:

Let me put a little example so you can see what I need:

ID| Time | Serial |   
 1| 10:45|   13   |  
 2| 11:00|   12   |  
 3| 11:17|   12   |  
 4| 11:00|   13   |  
 7| 11:05|   13   |  
 8| 11:07|   12   |  

我需要得到这行:

ID| Time | Serial |

1| 10:45| 13 |
2| 11:00| 12 |
3| 11:17| 12 |
4| 11:00| 13 |
7| 11:05| 13 |
8| 11:07| 12 |

1| 10:45| 13 |
2| 11:00| 12 |
3| 11:17| 12 |
4| 11:00| 13 |
7| 11:05| 13 |
8| 11:07| 12 |

并输出如下:

Time 1 | Time 2 | Serial | Usage  
 10:45 | 11:00  | 13     | 15min
 11:00 | 11:17  | 12     | 17min

我只需要同时比较具有相同序列号的2条记录.当然可以浏览所有记录. 我在时间列中有这种类型的数据:2012-09-06 16:53:05.581

I only need to compare 2 records at the same time with the same serial number. And of course go trough all the records. I have this type of the data in time column:2012-09-06 16:53:05.581

推荐答案

也许符合以下情况:

SELECT
    time1.time,
    time2.time,
    time1.serial,
    (time2.time - time1.time) / 60 AS usage
FROM
    mytable time1,
    mytable time2
WHERE
    time1.id != time2.id
    AND time1.serial = time2.serial
    AND time2.time - time1.time > 60 * 15

我对MySQL中的时间处理不熟悉-我假设可以将它们相减，并且差异以秒为单位.如果需要显式转换，请在此处使用函数.

I am not familiar with time handling in MySQL - I am assuming that they can be subtracted, and that the difference is in seconds. Use a function here if it requires explicit conversion.

附录:应该可以很好地处理成对的记录条目.但是，如果三个之间有15分钟的间隔，则将产生1-2、2-3和1-3，因此您可能需要对其进行一些修改.

Addendum: this should handle pairs of record entries fine. However if there are three that each have 15 minutes between them, then this will produce 1-2, 2-3 and 1-3, so you may need to modify it a bit.

这篇关于仅当它们之间的差异超过15分钟时，才如何获取2条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！