select
data6.*,
DATE_FORMAT(s.time,'%H:%i:%s') as max_bacteria_time,
cast(s.bacteria*1e15 as SIGNED)/1e15 as max_bacteria
from acm_data as s
right join
(
select
max(cast(t.bacteria*1e15 as SIGNED)/1e15 ) as rmax,
DATE_FORMAT(t.time,'%Y-%m-%d') as rtime
from acm_data as t,deviceinfo as n,area as m
where m.ID=n.area_ID and n.ID=t.device_ID and n.area_ID=1 and t.time>='2009-12-10' and t.time<='2009-12-16'
group by DATE_FORMAT(t.time,'%Y-%m-%d')) as r
on DATE_FORMAT(s.time,'%Y-%m-%d') = r.rtime and cast(s.bacteria*1e15 as SIGNED)/1e15=r.rmax
join
(
select
data5.*,
DATE_FORMAT(p.time,'%H:%i:%s') as max_PM10_time,
cast(p.PM10*1e15 as SIGNED)/1e15 as max_PM10
from acm_data as p
right join
(
select
max(cast(q.PM10*1e15 as SIGNED)/1e15 ) as rmax,
DATE_FORMAT(q.time,'%Y-%m-%d') as rtime
from acm_data as q,deviceinfo as n,area as m
where m.ID=n.area_ID and n.ID=q.device_ID and n.area_ID=1 and q.time>='2009-12-10' and q.time<='2009-12-16'
group by DATE_FORMAT(q.time,'%Y-%m-%d')) as r
on DATE_FORMAT(p.time,'%Y-%m-%d') = r.rtime and cast(p.PM10*1e15 as SIGNED)/1e15=r.rmax
join
(
select
data4.*,
DATE_FORMAT(i.time,'%H:%i:%s') as max_humidity_time,
cast(i.humidity*1e15 as SIGNED)/1e15 as max_humidity
from acm_data as i
right join
(
select
max(cast(j.humidity*1e15 as SIGNED)/1e15 ) as rmax,
DATE_FORMAT(j.time,'%Y-%m-%d') as rtime
from acm_data as j,deviceinfo as n,area as m
where m.ID=n.area_ID and n.ID=j.device_ID and n.area_ID=1 and j.time>='2009-12-10' and j.time<='2009-12-16'
group by DATE_FORMAT(j.time,'%Y-%m-%d')) as r
on DATE_FORMAT(i.time,'%Y-%m-%d') = r.rtime and cast(i.humidity*1e15 as SIGNED)/1e15=r.rmax
join
(
select
data3.*,
DATE_FORMAT(g.time,'%H:%i:%s') as max_temperature_time,
cast(g.temperature*1e15 as SIGNED)/1e15 as max_temperature
from acm_data as g
right join
(
select
max(cast(h.temperature*1e15 as SIGNED)/1e15 ) as rmax,
DATE_FORMAT(h.time,'%Y-%m-%d') as rtime
from acm_data as h,deviceinfo as n,area as m
where m.ID=n.area_ID and n.ID=h.device_ID and n.area_ID=1 and h.time>='2009-12-10' and h.time<='2009-12-16'
group by DATE_FORMAT(h.time,'%Y-%m-%d')) as r
on DATE_FORMAT(g.time,'%Y-%m-%d') = r.rtime and cast(g.temperature*1e15 as SIGNED)/1e15=r.rmax
join
(
select
data2.*,
DATE_FORMAT(e.time,'%H:%i:%s') as max_co2_time,
cast(e.co2*1e15 as SIGNED)/1e15 as max_co2
from acm_data as e
right join
(
select
max(cast(f.co2*1e15 as SIGNED)/1e15 ) as rmax,
DATE_FORMAT(f.time,'%Y-%m-%d') as rtime
from acm_data as f,deviceinfo as n,area as m
where m.ID=n.area_ID and n.ID=f.device_ID and n.area_ID=1 and f.time>='2009-12-10' and f.time<='2009-12-16'
group by DATE_FORMAT(f.time,'%Y-%m-%d')) as r
on DATE_FORMAT(e.time,'%Y-%m-%d') = r.rtime and cast(e.co2*1e15 as SIGNED)/1e15=r.rmax
join
(
select
DATE_FORMAT(c.time,'%H:%i:%s') as max_ch2o_time,
cast(c.ch2o*1e15 as SIGNED)/1e15 as max_ch2o,
data1.*
from acm_data as c
right join
(
select
max(cast(d.ch2o*1e15 as SIGNED)/1e15 ) as rmax,
DATE_FORMAT(d.time,'%Y-%m-%d') as rtime
from acm_data as d,deviceinfo as n,area as m
where m.ID=n.area_ID and n.ID=d.device_ID and n.area_ID=1 and d.time>='2009-12-10' and d.time<='2009-12-16'
group by DATE_FORMAT(d.time,'%Y-%m-%d')) as r
on DATE_FORMAT(c.time,'%Y-%m-%d') = r.rtime and cast(c.ch2o*1e15 as SIGNED)/1e15=r.rmax
join
(
select
r.*,
DATE_FORMAT(a.time,'%Y-%m-%d') as time1,
DATE_FORMAT(a.time,'%H:%i:%s') as max_co_time,
cast(a.co*1e15 as SIGNED)/1e15 as max_co
from acm_data as a
right join
(
select
avg(cast(b.co2*1e15 as SIGNED)/1e15),
avg(cast(b.co*1e15 as SIGNED)/1e15),
avg(cast(b.humidity*1e15 as SIGNED)/1e15),
avg(cast(b.ch2o*1e15 as SIGNED)/1e15),
avg(cast(b.PM10*1e15 as SIGNED)/1e15),
avg(cast(b.bacteria*1e15 as SIGNED)/1e15),
avg(cast(b.temperature*1e15 as SIGNED)/1e15),
max(cast(b.co*1e15 as SIGNED)/1e15 ) as rmax,
DATE_FORMAT(b.time,'%Y-%m-%d') as rtime
from acm_data as b,deviceinfo as n,area as m
where m.ID=n.area_ID and n.ID=b.device_ID and n.area_ID=1 and b.time>='2009-12-10' and b.time<='2009-12-16'
group by DATE_FORMAT(b.time,'%Y-%m-%d')) as r
on DATE_FORMAT(a.time,'%Y-%m-%d') = r.rtime and cast(a.co*1e15 as SIGNED)/1e15=r.rmax) as data1
on DATE_FORMAT(c.time,'%Y-%m-%d')=data1.time1
)as data2 on DATE_FORMAT(e.time,'%Y-%m-%d')=data2.time1
)as data3 on DATE_FORMAT(g.time,'%Y-%m-%d')=data3.time1
)as data4 on DATE_FORMAT(i.time,'%Y-%m-%d')=data4.time1
)as data5 on DATE_FORMAT(p.time,'%Y-%m-%d')=data5.time1
)as data6 on DATE_FORMAT(s.time,'%Y-%m-%d')=data6.time1
谁知道怎么优化这个SQL语句?
最新推荐文章于 2024-08-16 21:30:01 发布