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

Question

数据表结构如下：
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

Answer 1

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')

Answer 2

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>

Answer 3

把测试记录也贴出来。
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

Answer 4

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

Answer 5

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>