获取每天最高分数且最新的数据（java实现）

背景

这篇讲一下我最近遇到的一个问题，并记录保存，方便下次直接查看。

刚开始会讲一些sql操作，后面会讲java代码实现，本文会从三种方法来实现该需求，如果你也正在遇到同样的问题，可以根据自身需求来选择哪种方式实现。

需求

提供一张表，字段有id、score、create_time（年月日）。

查询出每天的最高分最新的数据列表。

提供数据表结构

CREATE TABLE `t_score` (
  `id` int NOT NULL AUTO_INCREMENT,
  `score` int DEFAULT NULL COMMENT '分数',
  `create_time` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

提供数据

INSERT INTO `t_score` VALUES (1, 5, '2021-09-15');
INSERT INTO `t_score` VALUES (2, 10, '2021-09-15');
INSERT INTO `t_score` VALUES (3, 10, '2021-09-15');
INSERT INTO `t_score` VALUES (4, 15, '2021-09-15');
INSERT INTO `t_score` VALUES (5, 15, '2021-09-15');
INSERT INTO `t_score` VALUES (6, 20, '2021-09-16');
INSERT INTO `t_score` VALUES (7, 15, '2021-09-16');
INSERT INTO `t_score` VALUES (8, 10, '2021-09-16');
INSERT INTO `t_score` VALUES (9, 30, '2021-09-16');
INSERT INTO `t_score` VALUES (10, 30, '2021-09-16');

实现

拿起键盘就是干呀

闭着眼睛写sql

SELECT
	id,
	create_time,
	max( score ) 
FROM
	t_score 
GROUP BY
	create_time

注意：如果此处你的mysql报错:1055,group by不兼容时，点击此处解决

mysql查询group by 1055 问题完美解决，最简单最便捷的方法

用时两分钟，搞定

等等，再看下数据。

按照需求所说，查询出每天的最高分最新的数据列表

查询结果的id应该为5和10才对。

于是，于是，于是我就陷入了沉思…

终于，用了两个小时，写出了下面sql。

SELECT
	max( a1.id ) AS id,
	a1.score,
	a1.create_time 
FROM
	t_score a1
	JOIN ( SELECT create_time, max( score ) AS score FROM t_score GROUP BY create_time ) a2 ON a1.create_time = a2.create_time 
WHERE
	a1.score = a2.score 
GROUP BY
	a1.create_time 
ORDER BY
	a1.id

结果正确，没问题。

实现一

接下来开始写java代码咯，本文使用mybatis-plus来操作哦。

控制层

@RestController
@RequestMapping("score")
public class ScoreController {
    @Autowired
    private ScoreService service;

    @RequestMapping("list1")
    public List<ScoreDo> list1() {
        return service.list1();
    }
}

服务层

public interface ScoreService {
    /**
     * 查询列表
     * @return  列表
     */
    List<ScoreDo> list1();
}

服务实现层

@Service
public class ScoreServiceImpl extends ServiceImpl<ScoreMapper, ScoreDo> implements ScoreService {
    @Resource
    private ScoreMapper scoreMapper;

    @Override
    public List<ScoreDo> list1() {
        return scoreMapper.list1();
    }
}

数据交互层

public interface ScoreMapper extends BaseMapper<ScoreDo> {
    /**
     * 查询列表
     * @return
     */
    @Select("SELECT " +
            " max( a1.id ) as id, " +
            " a1.score as score, " +
            " a1.create_time as createTime " +
            "FROM " +
            " t_score a1 " +
            " JOIN ( SELECT create_time, max( score ) AS score FROM t_score GROUP BY create_time ) a2 ON a1.score = a2.score  " +
            "WHERE " +
            " a1.create_time = a2.create_time  " +
            "GROUP BY " +
            " a1.create_time  " +
            "ORDER BY " +
            " a1.id ASC")
    List<ScoreDo> list1();
}

实体类

@TableName("t_score")
public class ScoreDo {
    private Integer id;
    private Integer score;
    private String createTime;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public Integer getScore() {
        return score;
    }

    public void setScore(Integer score) {
        this.score = score;
    }

    public String getCreateTime() {
        return createTime;
    }

    public void setCreateTime(String createTime) {
        this.createTime = createTime;
    }

    @Override
    public String toString() {
        return "ScoreDo{" +
                "id=" + id +
                ", score=" + score +
                ", createTime='" + createTime + '\'' +
                '}';
    }
}

nice，posman测试

没问题，返回结果也正确，这是第一种实现方式。

实现二

思考：既然我项目中使用了mybatis-plus，但是刚才的操作好像和plus没关系啊。

而且这个sql也过长，不容易阅读和理解。

能否把这个长SQL，改良为两个短sql。

说干就干，那我就先查询出最高分和日期，再查询最高的id。

控制层

@RequestMapping("list2")
public List<ScoreDo> list2() {
    return service.list2();
}

服务层

List<ScoreDo> list2();

服务实现层

@Override
public List<ScoreDo> list2() {
    QueryWrapper<ScoreDo> queryWrapper = new QueryWrapper<>();
    queryWrapper.select("create_time,max(score) as score");
    queryWrapper.groupBy("create_time");
    List<ScoreDo> list = this.list(queryWrapper);
    list.forEach(scoreDo -> {
        QueryWrapper<ScoreDo> scoreDoQueryWrapper = new QueryWrapper<>();
        scoreDoQueryWrapper.select("max(id) as id,score,create_time");
        scoreDoQueryWrapper.eq("create_time", scoreDo.getCreateTime());
        scoreDoQueryWrapper.eq("score", scoreDo.getScore());
        scoreDoQueryWrapper.groupBy("create_time");
        ScoreDo score = this.getOne(scoreDoQueryWrapper);
        scoreDo.setId(score.getId());
    });
    return list;
}

结果没问题，这样拆分也比较好理解，但是又增加了数据库的交互，查询的数据如果有多个日期，则要查多少次数据库。还有没有更好的实现方法呢？思考。

实现三

可以使用java8新特性来处理。

控制层

@RequestMapping("list3")
public List<ScoreDo> list3() {
    return service.list3();
}

服务层

List<ScoreDo> list3();

服务实现层

@Override
public List<ScoreDo> list3() {
    List<ScoreDo> scoreDoList = scoreMapper.selectList(null);
    LinkedHashMap<String, ScoreDo> linkedHashMap = scoreDoList.stream().collect(
            Collectors.toMap(ScoreDo::getCreateTime,
                    Function.identity(), (c1, c2) -> c1.getScore() > c2.getScore() ? c1 : c2, LinkedHashMap::new));
    List<ScoreDo> list = new ArrayList<>(linkedHashMap.size());
    linkedHashMap.forEach((x, y) -> list.add(y));
    return list;
}

从代码来看3行解决，不得不说，java8真香。

1. 首先是把数据都查询出来；

2. 以时间来分组，这个LinkedHashMap的key就是时间；

3. 按照分组后的时间进行比较数据，如果c1大于c2，则留下c1.依次比较；

4. 转list返回。

是不是第三种方法更简洁方便呢？

你还有其他的实现方法吗，欢迎沟通讨论，如果讲述不对的地方，欢迎指出来。