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

bravility
  • 239

如下表

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
回复
阅读 2.3k
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)用程序运算出来再代替进去。

撰写回答
你尚未登录,登录后可以
  • 和开发者交流问题的细节
  • 关注并接收问题和回答的更新提醒
  • 参与内容的编辑和改进,让解决方法与时俱进
宣传栏