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

问题描述

如下表

uid order moneytime1 100 10 2016-08-08 12:00:001 101 6 2016-08-08 12:00:582 102 8 2016-08-08 12:02:002 103 10 2016-08-08 12:02:332 104 15 2016-08-08 12:03:003 105 10 2016-08-08 12:03:011 106 10 2016-08-08 12:05:00

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

uid order moneytime1 100 10 2016-08-08 12:00:001 101 6 2016-08-08 12:00:582 102 8 2016-08-08 12:02:002 103 10 2016-08-08 12:02:332 104 15 2016-08-08 12:03:00

问题解答

写法一：

SELECT * FROM table AS aWHERE 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 tableWHERE 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)用程序运算出来再代替进去。