统计不同分组下，每天的时间差值。

[kyle315]

狼兄已帮我解决了累加时长的问题，在此表示感谢，现在还要统计每天的时间差，说明如下：

有一表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  
10  g1     2011-2-2 0:00:00  0
11  g2     2011-2-2 0:00:00  0
12  g3     2011-2-2 0:00:00  0
13  g1     2011-2-2 8:00:00  1
14  g3     2011-2-2 14:00:00 1
15  g1     2011-2-2 23:59:59 1
16  g2     2011-2-2 23:59:59 0
17  g3     2011-2-2 23:59:59 1   
18  g1     2011-2-4 0:00:00  1
19  g2     2011-2-4 0:00:00  0
20  g3     2011-2-4 0:00:00  1
21  g2     2011-2-4 10:00:00 1
22  g1     2011-2-4 15:00:00 0
23  g1     2011-2-4 23:59:59 0
24  g2     2011-2-4 23:59:59 1
25  g3     2011-2-4 23:59:59 1 

想按group1分组，计算每天在obj_value字段不同状态下(0、1)，每日时间差（当天的累加时长-上一天的累加时长），
，如果无上一天的数据，当天时间差可标记为0或者null ,精确的分钟。
统计后结果为：
group1 date_time time_len cha_value obj_value
g1     2011-2-1  1080     null      1
g1     2011-2-1  360      null      0
g2     2011-2-1  120      null      1  
g2     2011-2-1  1320     null      0
g3     2011-2-1  0        null      1
g3     2011-2-1  1440     null      0
g1     2011-2-2  960      -120      1
g1     2011-2-2  480      120       0
g2     2011-2-2  0        -120      1  
g2     2011-2-2  1440     120       0
g3     2011-2-2  600      600       1
g3     2011-2-2  840      -600      0
g1     2011-2-4  900      null      1
g1     2011-2-4  540      null      0
g2     2011-2-4  840      null      1  
g2     2011-2-4  600      null      0
g3     2011-2-4  1440     null      1
g3     2011-2-4  0        null      0

说明，每天会有00:00:00和23:59:59时间点的obj_value字段状态，方便统计；
上述数据中因2011-2-1和2011-2-4的上一天没有数据，所以cha_value字段用null代替了

[小小小小周]

楼主把表结构和insert 语句发出来吧.不然我黏贴复制要老半天..

[小小小小周]

mysql> select * from t;
+------+--------+---------------------+-----------+
| 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 |
|   10 | g1     | 2011-02-02 00:00:00 |         0 |
|   11 | g2     | 2011-02-02 00:00:00 |         0 |
|   12 | g3     | 2011-02-02 00:00:00 |         0 |
|   13 | g1     | 2011-02-02 08:00:00 |         1 |
|   14 | g3     | 2011-02-02 14:00:00 |         1 |
|   15 | g1     | 2011-02-02 23:59:59 |         1 |
|   16 | g2     | 2011-02-02 23:59:59 |         0 |
|   17 | g3     | 2011-02-02 23:59:59 |         1 |
|   18 | g1     | 2011-02-04 00:00:00 |         1 |
|   19 | g2     | 2011-02-04 00:00:00 |         0 |
|   20 | g3     | 2011-02-04 00:00:00 |         1 |
|   21 | g2     | 2011-02-04 10:00:00 |         1 |
|   22 | g1     | 2011-02-04 15:00:00 |         0 |
|   23 | g1     | 2011-02-04 23:59:59 |         0 |
|   24 | g2     | 2011-02-04 23:59:59 |         1 |
|   25 | g3     | 2011-02-04 23:59:59 |         1 |
+------+--------+---------------------+-----------+
25 rows in set (0.00 sec)

mysql> select abcc.group1,abcc.date_time,abcc.time_len,(abcc.time_len-abdd.time_
len) as cha_value ,abcc.obj_value from
    -> (
    -> 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)=d
ate(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
    -> ) abcc left join
    -> (
    -> 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)=d
