sql查询相邻两行列的字段的差值

1.如果id字段是自动递增的，而且不需要通过时间倒序（正序）排列来进行差值，一条简单的sql语句解决

如：

CREATE TABLE `test` (
  `id` int(20) NOT NULL AUTO_INCREMENT,
  `createDate` datetime DEFAULT NULL,
  `counter` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

INSERT INTO `test` VALUES ('1', '2014-01-01 00:00:00', '100');
INSERT INTO `test` VALUES ('2', '2014-01-05 00:00:00', '105');
INSERT INTO `test` VALUES ('3', '2014-01-04 00:00:00', '102');
INSERT INTO `test` VALUES ('4', '2014-01-09 00:00:00', '99');
INSERT INTO `test` VALUES ('5', '2014-01-10 00:00:00', '103');

select a.id,a.counter,  a.counter -b.counter as counterDiffer
from test a
,test b
where a.id = b.id -1（该表id自增）

2.如果是根据时间倒序（正序）排列来进行差值，而且id不自增是32位UUID,sql查询有点麻烦，这时候可以在js处理

如：

查出来的数据放入到Array中，然后循环Array相减，再放入到对应的该Array

var differArr = new Array();
    differArr =[{createDate:1543210566000,drugValue:192,
hotelId:"cf37ee0de1b6414a873e217f3d221971",
hotelName:"旅馆测试2",hotelPhoto:"44",
id:"774b18df02604189ad6cd072c850fad3",
qtime:1543075200,totalValue:415,unregValue:163,yellowValue:60},{createDate:1543210566000,drugValue:192,
hotelId:"cf37ee0de1b6414a873e217f3d221971",
hotelName:"旅馆测试2",hotelPhoto:"44",
id:"774b18df02604189ad6cd072c850fad3",
qtime:1543075200,totalValue:415,unregValue:163,yellowValue:60}
   ]

 for(var i=0;i<differArr.length-1;i++){
      var differValue=differArr[i].unregValue-differArr[i+1].unregValue;
      var yellowValue=differArr[i].yellowValue-differArr[i+1].yellowValue;
      var drugValue=differArr[i].drugValue-differArr[i+1].drugValue;
      if(differValue>=0){
          differArr[i]['unregDiffer'] = "+"+differValue;
      }else {
          differArr[i]['unregDiffer'] = differValue;
      }
      if(yellowValue>=0){
          differArr[i]['yellowDiffer'] = "+"+yellowValue;
      }else {
          differArr[i]['yellowDiffer'] = yellowValue;
      }
      if(drugValue>=0){
          differArr[i]['drugDiffer'] = "+"+drugValue;
      }else {
          differArr[i]['drugDiffer'] = drugValue;
      }
  }