mysql - SQL LIMIT 动态 N

Question

我想从具有指定 county_number 的所有站点获取(最后一行)平均 air_temperature。

因此，我的解决方案是这样的

SELECT AVG(air_temperature) 
  FROM weather 
 WHERE station_id IN (
       SELECT station_id 
         FROM stations 
        WHERE county_number = 25
       )
 ORDER 
    BY id DESC 
 LIMIT 1; 

显然，这并没有给出正确的行，因为它返回的是基于一个站点曾经记录的所有 air_temperature 的平均 air_temperature。

回到问题，我想从具有指定 county_number 的每个站点获取最后插入行的平均 air_temperature。

表天气

+------------------+-------------+------+-----+---------+----------------+
| Field            | Type        | Null | Key | Default | Extra          |
+------------------+-------------+------+-----+---------+----------------+
| id               | int(11)     | NO   | PRI | NULL    | auto_increment |
| station_id       | char(20)    | YES  | MUL | NULL    |                |
| timestamp        | timestamp   | YES  |     | NULL    |                |
| air_temperature  | float       | YES  |     | NULL    |                |
+------------------+-------------+------+-----+---------+----------------+

桌面站

+---------------+-------------+------+-----+---------+-------+
| Field         | Type        | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| station_id    | char(20)    | NO   | PRI | NULL    |       |
| county_number | int(10)     | YES  |     | NULL    |       |
+---------------+-------------+------+-----+---------+-------+

表格被最小化

Answer 1

我建议使用 join 和一些过滤来做到这一点:

select avg(w.air_temperature)
from weather w join
     stations s
     on w.station_id = s.station_id
where s.county_number = 25 and
      w.timestamp = (select max(w2.timestamp) from weather w2 where w2.station_id = w.station_id)