用脚本处理
mysql -uroot -p -e "select id,时间 from tablename" | awk -F '[ |:]+' '{a[$3]=$0;b[$3]++}END{for(i in b){if(b[i]<33){print a[i]}}}'
2 2014-12-12 10:40:05
4 2014-12-12 11:41:07
6 2014-12-12 12:42:16结果是打印出,有缺失时间点的最后一个记录
mysql -uroot -p -e "select id,时间 from tablename" | awk -F '[ |:]+' '{a[$3]=$0;b[$3]++}END{for(i in b){if(b[i]<33){print a[i]}}}'
2 2014-12-12 10:40:05
4 2014-12-12 11:41:07
6 2014-12-12 12:42:16结果是打印出,有缺失时间点的最后一个记录
是小于3,多打了个3
mysql> select * from t_tflb;
+----+---------------------+
| id | ttt |
+----+---------------------+
| 1 | 2014-12-12 10:20:01 |
| 2 | 2014-12-12 10:40:05 |
| 3 | 2014-12-12 11:00:07 |
| 4 | 2014-12-12 11:41:07 |
| 5 | 2014-12-12 12:22:09 |
| 6 | 2014-12-12 12:42:16 |
+----+---------------------+
6 rows in set (0.00 sec)mysql> select count(*) from t_tflb t
-> where (select min(ttt) from t_tflb where ttt>t.ttt)>ttt+interval 30 minute;
+----------+
| count(*) |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec)mysql> select * from t_tflb t
-> where (select min(ttt) from t_tflb where ttt>t.ttt)>ttt+interval 30 minute
-> or (select max(ttt) from t_tflb where ttt<t.ttt)<ttt-interval 30 minute;
+----+---------------------+
| id | ttt |
+----+---------------------+
| 3 | 2014-12-12 11:00:07 |
| 4 | 2014-12-12 11:41:07 |
| 5 | 2014-12-12 12:22:09 |
+----+---------------------+
3 rows in set (0.00 sec)mysql>