如何求出不符合增幅范围的记录？

Question

假设A表，有字段timestamp(时间戳),name（记录事件发生地，不唯一）,value（值，浮点数）;
指定value字段增幅合法范围为1.8到8.9之间；任意一条记录中value比上一条记录的增幅在1.8之下或者8.9之上的都认为是不合格的数据，要显示出来；这样的sql语句应该怎么写？ 
请各位大大不吝赐教，不胜感激！

Answer 1

补充一下：是MySQL版本5的数据库

Answer 2

举例timestamp                          name                value
------------------              -----------            ---------------------------
2009-02-03 11:00:00                  泽里                 1.2
2009-02-03 11:05:00                  泽里                 4.2
2009-02-03 11:10:00                  泽里                 14.2
2009-02-03 11:15:00                  泽里                 17.5
2009-02-03 11:20:00                 泽里         34.2
2009-02-03 11:25:00                  泽里                  4.2
增幅在1.8和8.9之间为合理范围，因此查阅出的不合格记录应当是timestamp                          name                value
------------------               -----------            ----------------------
2009-02-03 11:10:00                泽里                     14.2
2009-02-03 11:20:00               泽里                    34.2
2009-02-03 11:25:00                泽里                     4.2

Answer 3

select 
  a.`timestamp`,
  a.`name`,
  a.`value`
from 
  (select *,px=(select count(1)+1 from tb where name=t.name and `timestamp`<t.`timestamp`) from tb t) a,
  (select *,px=(select count(1)+1 from tb where name=t.name and `timestamp`<t.`timestamp`) from tb t) b
where
  a.px=b.px+1
and
  a.value-b.value not between 1.8 and 8.9

Answer 4

mysql> select * from t_little_fairycat;
+---------------------+------+-------+
| timestamp           | name | value |
+---------------------+------+-------+
| 2009-02-03 11:00:00 | 泽里 |  1.20 |
| 2009-02-03 11:05:00 | 泽里 |  4.20 |
| 2009-02-03 11:10:00 | 泽里 | 14.20 |
| 2009-02-03 11:15:00 | 泽里 | 17.50 |
| 2009-02-03 11:20:00 | 泽里 | 34.20 |
| 2009-02-03 11:25:00 | 泽里 |  4.20 |
+---------------------+------+-------+
6 rows in set (0.00 sec)mysql> select *
    -> from t_little_fairycat t
    -> where t.value-(select `value` from t_little_fairycat where `timestamp`<t.
`timestamp` order by `timestamp` desc limit 1 ) not between 1.8 and 8.9;
+---------------------+------+-------+
| timestamp           | name | value |
+---------------------+------+-------+
| 2009-02-03 11:10:00 | 泽里 | 14.20 |
| 2009-02-03 11:20:00 | 泽里 | 34.20 |
| 2009-02-03 11:25:00 | 泽里 |  4.20 |
+---------------------+------+-------+
3 rows in set (0.00 sec)mysql>另外建议请勿使用保留字做为字段名。

Answer 5

4楼的语句试验没通过，报个Unknown column 'px' in 'field list'

Answer 6

谢谢五楼的sql语句，按照你的指点我终于得出了想要的结果；
另我实际的字段不是这样的，这边为了举例随便弄了一些数据，没留心用了保留字，谢谢你的指点，我以后一定会注意的

Answer 7

忘了mysql不能直接用＝号select 
  a.`timestamp`,
  a.`name`,
  a.`value`
from 
  (select *,(select count(1)+1 from tb where name=t.name and `timestamp`<t.`timestamp`) as px from tb t) a,
  (select *,(select count(1)+1 from tb where name=t.name and `timestamp`<t.`timestamp`) as px from tb t) b
where
  a.px=b.px+1
and
  a.value-b.value not between 1.8 and 8.9

Answer 8

上述两种方法我都得到了想要的结果；不过这两种方法限制性很大，因为都用到了除了要比较的value列之外的字段做查询条件；现在我想要建立一个临时表，把原表中符合条件的数据（比如name='泽里'）原封不动拷贝过去，只多加一个rownum作为字段，然后再在临时表里只要row+1的value和row的value想减即可得到想要的结果了，这样就可以最大限度摆脱时间字段的限制了
我知道sql server里是可以这样写：
select identity(int,1,1) as idd ,* into #tmp from tb where name='泽里'
select 
  a.* 
from 
  #tmp a 
left join 
  #tmp b 
on 
  a.idd=b.idd-1 
where 
  b.value-a.value not between 100 and 200
不知道改成MySQL该如何写？

Answer 9

select identity(int,1,1) as idd ,* into #tmp from tb where name='泽里'
select 
  a.* 
from 
  #tmp a 
left join 
  #tmp b 
on 
  a.idd=b.idd-1 
where 
  b.value-a.value not between 100 and 200
------------>set @i=0;create temporary table #tmp
as
select @i:=@i+1 as idd ,*  from tb where name='泽里';select 
  a.* 
from 
  #tmp a 
left join 
  #tmp b 
on 
  a.idd=b.idd-1 
where 
  (b.value-a.value) not between 100 and 200;

Answer 10

set @i=0;create temporary table tmp
as
select @i:=@i+1 as idd ,其它需要的列清单  from tb where name='泽里';select 
  a.* 
from 
  tmp a 
left join 
  tmp b 
on 
  a.idd=b.idd-1 
where 
  (b.value-a.value) not between 100 and 200;

Answer 11

总是报Table 'dbname.tmp' doesn't exist是怎么回事呢？

Answer 12

我在mysql query browser的窗口里直接全部输入点执行的，难道它是默认一句话就是一个会话？不应该啊

Answer 13

set @i=0; create temporary table tmp 
as 
select @i:=@i+1 as idd ,*  from tb where name='泽里'; 
原来这样写是有错的，改成set @i=0; create temporary table tmp 
as 
select *, @i:=@i+1 as iddfrom tb where name='泽里'; 这样就好了，奇怪哦；
不过后面select 
  a.* 
from 
  tmp a 
left join 
  tmp b 
on 
  a.idd=b.idd-1 
where 
  (b.value-a.value) not between 100 and 200;却又报错can't reopen table:'a'
还要继续排查一下

Answer 14

原来can't reopen table:'a' 是MySQL临时表一直不能解决的bug，现在我用了两张临时表解决了问题，现贴出最终解决代码：use test_db;
set @i=0; 
set @j=0; 
--创建临时表一
create temporary table tmp1 as select  *, @i:=@i+1 as idd  from tb where name='泽里'; 
--创建临时表二
create temporary table tmp2 as select  *, @j:=@j+1 as idd  from tb where name='泽里'; 
--result
select  * from  tmp1 left join  tmp2 on  tmp1.idd=tmp2.idd-1 where  (tmp2.value-tmp1.value) not between 100 and 200;感谢各位拔刀相助，结贴