mysql 取出当天最大一次记录信息再汇总

Question

需求

为了汇总，并且取出当天最后一次记录的信息。

表结构及数据

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)

Answer 1

select t1.*,t2.price,t2.cost,t2.unit 
from (select date(logtime) dt,count(1) num, sum(cost),avg(cost),max(id) max_id
      from history
      group by dt) t1 
inner join history t2 on t1.max_id=t2.id;