mysql 查询每个分组中最新一条记录的分数

最新推荐文章于 2024-03-16 14:06:39 发布

萧、宇情

最新推荐文章于 2024-03-16 14:06:39 发布

阅读量120

点赞数

文章标签： mysql 数据库 java

本文链接：https://blog.csdn.net/weixin_45733891/article/details/130324936

版权

基础数据准备：

CREATE TABLE `test` (
  `id` int(11) NOT NULL,
  `proj_id` varchar(36) DEFAULT NULL,
  `proj_name` varchar(255) DEFAULT NULL,
  `time` datetime DEFAULT NULL,
  `score` decimal(5,2) DEFAULT NULL,
  `crop_id` varchar(36) DEFAULT NULL,
  `crop_name` varchar(255) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `test` (`id`, `proj_id`, `proj_name`, `time`, `score`, `crop_id`, `crop_name`, `create_time`) VALUES (1, '123123', 'A', '2023-04-22 12:00:00', 80.00, '123456', '公司1', '2023-04-22 12:00:00');
INSERT INTO `test` (`id`, `proj_id`, `proj_name`, `time`, `score`, `crop_id`, `crop_name`, `create_time`) VALUES (2, '123123', 'A', '2023-04-21 12:00:00', 90.00, '123456', '公司1', '2023-04-22 11:00:00');
INSERT INTO `test` (`id`, `proj_id`, `proj_name`, `time`, `score`, `crop_id`, `crop_name`, `create_time`) VALUES (3, '123123', 'A', '2023-03-20 12:00:00', 100.00, '123456', '公司1', '2023-04-22 10:00:00');
INSERT INTO `test` (`id`, `proj_id`, `proj_name`, `time`, `score`, `crop_id`, `crop_name`, `create_time`) VALUES (4, '456456', 'B', '2023-04-22 12:00:00', 100.00, '123456', '公司1', '2023-04-22 09:00:00');
INSERT INTO `test` (`id`, `proj_id`, `proj_name`, `time`, `score`, `crop_id`, `crop_name`, `create_time`) VALUES (5, '456456', 'B', '2022-04-21 12:00:00', 90.00, '123456', '公司1', '2023-04-22 08:00:00');
INSERT INTO `test` (`id`, `proj_id`, `proj_name`, `time`, `score`, `crop_id`, `crop_name`, `create_time`) VALUES (6, '456456', 'B', '2022-04-20 12:00:00', 80.00, '123456', '公司1', '2023-04-22 07:00:00');
INSERT INTO `test` (`id`, `proj_id`, `proj_name`, `time`, `score`, `crop_id`, `crop_name`, `create_time`) VALUES (7, '789789', 'C', '2023-04-22 12:00:00', 90.00, '456789', '公司2', '2023-04-22 06:00:00');
INSERT INTO `test` (`id`, `proj_id`, `proj_name`, `time`, `score`, `crop_id`, `crop_name`, `create_time`) VALUES (8, '789789', 'C', '2023-03-21 12:00:00', 80.00, '456789', '公司2', '2023-04-22 05:00:00');
INSERT INTO `test` (`id`, `proj_id`, `proj_name`, `time`, `score`, `crop_id`, `crop_name`, `create_time`) VALUES (9, '789789', 'C', '2022-04-20 12:00:00', 100.00, '456789', '公司2', '2023-04-22 04:00:00');

查询语句：

SELECT
	a.crop_id,
	a.crop_name,
	a.proj_id,
	a.proj_name,
	a.year_time,
	any_value(a.score)
FROM (
	SELECT 
		crop_id,
		crop_name,
		proj_id,
		proj_name,
		year(time) as year_time,
		score 
	FROM test 
	ORDER BY create_time DESC 
) a 
GROUP BY
a.crop_id,
a.crop_name,
a.proj_id,
a.proj_name,
a.year_time

查询结果：
在这里插入图片描述