场景
感觉一句话说不清,还是结合场景来说吧,如果已经对问题有了解的小伙伴直接看最后结论就可以了。
上图是我们要做处理的数据,我们要做的是拿到每天最后一次更新的数据,也就是需要得到每天中create_date时间最大的数据。首先想到的肯定是通过group by分组,得到每天的数据。SQL语句如下:
SELECT create_date,price FROM material_price_history
WHERE del_flag = 0
AND create_date >= '2019-12-01'
AND create_date < '2019-12-06'
AND material_code_id='materialCode6'
AND type=1
GROUP BY
DATE_FORMAT(create_date, '%Y-%m-%d')
这里很简单,条件是12月1号到6号的数据,然后根据create_date的年月日进行分组,这样每组数据就是每天的价格。可以看到结果如下:
这里得到12月1号的数据并不是create_date时间最大的数据,因为17点的时候还进行过一次数据的更新。这里如果不进行任何处理,默认得到的是id最小的数据。
解决办法
这里肯定想到的是利用子查询,即首先对数据进行排序得到临时数据表,之后再对这个临时表进行group by操作。
SELECT create_date,price FROM (SELECT create_date,price FROM material_price_history
WHERE del_flag = 0
AND create_date >= '2019-12-01'
AND create_date < '2019-12-06'
AND material_code_id='materialCode6'
AND type=1
ORDER BY create_date DESC) e
GROUP BY
DATE_FORMAT(create_date, '%Y-%m-%d')
得到结果如下:
我们发现结果还是没有改变。
查阅资料后发现,在5.7版本后的mysql必须要在后面加limit才可以得到我们想要的结果。这里推测是高版本的sql对子查询中的ORDER BY操作做了优化处理,导致实际上没有进行排序。
我的mysql版本是:
但在没有limit时,依旧无法得到正确结果。因此若结果不正确,加上limit语句后即可解决。
结论
利用子查询,使用ORDER BY 和LIMIT语句,可以得到正确的结果。SQL语句如下:
SELECT create_date,price FROM (SELECT create_date,price FROM material_price_history
WHERE del_flag = 0
AND create_date >= '2019-12-01'
AND create_date < '2019-12-06'
AND material_code_id='materialCode6'
AND type=1
ORDER BY create_date DESC LIMIT 100000000) e
GROUP BY
DATE_FORMAT(create_date, '%Y-%m-%d')
最后得到的结果:
可以看到得到的为创建时间17点的数据