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;
还没有评论,来说两句吧...