提问者:小点点

查找对象是否在不到一分钟内被记录两次


所以我的桌子是:

    ID          keeper cameraID  WHEN                REG
    "SO 02 ASP"  1     1    "2007-02-25 06:10:13"   "SO 02 ASP"
    "SO 02 ASP"  1     17   "2007-02-25 06:20:01"   "SO 02 ASP"
    "SO 02 ASP"  1     18   "2007-02-25 06:23:40"   "SO 02 ASP"
    "SO 02 ASP"  1     9    "2007-02-25 06:26:04"   "SO 02 ASP"
    "SO 02 CSP"  1     17   "2007-02-25 06:57:31"   "SO 02 CSP"
    "SO 02 CSP"  1     17   "2007-02-25 07:00:40"   "SO 02 CSP"
    "SO 02 CSP"  1     12   "2007-02-25 07:04:31"   "SO 02 CSP"
    "SO 02 GSP"  6     5    "2007-02-25 07:10:00"   "SO 02 GSP"
    "SO 02 GSP"  6     16   "2007-02-25 07:13:00"   "SO 02 GSP"
    "SO 02 TSP"  6     2    "2007-02-25 07:20:01"   "SO 02 TSP"
    "SO 02 TSP"  6     19   "2007-02-25 07:23:00"   "SO 02 TSP"
    "SO 02 TSP"  6     19   "2007-02-25 07:26:31"   "SO 02 TSP"
    "SO 02 TSP"  6     19   "2007-02-25 07:29:00"   "SO 02 TSP"
    "SO 02 CSP"  1     8    "2007-02-25 07:35:41"   "SO 02 CSP"
    "SO 02 CSP"  1     18   "2007-02-25 07:39:04"   "SO 02 CSP"
    "SO 02 CSP"  1     18   "2007-02-25 07:42:30"   "SO 02 CSP"
    "SO 02 CSP"  1     10   "2007-02-25 07:45:11"   "SO 02 CSP"
    "SO 02 CSP"  1     8    "2007-02-25 07:48:10"   "SO 02 CSP"
    "SO 02 CSP"  1     19   "2007-02-25 07:51:10"   "SO 02 CSP"
    "SO 02 CSP"  1     18   "2007-02-25 07:55:11"   "SO 02 CSP"
    "SO 02 CSP"  1     11   "2007-02-25 07:58:01"   "SO 02 CSP"
    "SO 02 SSP"  2     18   "2007-02-25 16:28:40"   "SO 02 SSP"
    "SO 02 SSP"  2     9    "2007-02-25 16:31:01"   "SO 02 SSP"
    "SO 02 RSP"  1     18   "2007-02-25 16:38:31"   "SO 02 RSP"
    "SO 02 RSP"  1     9    "2007-02-25 16:39:10"   "SO 02 RSP"
    "SO 02 HSP"  5     9    "2007-02-25 16:45:04"   "SO 02 HSP"
    "SO 02 HSP"  5     9    "2007-02-25 16:48:11"   "SO 02 HSP"
    "SO 02 HSP"  5     9    "2007-02-25 16:51:30"   "SO 02 HSP"
    "SO 02 ISP"  6     9    "2007-02-25 16:58:01"   "SO 02 ISP"
    "SO 02 ISP"  6     12   "2007-02-25 17:01:13"   "SO 02 ISP"
    "SO 02 JSP"  2     3    "2007-02-25 17:07:00"   "SO 02 JSP"
    "SO 02 JSP"  2     18   "2007-02-25 17:10:43"   "SO 02 JSP"
    "SO 02 JSP"  2     19   "2007-02-25 17:14:11"   "SO 02 JSP"
    "SO 02 JSP"  2     3    "2007-02-25 17:17:03"   "SO 02 JSP"
    "SO 02 MUP"        10   "2007-02-25 18:23:11"   "SO 02 MUP"
    "SO 02 NUP"        11   "2007-02-25 18:26:13"   "SO 02 NUP"
    "SO 02 OUP"        12   "2007-02-25 18:29:01"   "SO 02 OUP"
    "SO 02 PUP"        3    "2007-02-25 18:33:10"   "SO 02 PUP"
    "SO 02 PUP"        15   "2007-02-25 18:36:31"   "SO 02 PUP"
    "SO 02 PUP"        3    "2007-02-25 18:39:10"   "SO 02 PUP"
    "SO 02 TSP"  6     10   "2007-02-26 05:13:30"   "SO 02 TSP"
    "SO 02 DSP"  4     18   "2007-02-25 16:29:11"   "SO 02 DSP"
    "SO 02 DSP"  4     19   "2007-02-25 16:31:01"   "SO 02 DSP"
    "SO 02 DSP"  4     19   "2007-02-25 17:42:41"   "SO 02 DSP"
    "SO 02 DSP"  4     9    "2007-02-25 18:54:30"   "SO 02 DSP"
    "SO 02 ESP"  1     3    "2007-02-25 17:16:11"   "SO 02 ESP"
    "SO 02 ESP"  1     10   "2007-02-25 18:08:40"   "SO 02 ESP"
    "SO 02 FSP"  3     11   "2007-02-25 18:08:00"   "SO 02 FSP"
    "SO 02 GSP"  6     12   "2007-02-25 18:08:13"   "SO 02 GSP"  

我想知道任何一个ID(车辆)是否在不到一分钟的时间内被任何一个摄像头拍摄了两次(不一定是同一个摄像头)

%id和REG来自我使用的联接

例子。 一个答案应该是“SO 02 RSP”,它在不到一分钟内被记录了两次

"SO 02 RSP"  1     18   "2007-02-25 16:38:31"   "SO 02 RSP"
"SO 02 RSP"  1     9    "2007-02-25 16:39:10"   "SO 02 RSP"

共1个答案

匿名用户

使用lag():

select t.*
from (select t.*,
             lag(when) over (partition by id order by `when`) as prev_when
      from t
     ) t
where prev_when > `when` - interval 1 minute;

这将返回发生此情况的第二行(以及后续行)。

对于一个列来说是一个非常糟糕的名称时--我认为您并没有真正使用它。 它是一个SQL关键字(在case表达式中使用)。