按分组取每日最大值、最小值和差值

[kyle315]

数据表结构如下：
id group1 obj date_time obj_value
1 一楼 房间1 2010-12-01 00:00:00 10
2 一楼 房间2 2010-12-01 00:00:00 15
3 一楼 房间3 2010-12-01 00:00:00 15
4 二楼 房间1 2010-12-01 00:00:00 20
5 二楼 房间2 2010-12-01 00:00:00 10
6 一楼 房间1 2010-12-01 23:00:00 20
7 一楼 房间2 2010-12-01 23:00:00 25
8 一楼 房间3 2010-12-01 23:00:00 20
9 二楼 房间1 2010-12-01 23:00:00 30
10 二楼 房间2 2010-12-01 23:00:00 20
想分别按"group1"和date_time(yyyy-MM-dd)分组后，取该天最大时间分组下，所有obj_value之和，与该天最小时间分组下，所有obj_value之和，最后取两者之差。例如，在上表中，最大时间分组是2010-12-01 23:00:00，最小时间分组是2010-12-01 00:00:00，按group1分组后，一楼最大时间分组之和为（20+25+20=65），一楼最小时间分组之和为（10+15+15=40），两者之差为(65-40=25).
实现后的样子如下:
gourp1 date_time max_value min_value cha_value
一楼 2010-12-01 65 40 25
二楼 2010-12-01 50 30 20

[WWWWA]

SELECT b.group1,DATE_FORMAT(b.date_time,'%Y-%m-%d'),SUM(IF(b.date_time=ma,b.obj_value,0)) AS MAX_VALUE,
SUM(IF(b.date_time=mi,b.obj_value,0)) AS Min_VALUE,
SUM(IF(b.date_time=ma,b.obj_value,0))-SUM(IF(b.date_time=mi,b.obj_value,0)) AS cha_value
FROM ttw b
INNER JOIN
(SELECT a.group1,DATE_FORMAT(a.date_time,'%Y-%m-%d') AS newtime ,

MIN(a.date_time) AS mi,MAX(a.date_time) AS ma FROM ttw a GROUP BY a.group1,DATE_FORMAT(a.date_time,'%Y-%m-%d')) a
ON a.group1=b.group1 AND a.newtime=DATE_FORMAT(b.date_time,'%Y-%m-%d')
GROUP BY b.group1,DATE_FORMAT(b.date_time,'%Y-%m-%d')

[zuoxingyu]

mysql> select group1,date_time,max(a.Num) as MAX_VALUE,min(a.Num) as MIN_VALUE,max(a.Num)-min(a.Num) as CHA_VALUE
    -> from(
    -> select group1,date_time,sum(obj_value) as Num from tbl group by group1,date_time ) a
    -> group by group1;
+--------+---------------------+-----------+-----------+-----------+
| group1 | date_time           | MAX_VALUE | MIN_VALUE | CHA_VALUE |
+--------+---------------------+-----------+-----------+-----------+
| 一楼     | 2010-12-01 00:00:00 |        65 |        40 |        25 |
| 二楼      | 2010-12-01 00:00:00 |        50 |        30 |        20 |
+--------+---------------------+-----------+-----------+-----------+
2 rows in set (0.00 sec)

mysql>

[zuoxingyu]

把测试记录也贴出来。

create table tbl(
id int, group1 varchar(30), obj varchar(30), date_time datetime, obj_value int)
insert into tbl values
(1 ,'一楼','房间1',' 2010-12-01 00:00:00', 10),  
(2 ,'一楼','房间2',' 2010-12-01 00:00:00', 15),
(3 ,'一楼','房间3',' 2010-12-01 00:00:00',15),
(4 ,'二楼','房间1',' 2010-12-01 00:00:00', 20),
(5 ,'二楼','房间2',' 2010-12-01 00:00:00', 10),
(6 ,'一楼','房间1',' 2010-12-01 23:00:00', 20  ),
(7 ,'一楼','房间2',' 2010-12-01 23:00:00', 25),
(8 ,'一楼','房间3',' 2010-12-01 23:00:00', 20),
(9 ,'二楼','房间1',' 2010-12-01 23:00:00', 30),
(10,'二楼','房间2',' 2010-12-01 23:00:00', 20)

select group1,date_time,max(a.Num) as MAX_VALUE,min(a.Num) as MIN_VALUE,max(a.Num)-min(a.Num) as CHA_VALUE
from( 
select group1,date_time,sum(obj_value) as Num from tbl group by group1,date_time ) a
group by group1

[ACMAIN_CHM]

参考下贴中的多种方法

http://topic.csdn.net/u/20091231/16/2f268740-391e-40f2-a15e-f243b2c925ab.html
[征集]分组取最大N条记录方法征集，及散分....

[ACMAIN_CHM]

mysql> select * from tbl;
+------+--------+-------+---------------------+-----------+
| id   | group1 | obj   | date_time           | obj_value |
+------+--------+-------+---------------------+-----------+
|    1 | 一楼   | 房间1 | 2010-12-01 00:00:00 |        10 |
|    2 | 一楼   | 房间2 | 2010-12-01 00:00:00 |        15 |
|    3 | 一楼   | 房间3 | 2010-12-01 00:00:00 |        15 |
|    4 | 二楼   | 房间1 | 2010-12-01 00:00:00 |        20 |
|    5 | 二楼   | 房间2 | 2010-12-01 00:00:00 |        10 |
|    6 | 一楼   | 房间1 | 2010-12-01 23:00:00 |        20 |
|    7 | 一楼   | 房间2 | 2010-12-01 23:00:00 |        25 |
|    8 | 一楼   | 房间3 | 2010-12-01 23:00:00 |        20 |
|    9 | 二楼   | 房间1 | 2010-12-01 23:00:00 |        30 |
|   10 | 二楼   | 房间2 | 2010-12-01 23:00:00 |        20 |
+------+--------+-------+---------------------+-----------+
10 rows in set (0.00 sec)

mysql> select group1,
    ->  Sum(if(date_time=(select max(date_time) from tbl where group1=a.group1),obj_value,0)) As max_value,
    ->  Sum(if(date_time=(select min(date_time) from tbl where group1=a.group1),obj_value,0)) as min_value,
    ->  Sum(if(date_time=(select max(date_time) from tbl where group1=a.group1),obj_value,0))
    ->  -sum(if(date_time=(select min(date_time) from tbl where group1=a.group1),obj_value,0))  As cha_value
    -> from tbl a
    -> group by group1;
+--------+-----------+-----------+-----------+
| group1 | max_value | min_value | cha_value |
+--------+-----------+-----------+-----------+
| 一楼   |        65 |        40 |        25 |
| 二楼   |        50 |        30 |        20 |
+--------+-----------+-----------+-----------+
2 rows in set (0.00 sec)

mysql>

[kyle315]

来晚了，一直没来及结贴，多谢大家。