表结构
id create_time end_time size
1 2009-09-14 14:12:02 2009-09-14 14:34:03 23
1.求时间小于5分钟的个数及平均大小。
2.求时间小于1天大于1小时的个数及平均大小。
3.求2009/3/1至2009/5/23平均每天新增数量
4.求开始时间与结束时间最长的记录
5.求2009/3/1至2009/5/23平均每天大小
Ttable 为表名
1.select count( time_to_sec(end_time)-time_to_sec(create_time)) as amount , avg(size) as average from Ttable where (time_to_sec(end_time)-time_to_sec(create_time) )<5*60
2.select count( time_to_sec(end_time)-time_to_sec(create_time)) as amount , avg(size) as average from Ttable where (time_to_sec(end_time)-time_to_sec(create_time) )>60*60 and (time_to_sec(end_time)-time_to_sec(create_time) )<24*60*60
3.
4.select *from Ttable where ( time_to_sec(end_time)-time_to_sec(create_time))=( select max( time_to_sec(end_time)-time_to_sec(create_time))as poor from Ttable )
5.select sum(size) /TIMESTAMPDIFF(day,'2009-03-01','2009-05-23') from Ttable where create_time>'2009-03-01 00:00:00' and end_time<'2009-05-24 00:00:00'
这么麻烦，就三分。。。