mysql - 如何以uid为基准查询所有一分钟之内有两条数据以上的数据

Question

如下表

uid    order    money        time
1       100      10    2016-08-08 12:00:00
1       101       6    2016-08-08 12:00:58
2       102       8    2016-08-08 12:02:00
2       103      10    2016-08-08 12:02:33
2       104      15    2016-08-08 12:03:00
3       105      10    2016-08-08 12:03:01
1       106      10    2016-08-08 12:05:00

根据题意需要找到的数据：

uid    order    money        time
1       100      10    2016-08-08 12:00:00
1       101       6    2016-08-08 12:00:58
2       102       8    2016-08-08 12:02:00
2       103      10    2016-08-08 12:02:33
2       104      15    2016-08-08 12:03:00

Answer 1

写法一：

SELECT * FROM table AS a
WHERE EXISTS (
    SELECT 1 FROM table AS b
    WHERE a.uid = b.uid
    AND b.time >= date_sub(now(), INTERVAL 1 minute)
    GROUP BY b.uid
    HAVING count(1) > 1
);

写法二：

SELECT * FROM table
WHERE uid IN (
    SELECT uid FROM table
    WHERE time >= date_sub(now(), INTERVAL 1 minute)
    GROUP BY uid
    HAVING count(1) > 1
);

建议将date_sub(now(), INTERVAL 1 minute)用程序运算出来再代替进去。