有一表t,结构如下:
id group1 date_time obj_value
1 g1 2011-2-1 0:00:00 1
2 g2 2011-2-1 0:00:00 0
3 g3 2011-2-1 0:00:00 0
4 g2 2011-2-1 10:00:00 1
5 g2 2011-2-1 12:00:00 0
6 g1 2011-2-1 18:00:00 0
7 g1 2011-2-1 23:59:59 0
8 g2 2011-2-1 23:59:59 0
9 g3 2011-2-1 23:59:59 0 想按group1分组,计算每天在obj_value字段不同状态下(0、1)的累加时间长度,精确的分钟。
统计后结果为:
group1 date_time time_len obj_value
g1 2011-2-1 1080 1
g1 2011-2-1 360 0
g2 2011-2-1 120 1
g2 2011-2-1 1320 0
g3 2011-2-1 0 1
g3 2011-2-1 1440 0以上只是列出了1天的数据,其它天的和此类似,每天会有00:00:00和23:59:59时间点的obj_value字段状态,方便统计。
id group1 date_time obj_value
1 g1 2011-2-1 0:00:00 1
2 g2 2011-2-1 0:00:00 0
3 g3 2011-2-1 0:00:00 0
4 g2 2011-2-1 10:00:00 1
5 g2 2011-2-1 12:00:00 0
6 g1 2011-2-1 18:00:00 0
7 g1 2011-2-1 23:59:59 0
8 g2 2011-2-1 23:59:59 0
9 g3 2011-2-1 23:59:59 0 想按group1分组,计算每天在obj_value字段不同状态下(0、1)的累加时间长度,精确的分钟。
统计后结果为:
group1 date_time time_len obj_value
g1 2011-2-1 1080 1
g1 2011-2-1 360 0
g2 2011-2-1 120 1
g2 2011-2-1 1320 0
g3 2011-2-1 0 1
g3 2011-2-1 1440 0以上只是列出了1天的数据,其它天的和此类似,每天会有00:00:00和23:59:59时间点的obj_value字段状态,方便统计。
解决方案 »
- 求解,mysql表分区后查询缓存不生效了!
- 超市mysql 服务器与客户端问题
- mysql命令行非标准端口怎么连接?
- mysql 触发器中如何查询同库中的某表中的字段赋值给一个标量
- 求救,PGSQL不支持UPDATE TAB SET C=(子查询) 怎么办
- C++Builder6+MySQL+Windows2000的问题。。。。。。。
- (在线等!)cp.chinadns.com(新网)主页的MYSQL连接语句的SERVERNAME是什么?急EST!
- 请教mysql 中SELECT LAST_INSERT_ID() 获取的值的使用
- mysql_query函数返回值怎么是1呢
- 更新记录,唯一键冲突
- mysql新建用户疑惑
- 如何提取用|分割的字符串内容
+----+--------+---------------------+-----------+
| id | group1 | date_time | obj_value |
+----+--------+---------------------+-----------+
| 1 | g1 | 2011-02-01 00:00:00 | 1 |
| 2 | g2 | 2011-02-01 00:00:00 | 0 |
| 3 | g3 | 2011-02-01 00:00:00 | 0 |
| 4 | g2 | 2011-02-01 10:00:00 | 1 |
| 5 | g2 | 2011-02-01 12:00:00 | 0 |
| 6 | g1 | 2011-02-01 18:00:00 | 0 |
| 7 | g1 | 2011-02-01 23:59:59 | 0 |
| 8 | g2 | 2011-02-01 23:59:59 | 0 |
| 9 | g3 | 2011-02-01 23:59:59 | 0 |
+----+--------+---------------------+-----------+
9 rows in set (0.02 sec)mysql> Select group1,date(date_time) as date_time,obj_value,
-> CEILING(sum(UNIX_TIMESTAMP(next_date_time)-UNIX_TIMESTAMP(date_time))/60) as time_len
-> from(
-> select *,
-> (Select MIN(date_time) from t Where group1=a.group1 And date(date_time)=date(a.date_time) and date_time>a.date_time) as next_date_time
-> from t a
-> ) u
-> Group by group1,date(date_time),obj_value;
+--------+------------+-----------+----------+
| group1 | date_time | obj_value | time_len |
+--------+------------+-----------+----------+
| g1 | 2011-02-01 | 0 | 360 |
| g1 | 2011-02-01 | 1 | 1080 |
| g2 | 2011-02-01 | 0 | 1320 |
| g2 | 2011-02-01 | 1 | 120 |
| g3 | 2011-02-01 | 0 | 1440 |
+--------+------------+-----------+----------+
5 rows in set (0.02 sec)mysql>
where tt.group1=t.group1 and tt.date_time<>t.date_time1 and tt.date_time<t.date_time1
) asd
group by
group1,date(date_time),obj_value
http://topic.csdn.net/u/20110124/13/fee37554-06aa-42c0-9ca5-69e5e5f2d556.html