max等聚合函数和group by 使用时，需要HAVING，否则查出的有误

静水流深LY

已于 2024-08-29 21:01:35 修改

阅读量215

点赞数 2

文章标签： java 前端数据库

于 2024-08-29 21:00:15 首次发布

本文链接：https://blog.csdn.net/dota51888/article/details/141688360

版权

-- ----------------------------
-- Table structure for score
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `subject` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `no` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `score` double(12, 4) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 10 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of score
-- ----------------------------
INSERT INTO `score` VALUES (1, '语文', '王一', '001', 99.0000);
INSERT INTO `score` VALUES (2, '语文', '周二', '002', 98.0000);
INSERT INTO `score` VALUES (3, '语文', '徐三', '003', 97.0000);
INSERT INTO `score` VALUES (4, '数学', '王一', '001', 91.0000);
INSERT INTO `score` VALUES (5, '数学', '周二', '002', 96.0000);
INSERT INTO `score` VALUES (6, '数学', '徐三', '003', 92.0000);
INSERT INTO `score` VALUES (7, '英语', '王一', '001', 76.0000);
INSERT INTO `score` VALUES (8, '英语', '周二', '002', 66.0000);
INSERT INTO `score` VALUES (9, '英语', '徐三', '003', 86.0000);

SELECT
	s.NO,
	s.NAME,
	s.SUBJECT,
	MAX(s.score) '最高分' 
FROM
	( SELECT NO, NAME, SUBJECT, score FROM score HAVING 1 = 1 ORDER BY score.score DESC ) s 
WHERE
	1 = 1 
GROUP BY
SUBJECT