56,675
社区成员
发帖
与我相关
我的任务
分享
mysql> select f_id,date(f_time),sum(if(time(k)='23:59:59',1,0)+UNIX_TIMESTAMP(k)-UNIX_TIMESTAMP(f_time))/60 as m
-> from (
-> select f_id,f_time,
-> (select Min(f_time) From t Where f_id=a.f_id And f_time>a.f_time And DATE(f_time)=date(t.f_time)) as k
-> from t a
-> where f_value=1
-> ) b
-> where k is not null
-> group by f_id,date(f_time);
+------+--------------+-----------+
| f_id | date(f_time) | m |
+------+--------------+-----------+
| A | 2011-03-10 | 1140.0000 |
| B | 2011-03-10 | 960.0000 |
| C | 2011-03-10 | 600.0000 |
| E | 2011-03-10 | 1440.0000 |
+------+--------------+-----------+
4 rows in set (0.06 sec)
mysql>
mysql> select f_id,date(f_time),sum(UNIX_TIMESTAMP(k)-UNIX_TIMESTAMP(f_time))/60 as m
-> from (
-> select f_id,f_time,
-> (select Min(f_time) From t Where f_id=a.f_id And f_time>a.f_time And DATE(f_time)=date(t.f_time)) as k
-> from t a
-> where f_value=1
-> ) b
-> where k is not null
-> group by f_id,date(f_time);
+------+--------------+-----------+
| f_id | date(f_time) | m |
+------+--------------+-----------+
| A | 2011-03-10 | 1139.9833 |
| B | 2011-03-10 | 959.9833 |
| C | 2011-03-10 | 600.0000 |
| E | 2011-03-10 | 1439.9833 |
+------+--------------+-----------+
4 rows in set (0.08 sec)
mysql>
select f_id,date(f_time),sum(UNIX_TIMESTAMP(k)-UNIX_TIMESTAMP(f_time))/60 as m
from (
select f_id,f_time,
(select Min(f_time) From t Where f_id=a.f_id And f_time>a.f_time And DATE(f_time)=date(t.f_time)) as k
from t a
where f_value=1
) b
where k is not null
group by f_id,date(f_time)