根据提供的数据，目标是从历史记录表中汇总每天的数据，并确保每次汇总都包含当天的最新记录信息。当前的SQL查询能实现这一目标，但在数据量大时效率较低。问题在于如何将此查询转换为JOIN操作以提高性能。
需求
为了汇总，并且取出当天最后一次记录的信息。
表结构及数据mysql> select * from history order by logtime asc;
+----+--------+---------+---------+-------+---------------------+
| id | code | price | cost | unit | logtime |
+----+--------+---------+---------+-------+---------------------+
| 3 | 601319 | 5.0000 | 2.0000 | 1000 | 2020-11-12 20:24:09 |
| 4 | 601319 | 4.0000 | 3.0000 | -1000 | 2020-11-16 05:24:09 |
| 1 | 601319 | 12.0100 | 10.5000 | 1000 | 2020-11-16 10:23:41 |
| 2 | 601319 | 14.0000 | 12.5000 | -200 | 2020-11-16 21:23:41 |
+----+--------+---------+---------+-------+---------------------+
4 rows in set (0.00 sec)
现在的SQL
可以用这个SQL取出来，如果数据较多的时候，这个明显比较浪费了，如何写成JOIN？mysql> select date(logtime) dt,count(1) num, sum(cost),avg(cost),
-> ( select `price` from history where id=max(a.id) ) as price,
-> ( select `cost` from history where id=max(a.id) ) as cost,
-> ( select `unit` from history where id=max(a.id) ) as unit
-> from history a
-> group by dt;
+------------+-----+-----------+------------+--------+--------+-------+
| dt | num | sum(cost) | avg(cost) | price | cost | unit |
+------------+-----+-----------+------------+--------+--------+-------+
| 2020-11-16 | 3 | 26.0000 | 8.66666667 | 4.0000 | 3.0000 | -1000 |
| 2020-11-12 | 1 | 2.0000 | 2.00000000 | 5.0000 | 2.0000 | 1000 |
+------------+-----+-----------+------------+--------+--------+-------+
2 rows in set (0.01 sec)
4270
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
