202
社区成员
发帖
与我相关
我的任务
分享
create table t (name char(1), age int, cnt datetime);
insert into t values ('a',20,'2017-02-25 07:12:34');
insert into t values ('a',20,'2017-02-25 07:12:38');
insert into t values ('a',20,'2017-02-25 07:12:40');
insert into t values ('a',20,'2017-02-25 07:12:42');
insert into t values ('a',20,'2017-02-25 07:13:18');
insert into t values ('a',20,'2017-02-25 07:13:20');
insert into t values ('a',20,'2017-02-25 09:13:20');
insert into t values ('b',20,'2017-02-25 07:18:42');
insert into t values ('b',20,'2017-02-25 09:20:18');
insert into t values ('b',20,'2017-02-25 09:23:20');
mysql> select * from t;
+------+------+---------------------+
| name | age | cnt |
+------+------+---------------------+
| a | 20 | 2017-02-25 07:12:34 |
| a | 20 | 2017-02-25 07:12:38 |
| a | 20 | 2017-02-25 07:12:40 |
| a | 20 | 2017-02-25 07:12:42 |
| a | 20 | 2017-02-25 07:13:18 |
| a | 20 | 2017-02-25 07:13:20 |
| a | 20 | 2017-02-25 09:13:20 |
| b | 20 | 2017-02-25 07:18:42 |
| b | 20 | 2017-02-25 09:20:18 |
| b | 20 | 2017-02-25 09:23:20 |
+------+------+---------------------+
10 rows in set (0.00 sec)
mysql> select *
-> from t a
-> where not exists (
-> select 1
-> from t
-> where name=a.name
-> and cnt >= a.cnt-interval 2 hour
-> and cnt <a.cnt
-> and age !=a.age
-> )
-> and age=(select age
-> from t
-> where name=a.name
-> and cnt < a.cnt-interval 2 hour
-> order by cnt desc
-> limit 1
-> )
-> ;
+------+------+---------------------+
| name | age | cnt |
+------+------+---------------------+
| a | 20 | 2017-02-25 09:13:20 |
| b | 20 | 2017-02-25 09:20:18 |
| b | 20 | 2017-02-25 09:23:20 |
+------+------+---------------------+
3 rows in set (0.00 sec)
mysql>
select * from tb a where exists (select 1 from tb b where a.name=b.name and a.age=b.age and a.cnn>date_add(b.cnn,INTERVAL 2 hour )
SELECT DISTINCT a.* FROM test a JOIN test b ON a.name = b.name AND a.age = b.age WHERE TIMESTAMPDIFF(HOUR, a.time, b.time) >= 2
SELECT
DISTINCT
a.*
FROM test a
JOIN test b ON a.name = b.name
AND a.age = b.age
WHERE TIMESTAMPDIFF(HOUR, a.time, b.time) >= 2