Code Bye

mysql计算两条数据差值,求大神解答


想实现按照roomid与时间降序排序后,求上下两条字段water的差值,如果是最后一条就差值就减去0
id   water     roomId     差值
4    210         1             11
2    150         1             10
1    100         1             100
3    50           2              50 

20分
1.创建3张表:
CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `warter` int(11) DEFAULT NULL,
  `roomid` int(11) DEFAULT NULL,
  `createtime` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;
create table t1_01 like t1;
create table t1_02 like t1;

2.初始化数据(类似楼主的数据):
insert into t1(warter,roomid,createtime) values(100,1,””2015-02-20 14:31:57″”),(150,1,””2015-03-20 14:33:01″”),(50,2,””2015-04-20 14:34:10″”),(210,1,””2015-04-20 14:56:50″”);
mysql> select * from t1 order by roomid,createtime desc;
+—-+——–+——–+———————+
| id | warter | roomid | createtime          |
+—-+——–+——–+———————+
|  4 |    210 |      1 | 2015-04-20 14:56:50 |
|  2 |    150 |      1 | 2015-03-20 14:33:01 |
|  1 |    100 |      1 | 2015-02-20 14:31:57 |
|  3 |     50 |      2 | 2015-04-20 14:34:10 |
+—-+——–+——–+———————+
4 rows in set (0.00 sec)

mysql> 

3.生成t1_01表的数据:
insert into t1_01(warter,roomid,createtime) select warter,roomid,createtime from t1 order by roomid,createtime desc;

4.生成t1_02表的数据:
insert into t1_02 select id+1 as id, warter,roomid,createtime from t1_01;

5.生成最终结果:
select t2.*,(t2.warter-t1.warter)  as warter_diff from t1_01 t1  
  inner join t1_02 t2  
  on t1.roomid=t2.roomid and t1.id=t2.id
union all
select t1.*, t1.warter as warter_diff from t1_01  t1 
  inner join (select min(createtime) cr,roomid from t1 group by roomid) t2 
  on t1.roomid=t2.roomid and t1.createtime=t2.cr;
+—-+——–+——–+———————+————-+
| id | warter | roomid | createtime          | warter_diff |
+—-+——–+——–+———————+————-+
|  2 |    210 |      1 | 2015-04-20 14:56:50 |          60 |
|  3 |    150 |      1 | 2015-03-20 14:33:01 |          50 |
|  3 |    100 |      1 | 2015-02-20 14:31:57 |         100 |
|  4 |     50 |      2 | 2015-04-20 14:34:10 |          50 |
+—-+——–+——–+———————+————-+
4 rows in set (0.00 sec)

mysql> 

注:t1_01和t1_02表在多并发情况下,可以采用临时表。

select water-(select water from table1 where roomid=a.roomid and 时间<a.时间 order by 时间 desc limit 1)
from table1 a
20分
2楼的结果是这样的:
+————-+
| warter_diff |
+————-+
|        NULL |
|          50 |
|        NULL |
|          60 |
+————-+
4 rows in set (0.00 sec)

mysql> 

我再改写后,就可以了,结果如下:
select * from 
(select a.roomid,a.createtime, a.warter, a.warter-(select warter from t1 where roomid=a.roomid and createtime<a.createtime order by createtime desc limit 1) as warter_diff
from t1 a) aa  where  aa.warter_diff is not null
union all
select roomid,min(createtime),min(warter) warter,min(warter) as warter_diff from t1 group by roomid 
order by roomid,createtime desc;

+——–+———————+——–+————-+
| roomid | createtime          | warter | warter_diff |
+——–+———————+——–+————-+
|      1 | 2015-04-20 14:56:50 |    210 |          60 |
|      1 | 2015-03-20 14:33:01 |    150 |          50 |
|      1 | 2015-02-20 14:31:57 |    100 |         100 |
|      2 | 2015-04-20 14:34:10 |     50 |          50 |
+——–+———————+——–+————-+
4 rows in set (0.00 sec)

mysql> 


CodeBye 版权所有丨如未注明 , 均为原创丨本网站采用BY-NC-SA协议进行授权 , 转载请注明mysql计算两条数据差值,求大神解答