1.分组获取日期最新一条数据
现在有一个需求,先根据age分组,再获取分组中日期的最新一条。
解决:
左连接查询,对笛卡尔积进行条件筛选,如下:
select * from user a
left join user b on a.age= b.age
and a.date_time < b.date_time
where b.date_time is null
如果需要添加条件可以进行如下条件筛选:
select * from user a
left join user b on a.age= b.age
and a.date_time < b.date_time
and a.delete_flag = b.delete_flag
where b.date_time is null
and a.id = 1
and delete_flag = false
2.MySQL先group by再order by怎么操作?
先通过SQL进行分组
select * from user group by age
再通过java代码排序
List<User> list = new ArrayList<>();
// 正序
list.sort(Comparator.comparing(User::getDateTime));
// 倒序
Collections.reverse(list);