背景
这篇讲一下我最近遇到的一个问题,并记录保存,方便下次直接查看。
刚开始会讲一些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');
实现
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-atHv2jCz-1631888585408)(access/2021-09-17/img_proxy.gif)]](https://img-blog.csdnimg.cn/ca9ee4418a934deaa2f321711694a846.png)
拿起键盘就是干呀
闭着眼睛写sql
SELECT
id,
create_time,
max( score )
FROM
t_score
GROUP BY
create_time
注意:如果此处你的mysql报错:1055,group by不兼容时,点击此处解决
mysql查询group by 1055 问题完美解决,最简单最便捷的方法
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-YRk7a34k-1631888585411)(access/2021-09-17/统计分数最高.jpg)]](https://img-blog.csdnimg.cn/6ad6f665d2124cf8863c07b7609f3dce.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBA5L2g6Iul5Li65oiR57mB5Y2O,size_15,color_FFFFFF,t_70,g_se,x_16)
用时两分钟,搞定
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-F4Kz4kgU-1631888585413)(access/2021-09-17/dog.gif)]](https://img-blog.csdnimg.cn/938de0c0f4cf4ec4a1f01fd7d025cdbb.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBA5L2g6Iul5Li65oiR57mB5Y2O,size_7,color_FFFFFF,t_70,g_se,x_16)
等等,再看下数据。
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-qsyOLOEW-1631888585414)(access/2021-09-17/原始数据.jpg)]](https://img-blog.csdnimg.cn/e23a88d23c58403ca639cb4d7fdca5e0.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBA5L2g6Iul5Li65oiR57mB5Y2O,size_11,color_FFFFFF,t_70,g_se,x_16)
按照需求所说,查询出每天的最高分最新的数据列表
查询结果的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
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-MzJfeI2V-1631888585417)(access/2021-09-17/长sql.jpg)]](https://img-blog.csdnimg.cn/62e66f32dd12456eba615c87cd77985c.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBA5L2g6Iul5Li65oiR57mB5Y2O,size_20,color_FFFFFF,t_70,g_se,x_16)
结果正确,没问题。
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-wPJ5LfdA-1631888585418)(access/2021-09-17/秒啊.gif)]](https://img-blog.csdnimg.cn/a79aa02213b048f1bccd9d38e2568ce5.png)
实现一
接下来开始写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> {
@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测试
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-KW9TyeMu-1631888585420)(access/2021-09-17/sql查询接口.jpg)]](https://img-blog.csdnimg.cn/79ce58f1763e422790bbdd2e627a5b2a.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBA5L2g6Iul5Li65oiR57mB5Y2O,size_20,color_FFFFFF,t_70,g_se,x_16)
没问题,返回结果也正确,这是第一种实现方式。
实现二
思考:既然我项目中使用了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;
}
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-OLdVvdtt-1631888585421)(access/2021-09-17/接口查询2.jpg)]](https://img-blog.csdnimg.cn/a886ce2bc0554f3f9eda704c590898b6.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBA5L2g6Iul5Li65oiR57mB5Y2O,size_20,color_FFFFFF,t_70,g_se,x_16)
结果没问题,这样拆分也比较好理解,但是又增加了数据库的交互,查询的数据如果有多个日期,则要查多少次数据库。还有没有更好的实现方法呢?思考。
实现三
可以使用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真香。
-
首先是把数据都查询出来; -
以时间来分组,这个LinkedHashMap的key就是时间; -
按照分组后的时间进行比较数据,如果c1大于c2,则留下c1.依次比较; -
转list返回。
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-XRkBzzZs-1631888585422)(access/2021-09-17/接口查询3.jpg)]](https://img-blog.csdnimg.cn/2dab9ae5784f4d84882989e4a172ba49.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBA5L2g6Iul5Li65oiR57mB5Y2O,size_17,color_FFFFFF,t_70,g_se,x_16)
是不是第三种方法更简洁方便呢?
你还有其他的实现方法吗,欢迎沟通讨论,如果讲述不对的地方,欢迎指出来。

|