MySQL按天统计数据并按格式化后的日期排序

需求:

需要按天统计数据总量并按日期排序

数据库部分字段如下

in_date	trash_weight
2021-01-14 15:04:45	133
2021-01-14 12:14:45	223
2021-01-14 13:05:15	145
2021-01-14 18:06:322	121

1、直接进行查询发现数据并不按日期排序

SELECT
	DATE_FORMAT( in_date,'%c-%e') day,
	round(sum( trash_weight )/1000,2) weight
FROM
	tableName
GROUP BY day

SELECT
	DATE_FORMAT( in_date,'%c-%e') days,
	in_date,
	round(sum( trash_weight )/1000,2) weight
FROM
	tableName
GROUP BY days,in_date

2、考虑先将表中数据排序再进行查询,发现依然不是按日期排序

SELECT
	DATE_FORMAT(a.in_date,'%c-%e') day,
	round(sum(a.trash_weight)/1000,2) weight
FROM
	(select in_date,trash_weight from tableName ORDER BY in_date ) a
GROUP BY day

3、最后考虑增加一个排序的依据(计算每个日期与某个日期的相差天数)

SELECT
    DATE_FORMAT( in_date, '%c-%e' ) day,
    round(sum(trash_weight)/1000, 2) weight,
    datediff( now(), in_date ) sort
FROM
    tableName
GROUP BY day,sort
ORDER BY sort DESC