所以我的桌子是:
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"
使用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表达式中使用）。
