mysql平均最新5行

Question

我有 table :

describe tests;

+-----------+-----------+------+-----+-------------------+-----------------------------+
| Field     | Type      | Null | Key | Default           | Extra                       |
+-----------+-----------+------+-----+-------------------+-----------------------------+
| id        | int(11)   | NO   | PRI | NULL              | auto_increment              |
| line_id   | int(11)   | NO   |     | NULL              |                             |
| test_time | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| alarm_id  | int(11)   | YES  |     | NULL              |                             |
| result    | int(11)   | NO   |     | NULL              |                             |
+-----------+-----------+------+-----+-------------------+-----------------------------+

我执行查询:

SELECT avg(result) FROM tests WHERE line_id = 4 ORDER BY test_time LIMIT 5;

我想生成 5 个最新结果的平均值。还是有问题，因为查询生成所有表数据的平均值。可能出了什么问题？

Answer 1

如果您想要最后五行，那么您需要按时间列按降序顺序排序:

select avg(result)
from (select result
      from tests
      where line_id = 4
      order by test_time desc
      limit 5
     ) t