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)用程序運算出來再代替進去。