如下表
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
写法一:
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)
用程序运算出来再代替进去。