假设A表,有字段timestamp(时间戳),name(记录事件发生地,不唯一),value(值,浮点数);
指定value字段增幅合法范围为1.8到8.9之间;任意一条记录中value比上一条记录的增幅在1.8之下或者8.9之上的都认为是不合格的数据,要显示出来;这样的sql语句应该怎么写?
请各位大大不吝赐教,不胜感激!
指定value字段增幅合法范围为1.8到8.9之间;任意一条记录中value比上一条记录的增幅在1.8之下或者8.9之上的都认为是不合格的数据,要显示出来;这样的sql语句应该怎么写?
请各位大大不吝赐教,不胜感激!
解决方案 »
- 求助。表1有a、b两值,表2有c d e f四值,如何配对产生新表?
- group by与group_concat结合使用总会造成结果的排序,怎么可以避免这个
- navicate for mysql8 数据传输错误。
- 关于mysql数据迁移
- XP下mysql6我MY.INI配置,我贴出来大家对比一下,太长还有1楼
- mysql 线程问题(并发),请教大侠们,顶者有分!
- my sql给表重命名
- 有没有关于MySql5的书籍呀?
- 如何学习jsp+mysql?
- openfire服务器设置过程遇到的问题,急急急!
- MsMql用DTS导出数据到MySql问题 来个人啊~~~~
- 问个简单的问题insert嵌套select
------------------ ----------- ---------------------------
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
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
+---------------------+------+-------+
| 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>另外建议请勿使用保留字做为字段名。
另我实际的字段不是这样的,这边为了举例随便弄了一些数据,没留心用了保留字,谢谢你的指点,我以后一定会注意的
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
我知道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该如何写?
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;
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;
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'
还要继续排查一下
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;感谢各位拔刀相助,结贴