ate(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
    -> ) abdd
    -> on  abcc.group1=abdd.group1 and abcc.obj_value=abdd.obj_value and  datedi
ff(abdd.date_time,abcc.date_time)=-1
    -> group by abcc.group1,date(abcc.date_time),abcc.obj_value
    -> ;
+--------+------------+----------+-----------+-----------+
| group1 | date_time  | time_len | cha_value | obj_value |
+--------+------------+----------+-----------+-----------+
| g1     | 2011-02-01 |      360 |      NULL |         0 |
| g1     | 2011-02-01 |     1080 |      NULL |         1 |
| g1     | 2011-02-02 |      480 |       120 |         0 |
| g1     | 2011-02-02 |      960 |      -120 |         1 |
| g1     | 2011-02-04 |      540 |      NULL |         0 |
| g1     | 2011-02-04 |      900 |      NULL |         1 |
| g2     | 2011-02-01 |     1320 |      NULL |         0 |
| g2     | 2011-02-01 |      120 |      NULL |         1 |
| g2     | 2011-02-02 |     1440 |       120 |         0 |
| g2     | 2011-02-04 |      600 |      NULL |         0 |
| g2     | 2011-02-04 |      840 |      NULL |         1 |
| g3     | 2011-02-01 |     1440 |      NULL |         0 |
| g3     | 2011-02-02 |      840 |      -600 |         0 |
| g3     | 2011-02-02 |      600 |      NULL |         1 |
| g3     | 2011-02-04 |     1440 |      NULL |         1 |
+--------+------------+----------+-----------+-----------+
15 rows in set (0.00 sec)

用狼头大哥的基础表;

[ACMAIN_CHM]

用楼上的方法吧。

建议下次提问时，提供你的 create table ,insert into 语句。

[mysqldbd]

-- 基础数据表sql

drop table if exists test.tb;
create table test.tb (id int, group1 varchar(20), date_time timestamp, obj_value int);
insert into test.tb
select 1,   'g1',     '2011-2-1 0:00:00',  '1' union all
select 1,   'g2',     '2011-2-1 0:00:00',  '  0' union all
select 3,   'g3',     '2011-2-1 0:00:00',  '  0' union all
select 4,   'g2',     '2011-2-1 10:00:00',  ' 1' union all
select 5,   'g2',    '2011-2-1 12:00:00',  '0' union all
select 6,   '   g1',     '2011-2-1 18:00:00',  '0' union all
select 7,   '   g1',     '2011-2-1 23:59:59',  '0' union all
select 8,   '   g2',     '2011-2-1 23:59:59',  '0' union all
select 9,   '   g3',     '2011-2-1 23:59:59',  '0  ' union all
select 10,   '  g1',     '2011-2-2 0:00:00',  '0' union all
select 11,   '  g2',     '2011-2-2 0:00:00',  '0' union all
select 12,   '  g3',     '2011-2-2 0:00:00',  '0' union all
select 13,   '  g1',     '2011-2-2 8:00:00',  '1' union all
select 14,   '  g3',     '2011-2-2 14:00:00',  '1' union all
select 15,   '  g1',     '2011-2-2 23:59:59',  '1' union all
select 16,   '  g2',     '2011-2-2 23:59:59',  '0' union all
select 17,   '  g3',     '2011-2-2 23:59:59',  '1' union all   
select 18,   '  g1',     '2011-2-4 0:00:00',  '1' union all
select 19,   '  g2',     '2011-2-4 0:00:00',  '0' union all
select 20,   '  g3',     '2011-2-4 0:00:00',  '1' union all
select 21,   '  g2',     '2011-2-4 10:00:00',  '1' union all
select 22,   '  g1',     '2011-2-4 15:00:00',  '0' union all
select 23,   '  g1',     '2011-2-4 23:59:59',  '0' union all
select 24 ,   ' g2',     '2011-2-4 23:59:59',  '1' union all
select 25,   '  g3',     '2011-2-4 23:59:59',  '1';
update test.tb set group1=trim(group1);