group by和order by在springboot中连用03

1 mysql最初代码

SELECT DATE_FORMAT(update_time, "%Y") AS year FROM t_daily
GROUP BY year
ORDER BY MIN(update_time) DESC;

参考链接

• 1 order by查询的必须是原本的数据，不能是处理后的数据（year）
• 2 DATE_FORMAT(update_time, “%Y”) ：将日期字段update_time格式化为仅剩下年份
• 3 group by和order by连用时，order by必须放在聚合函数中，聚合函数任意，不放的话报错：

Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'daily.t_daily.update_time' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

2 springboot中

@Query("select function('date_format',d.updateTime,'%Y') as year from t_daily d group by year order by MIN(d.updateTime) desc ") //按照年份
List<String> findGroupYear(); //返回年份

• 数据渲染时发现hashmap输出的数据是无序的，若想让数据有序渲染到页面，应该使用LinkedHashMap

Map<String,List<Daily>> map = new LinkedHashMap<>();