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

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

### 需求 ###

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

### 测试数据 ###

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;