正序: select a.area_name, a.tot, (a.tot/b.num)-(select tot from 区域表 where area_name=a.area_name and freq=a.freq-1)/(select num from 总数表 where freq=a.freq-1) as X from 区域表 a ,总数表 b where a.freq=(select max(c.freq) from 区域表 c) and a.freq=b.freq order by X ASC limit 10;倒序: select a.area_name, a.tot, (a.tot/b.num)-(select tot from 区域表 where area_name=a.area_name and freq=a.freq-1)/(select num from 总数表 where freq=a.freq-1) as X from 区域表 a ,总数表 b where a.freq=(select max(c.freq) from 区域表 c) and a.freq=b.freq order by X DESC limit 10;
select a.area_name, a.tot as 最新一期该区域的数量, a.tot/b.num-c.tot/d.num as 所得值X from 区域表 a inner join 总数表 b on a.freq=b.freq inner join 区域表 c on a.freq=c.freq+1 inner join 总数表 d on c.freq=d.freq where a.freq = (select max(freq) from 总数表)
num---------------------------freq
600---------------------------2
860---------------------------3
1500--------------------------19
2000--------------------------20
区域表是
area_name------------tot------------freq
浙江-----------------200-------------2
广东-----------------100-------------2
山西-----------------101-------------2
浙江-----------------150-------------19
广东-----------------100-------------19
山西-----------------101-------------19
浙江-----------------200-------------20
广东-----------------190-------------20
山西-----------------151-------------20区域名称--------最新一期该区域的数量即第20期--------所得值X
浙江----------------200-----------------------------X
广东----------------190-----------------------------X
山西----------------151-----------------------------x
只计算当前所拥有的每期数据的最新一期也就是数值最大的一期 与这个最大日期之前一期的信息做比较
select
a.area_name,
a.tot,
(a.tot/b.num)-(select tot from 区域表 where area_name=a.area_name and freq=a.freq-1)/(select num from 总数表 where freq=a.freq-1) as X
from 区域表 a ,总数表 b where a.freq=(select max(c.freq) from 区域表 c) and a.freq=b.freq
order by X ASC limit 10;倒序:
select
a.area_name,
a.tot,
(a.tot/b.num)-(select tot from 区域表 where area_name=a.area_name and freq=a.freq-1)/(select num from 总数表 where freq=a.freq-1) as X
from 区域表 a ,总数表 b where a.freq=(select max(c.freq) from 区域表 c) and a.freq=b.freq
order by X DESC limit 10;
a.tot/b.num-c.tot/d.num as 所得值X
from 区域表 a inner join 总数表 b on a.freq=b.freq
inner join 区域表 c on a.freq=c.freq+1
inner join 总数表 d on c.freq=d.freq
where a.freq = (select max(freq) from 总数表)