mysql 取出最大最小记录并汇总

需求

按天汇总，并取出当天最后一次的记录。

测试数据

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)

实现代码

方式1.

select a.*,date(a.logtime) dt
from history a
left join history b 
on a.code=b.code
and ((a.logtime > b.logtime) or (a.logtime=b.logtime and a.id>b.id))
group by a.code,dt;

+----+--------+---------+---------+------+---------------------+------------+
| id | code   | price   | cost    | unit | logtime             | dt         |
+----+--------+---------+---------+------+---------------------+------------+
|  2 | 601319 | 14.0000 | 12.5000 | -200 | 2020-11-16 21:23:41 | 2020-11-16 |
|  3 | 601319 |  5.0000 |  2.0000 | 1000 | 2020-11-12 20:24:09 | 2020-11-12 |
+----+--------+---------+---------+------+---------------------+------------+
2 rows in set (0.00 sec)

方式2.

select a.*,date(a.logtime) dt
from history a
left outer join history b 
on a.code=b.code and date(a.logtime) = date(b.logtime) and a.`logtime` > b.`logtime`
where b.id is null
group by a.code,dt;

参考来源：https://thoughtbot.com/blog/ordering-within-a-sql-group-by-clause

方式3.(错误的)

select date(logtime) dt,count(1) num, sum(cost),avg(cost),
 ( select `price` from history where date(logtime)=date(a.logtime) and id=max(a.id)) as price,
 ( select `cost` from history where date(logtime)=date(a.logtime) and id=max(a.id) ) as cost,
 ( select `unit` from history where date(logtime)=date(a.logtime) and id=max(a.id) ) as unit
from history a
group by dt,code;