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

快来打我* 2022-12-21 15:49 152阅读 0赞

需求

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

测试数据

  1. mysql> select * from history order by logtime asc;
  2. +----+--------+---------+---------+-------+---------------------+
  3. | id | code | price | cost | unit | logtime |
  4. +----+--------+---------+---------+-------+---------------------+
  5. | 3 | 601319 | 5.0000 | 2.0000 | 1000 | 2020-11-12 20:24:09 |
  6. | 4 | 601319 | 4.0000 | 3.0000 | -1000 | 2020-11-16 05:24:09 |
  7. | 1 | 601319 | 12.0100 | 10.5000 | 1000 | 2020-11-16 10:23:41 |
  8. | 2 | 601319 | 14.0000 | 12.5000 | -200 | 2020-11-16 21:23:41 |
  9. +----+--------+---------+---------+-------+---------------------+
  10. 4 rows in set (0.00 sec)

实现代码

方式1.

  1. select a.*,date(a.logtime) dt
  2. from history a
  3. left join history b
  4. on a.code=b.code
  5. and ((a.logtime > b.logtime) or (a.logtime=b.logtime and a.id>b.id))
  6. group by a.code,dt;
  7. +----+--------+---------+---------+------+---------------------+------------+
  8. | id | code | price | cost | unit | logtime | dt |
  9. +----+--------+---------+---------+------+---------------------+------------+
  10. | 2 | 601319 | 14.0000 | 12.5000 | -200 | 2020-11-16 21:23:41 | 2020-11-16 |
  11. | 3 | 601319 | 5.0000 | 2.0000 | 1000 | 2020-11-12 20:24:09 | 2020-11-12 |
  12. +----+--------+---------+---------+------+---------------------+------------+
  13. 2 rows in set (0.00 sec)

方式2.

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

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

方式3.(错误的)

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

发表评论

表情:
评论列表 (有 0 条评论,152人围观)

还没有评论,来说两句吧...

相关阅读