insert into B
select t1.id
from (select id,value value1 from A where rq = 200504) t1,
(select id,value*0.5 value2 from A where rq = 200503) t2
where t1.value1 <= t2.value2 and t1.id = t2.id
select t1.id
from (select id,value value1 from A where rq = 200504) t1,
(select id,value*0.5 value2 from A where rq = 200503) t2
where t1.value1 <= t2.value2 and t1.id = t2.id
不过可以增加索引,以加快速度,
在value和id上键里索引
可以删掉所以试试。
select A.id
from A,
(select * from A where rq = 200503) t
where A.id = t.id
and A.value <= t.value * 0.5
and A.rq = 200404
select a.id
from A as a ,A as b
where a.id = b.id
and a.value<= b.value*0.5
and a.rq = 200404
and b.rq = 200503
是你自己数据库的问题,和语句无关吧
select id from a a1
where rq=200504
and exists(select 1 from a where rq=200503 and id=a1.id and (value + value - a1.value - a1.value)<value);
//建立双列索引(rq,id)。上边的写法不但可以保证正确的使用索引,而且还用加法替换了乘法
说明数据量很大,检索的时候加索引也只能在id加,可以把*改为实名,select id,value from A 这样开销毁小一点,但好像没有在优化的方法了。
**但要insert就会需要30多分钟
因为检索出来数据量很大,所以插入时间自然耗时,如果B表ID索引就给他手工删掉,因为影响插入的速度和索引有关,
另外也可能是表空间已满,表空间自动增加的耗时也比较大。