表是这样的
id date datetime value
1 2010-01-15 2010-01-15_15:18:34 abcdef
2 2010-01-15 2010-01-15_15:19:32 aaaaa
3 2010-01-15 2010-01-15_15:20:33 abcdef
4 2010-01-15 2010-01-15_15:22:34 aaaaa
5 2010-01-15 2010-01-15_15:25:34 abcdef
怎样通过sql语句 对5分钟以内 value相同的数据去重。
比如:
id为1的行 时间是2010-01-15_15:18:34 value为abcdef
id为3的行 值也为abcdef 和第一行值相同 ,时间是2010-01-15_15:18:34 和第一行相比在5分钟以内,所以不显示此条数据
id为5的行 值也为abcdef ,但是时间与第一行相比大于5分钟,所以显示最后要查询出来的数据就是去重以后的,想了半天没搞定,请大家帮忙,万分感谢
id date datetime value
1 2010-01-15 2010-01-15_15:18:34 abcdef
2 2010-01-15 2010-01-15_15:19:32 aaaaa
3 2010-01-15 2010-01-15_15:20:33 abcdef
4 2010-01-15 2010-01-15_15:22:34 aaaaa
5 2010-01-15 2010-01-15_15:25:34 abcdef
怎样通过sql语句 对5分钟以内 value相同的数据去重。
比如:
id为1的行 时间是2010-01-15_15:18:34 value为abcdef
id为3的行 值也为abcdef 和第一行值相同 ,时间是2010-01-15_15:18:34 和第一行相比在5分钟以内,所以不显示此条数据
id为5的行 值也为abcdef ,但是时间与第一行相比大于5分钟,所以显示最后要查询出来的数据就是去重以后的,想了半天没搞定,请大家帮忙,万分感谢
SELECT *,TIMEDIFF(a.datetime,b.ma) FROM qt4 a LEFT JOIN
(SELECT VALUE,MIN(DATETIME) AS ma FROM qt4 GROUP BY VALUE) b
ON a.value=b.value AND TIMEDIFF(a.datetime,b.ma)<='00:05:00' AND TIMEDIFF(a.datetime,b.ma)<>'00:00:00'
WHERE b.VALUE IS null
+----+------------+---------------------+--------+
| id | date | datetime | value |
+----+------------+---------------------+--------+
| 1 | 2010-01-15 | 2010-01-15 15:18:34 | abcdef |
| 2 | 2010-01-15 | 2010-01-15 15:19:32 | aaaaa |
| 3 | 2010-01-15 | 2010-01-15 15:20:33 | abcdef |
| 4 | 2010-01-15 | 2010-01-15 15:22:34 | aaaaa |
| 5 | 2010-01-15 | 2010-01-15 15:25:34 | abcdef |
+----+------------+---------------------+--------+
5 rows in set (0.00 sec)mysql> set @t='1900-01-01';
Query OK, 0 rows affected (0.00 sec)mysql> set @v='';
Query OK, 0 rows affected (0.02 sec)mysql> select id,`date`,`datetime`,`value`
-> from (
-> select *,
-> @t:=if(@v!=`value` or TIMEDIFF(`datetime`,@t)>'00:05:00' ,`datetime`,@t),
-> @t=`datetime` as k,
-> @v:=`value`
-> from t_chengkairen2
-> order by `value`,`datetime`
-> ) t
-> where k=1
-> order by `datetime`;
+----+------------+---------------------+--------+
| id | date | datetime | value |
+----+------------+---------------------+--------+
| 1 | 2010-01-15 | 2010-01-15 15:18:34 | abcdef |
| 2 | 2010-01-15 | 2010-01-15 15:19:32 | aaaaa |
| 5 | 2010-01-15 | 2010-01-15 15:25:34 | abcdef |
+----+------------+---------------------+--------+
3 rows in set (0.00 sec)mysql>